使用CTE有什么优点/缺点？ [英] What are the advantages/disadvantages of using a CTE?

问题描述

我正在考虑提高某些SQL的性能，目前CTE正在脚本中多次使用和引用。我会使用表变量获得改进吗？ （由于代码位于函数内，因此不能使用临时表。）

I'm looking at improving the performance of some SQL, currently CTEs are being used and referenced multiple times in the script. Would I get improvements using a table variable instead? (Can't use a temporary table as the code is within functions).

推荐答案

您实际上必须进行性能测试-没有是/否答案。根据安迪·利文（Andy Living）上面指向的链接，CTE只是查询或子查询的简写。

You'll really have to performance test - There is no Yes/No answer. As per Andy Living's post above links to, a CTE is just shorthand for a query or subquery.

如果您在同一函数中调用两次或更多次，则填充表变量然后加入该表变量或从中选择表变量可能会获得更好的性能。但是，由于表变量占用了某个地方的空间，并且没有索引/统计信息（表变量上已声明的主键除外），所以无法说出哪个会更快。

If you are calling it twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don't have indexes/statistics (With the exception of any declared primary key on the table variable) there's no way of saying which will be faster.

它们都有成本和节省的余地，这是最好的方法，这取决于他们获取的数据及其处理方式。我遇到过您，在各种情况下测试速度后-有些函数使用CTE，有些使用表变量。

They both have costs and savings, and which is the best way depends on the data they pull in and what they do with it. I've been in your situation, and after testing for speed under various conditions - Some functions used CTEs, and others used table variables.

这篇关于使用CTE有什么优点/缺点？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！