参考代码表的最佳 Sybase ASE 表锁定方案 a
我有一个参考代码表，只能在正常使用期间读取，并且只能在产品更改时更新（每月）。
我应该在创建表语句中使用什么表锁定？
我假设是 ALLPAGES，因为这样可以减少 ASE 需要管理的读锁总数；但我从该项目的另一位开发人员那里得到了不同的“建议”。
我在相关数据库中看到其他使用 ALLPAGES 的参考表；但它们是从 12.5 升级的，这可能只是当时可用版本的保留。
该表不是很宽，它有两个数字代码 id 列和一个 char(1) 列，并且聚集在这两个数字代码上。
I have a reference code table that will only be read from during normal use, and it aught to be only updated for product changes (monthly).
What table locking should I be using in the create table statement?
I was assuming ALLPAGES because that keeps the total number of read locks down that ASE needs to manage; but I am getting different 'advice' from another developer on the project.
I see other reference tables in the DB in question that use ALLPAGES; but they were upgraded from 12.5 it just may be a holdover from what was available then.
The table isn't very wide, it has two numeric code id columns, a char(1) column, and is clustered on the two numeric codes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我只有明显的答案 - 首先运行 sp_object_stats 并选择锁定方案。
I have only obvious answer - run sp_object_stats at the first and choose locking scheme.
如果引用表仅用于读取，那么全页锁定是最好的，因为它需要最少数量的锁（正如您所说的），并且进程在读取数据时获取的共享锁不存在冲突。
只是为了给您提供有关性能的额外提示：始终尝试通过相关子查询使用引用表以利用子查询缓存。另请记住，仅当子查询尚未被优化器展平并且未转换回常规联接时，才会发生子查询缓存。确保子查询不被展平的技巧是在属性上使用聚合函数，例如 max(attr)。 max 函数只是虚拟的，没有任何“Group By”。
If the reference table is only for reading, All-Pages locking is best as it takes least number of locks ( that you said ) and there is no conflict on Shared Locks that the processes acquire while reading data.
Just to give you an additional tip on performance: Always try to use a reference table through a co-related sub-query to take the advantage of sub-query caching. Also remember that sub-query caching takes place only when the sub-query has not been flattened by the optimizer and not converted back into a regular join. The trick to ensure than sub-query is not flattened is to use an aggregate function say max(attr) on the attribute. The max function will just be dummy without any "Group By".