While reading about tuning SQL queries, I read somewhere that 'Always use table alias and prefix all column names with the aliases when you are using more than one table.'
在阅读有关调优SQL查询的内容时,我在某处读到“当您使用多个表时,始终使用表别名并使用别名为所有列名添加前缀”。
How does table alias names affect performance? Or Do they actually affect?
表别名如何影响性能?或者他们真的会影响吗?
2 个解决方案
#1
The alias doesn't affect performance in any practical or measurable way at all (italics added on edit). That is, it would add a barely (if it all) measurable delay to query compilation. Once compiled (and re-used), it has no effect.
别名根本不会以任何实际或可测量的方式影响性能(在编辑时添加斜体)。也就是说,它会在查询编译时添加几乎(如果全部)可测量的延迟。一旦编译(并重新使用),它就没有效果。
An alias removes ambiguity when you have more than one table because you know which table it comes from. It also prevents future table changes from breaking a query. Say, you add an audit column to one table where it already exists in another table. Queries without aliases using both tables will break.
当您拥有多个表时,别名会消除歧义,因为您知道它来自哪个表。它还可以防止将来的表更改破坏查询。比如说,您将一个审计列添加到另一个表中已存在的表中。使用两个表而没有别名的查询将中断。
An alias is also mandatory in some cases e.g. schema bound views.
在某些情况下,别名也是强制性的,例如模式绑定视图。
The SQL parsing engine (that reads all queries before executing them, and uses the information to cache the compiled queries in the future for faster execution) is the only thing that looks at the aliases, and uses it to help remove ambiguities in symbol lookups. The system would already produce symbols, just like any other compilable statement in any other language, when it's being parsed prior to execution-storage.
SQL解析引擎(在执行它们之前读取所有查询,并使用该信息在将来缓存已编译的查询以便更快地执行)是唯一查看别名的内容,并使用它来帮助消除符号查找中的歧义。当系统在执行 - 存储之前被解析时,系统就会产生符号,就像任何其他语言中的任何其他可编译语句一样。
#2
Almost not at all, the performance impact is negligible, but you'll have a much better time reading the query. It's just for your convenience.
几乎没有,性能影响可以忽略不计,但您可以更好地阅读查询。这只是为了您的方便。
The performance impact is allocating a few kb of memory to store alias names, etc. in the SQL Server program itself. Compared to the rest of the operations needed to execute your query, this is almost nothing.
性能影响是分配几kb的内存来存储SQL Server程序本身的别名等。与执行查询所需的其他操作相比,这几乎没有。
#1
The alias doesn't affect performance in any practical or measurable way at all (italics added on edit). That is, it would add a barely (if it all) measurable delay to query compilation. Once compiled (and re-used), it has no effect.
别名根本不会以任何实际或可测量的方式影响性能(在编辑时添加斜体)。也就是说,它会在查询编译时添加几乎(如果全部)可测量的延迟。一旦编译(并重新使用),它就没有效果。
An alias removes ambiguity when you have more than one table because you know which table it comes from. It also prevents future table changes from breaking a query. Say, you add an audit column to one table where it already exists in another table. Queries without aliases using both tables will break.
当您拥有多个表时,别名会消除歧义,因为您知道它来自哪个表。它还可以防止将来的表更改破坏查询。比如说,您将一个审计列添加到另一个表中已存在的表中。使用两个表而没有别名的查询将中断。
An alias is also mandatory in some cases e.g. schema bound views.
在某些情况下,别名也是强制性的,例如模式绑定视图。
The SQL parsing engine (that reads all queries before executing them, and uses the information to cache the compiled queries in the future for faster execution) is the only thing that looks at the aliases, and uses it to help remove ambiguities in symbol lookups. The system would already produce symbols, just like any other compilable statement in any other language, when it's being parsed prior to execution-storage.
SQL解析引擎(在执行它们之前读取所有查询,并使用该信息在将来缓存已编译的查询以便更快地执行)是唯一查看别名的内容,并使用它来帮助消除符号查找中的歧义。当系统在执行 - 存储之前被解析时,系统就会产生符号,就像任何其他语言中的任何其他可编译语句一样。
#2
Almost not at all, the performance impact is negligible, but you'll have a much better time reading the query. It's just for your convenience.
几乎没有,性能影响可以忽略不计,但您可以更好地阅读查询。这只是为了您的方便。
The performance impact is allocating a few kb of memory to store alias names, etc. in the SQL Server program itself. Compared to the rest of the operations needed to execute your query, this is almost nothing.
性能影响是分配几kb的内存来存储SQL Server程序本身的别名等。与执行查询所需的其他操作相比,这几乎没有。