MS SQL Server 优化器以及变化的表和字段别名
我们有很多查询，我们在字段和表名称的末尾附加一个随机别名（由于自定义 ORM 实现可能很难更改）。查询如下所示（尽管大多数情况下更加复杂）：
SELECT fooA.field1 as field1B,
fooA.field2 as field1C
FROM foo as fooA
后缀 A、B 和 C 是随机生成的（且长度超过一个字符）。这会损害我们查询的性能吗（即优化器是否会由于随机部分而无法识别重复查询）？我们主要使用SQL Server 2005。
We have a lot of queries for which we append a random alias at the end of field and table names (due to a custom ORM implementation that might be hard to change). The queries are like the following (though substantially more complex, most of the time):
SELECT fooA.field1 as field1B,
fooA.field2 as field1C
FROM foo as fooA
The suffixes A, B and C are randomly generated (and longer than one character). Will this hurt performance of our queries (i.e. will the optimizer not be able to recognize repeated queries due to the random part)? We mainly use SQL Server 2005.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，优化器每次都需要重新解析和重新编译您的查询，因为查询哈希会发生变化。
Yes, the optimizer will need to reparse and recompile your query each time, since the query hash will change.
查询优化器引擎使用以 ObjectId 为单位的执行计划 - 别名纯粹用于编程目的，但在执行期间不会使用。因此，我认为使用不同的别名或使用小或长的别名不会影响性能。
Quassnoi 对于重新哈希查询提出了很好的观点。虽然查询性能本身不受影响，但整体性能环境会受到影响。
The Query Optimizer Engine uses the Execution plan which goes by ObjectId - the aliases are purely for programming purposes, but are not used during execution. So I do not think that performance will be affected by using different aliases or by using small or long aliases.
Quassnoi makes a good point about the rehashing of the query. Although the query performance itself is not impacted, the overall performance environment will be impacted.
如果这些是临时查询，则每个查询都将作为单独的查询进行编译和缓存。在 SQL' 中，要使查询与缓存的计划匹配，文本必须相同（大小写和空格）。
如果您有很多仅表别名不同的查询，那么您将得到非常多的结果。糟糕的计划重用、大量编译和非常大的计划缓存。
它只是通过 objectid 与缓存计划匹配的存储过程
If these are ad-hoc queries, then each will get compiled and cached as a separate query. In SQL' for a query to match to a cached plan, the text must be identical (down to case and white-space)
If you've got lots of queries that differ only in the table aliases then you're going to have very poor plan reuse, lots of compiles and a very big plan cache.
It's only stored procedures that match to cached plans by objectid