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
32
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
0
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.
性能影响是在SQL Server程序本身中分配一些kb内存来存储别名等。与执行查询所需的其他操作相比,这几乎是零。
#1
32
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
0
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.
性能影响是在SQL Server程序本身中分配一些kb内存来存储别名等。与执行查询所需的其他操作相比,这几乎是零。