优化使用多个SELECT语句并返回一个结果集的SQL查询(Optimize SQL query that uses multiple SELECT statements and returns one

优化使用多个SELECT语句并返回一个结果集的SQL查询(Optimize SQL query that uses multiple SELECT statements and returns one result set)

我有一个查询，它使用包含不同WHERE子句的多个SELECT语句来返回多个列，这些列获取不同类别的订单计数。 我目前正在使用cte来尝试优化它，但它仍然运行得很慢。 我认为临时表可能是一个更快的解决方案，但我无法到达那里：

查询：

DECLARE @User AS VARCHAR(50) SET @User = 'test.user'; WITH cte AS ( SELECT o.db_OrderNo, o.db_Deferral, d.db_SeqNo, d.db_Task FROM tblOrders o LEFT JOIN tblDate d on d.db_SeqNo = o.db_Status LEFT JOIN tblUserProfile up on up.db_UserId = o.db_RTS WHERE o.db_Deleted = 0 AND db_Date39 = '1900/01/01' AND d.db_Task IN (1, 2, 3, 4) AND up.db_Email LIKE @User + '%' ) SELECT (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Task = 1 AND db_Deferral = 0) OrderInEvalCount, (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Task = 2 AND db_Deferral = 0) OrderInDfcCount, (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Task IN (3, 4) AND db_SeqNo NOT IN (37, 39) AND db_Deferral = 0) OrderWipCount, (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Deferral != 0) OrderInPendedCount

输出：

OrderInEvalCount | OrderInDfcCount | OrderWipCount | OrderInPendedCount 20 34 9 2

温度表：

CREATE TABLE #TempCounts ( OrderInEvalCount int, OrderInDfcCount int, OrderWipCount int, OrderInzPendedCount int); // First column SELECT COUNT(DISTINCT db_OrderNo) INTO //Not sure how to specify a column FROM tblOrders o WHERE db_Task = 1 AND db_Deferral = 0 AND o.db_Deleted = 0 AND db_Date39 = '1900/01/01' AND up.db_Email LIKE @User + '%'

我以前没有使用TEMP表，所以这个开始可能有点偏。 您认为可以加快当前查询的任何其他解决方案将不胜感激。 谢谢

I have a query that uses multiple SELECT statements containing different WHERE clauses to return multiple columns that get a count of orders in different categories. I am currently using a cte to try and optimize it, but it is still running very slow. I'm thinking a temporary table may be a faster solution, but I am having trouble getting there:

Query:

DECLARE @User AS VARCHAR(50) SET @User = 'test.user'; WITH cte AS ( SELECT o.db_OrderNo, o.db_Deferral, d.db_SeqNo, d.db_Task FROM tblOrders o LEFT JOIN tblDate d on d.db_SeqNo = o.db_Status LEFT JOIN tblUserProfile up on up.db_UserId = o.db_RTS WHERE o.db_Deleted = 0 AND db_Date39 = '1900/01/01' AND d.db_Task IN (1, 2, 3, 4) AND up.db_Email LIKE @User + '%' ) SELECT (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Task = 1 AND db_Deferral = 0) OrderInEvalCount, (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Task = 2 AND db_Deferral = 0) OrderInDfcCount, (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Task IN (3, 4) AND db_SeqNo NOT IN (37, 39) AND db_Deferral = 0) OrderWipCount, (SELECT COUNT(DISTINCT db_OrderNo) FROM cte WHERE db_Deferral != 0) OrderInPendedCount

Output:

OrderInEvalCount | OrderInDfcCount | OrderWipCount | OrderInPendedCount 20 34 9 2

Temp Table:

CREATE TABLE #TempCounts ( OrderInEvalCount int, OrderInDfcCount int, OrderWipCount int, OrderInzPendedCount int); // First column SELECT COUNT(DISTINCT db_OrderNo) INTO //Not sure how to specify a column FROM tblOrders o WHERE db_Task = 1 AND db_Deferral = 0 AND o.db_Deleted = 0 AND db_Date39 = '1900/01/01' AND up.db_Email LIKE @User + '%'

I haven't used TEMP tables before, so this start may be a little off. Any other solution that you think could speed up the current query would be appreciated. Thanks

最满意答案

您可以对外部选择使用条件聚合：

SELECT COUNT(DISTINCT (CASE WHEN db_Task = 1 AND db_Deferral = 0 THEN db_OrderNo END)) as OrderInEvalCount, COUNT(DISTINCT (CASE WHEN db_Task = 2 AND db_Deferral = 0 THEN db_OrderNo END)) as OrderInDfcCount, COUNT(DISTINCT (CASE WHEN db_Task IN (3, 4) AND db_SeqNo NOT IN (37, 39) AND db_Deferral = 0 THEN db_OrderNo END)) as OrderWipCount, COUNT(DISTINCT (CASE WHEN db_Deferral <> 0 THEN db_OrderNo END)) as OrderInPendedCount FROM cte;

SQL Server以特定方式处理CTE：它将CTE的定义插入到它看到的每个位置的查询中。 因此，您的版本运行CTE的代码四次。 虽然SQL Server可以优化查询，但它无法识别出四个块正在执行相同的操作。 。 。 所以它做了更多的工作。

当CTE在查询中仅被引用一次时，使用CTE对性能没有影响。

You can use conditional aggregation for the outer select:

SELECT COUNT(DISTINCT (CASE WHEN db_Task = 1 AND db_Deferral = 0 THEN db_OrderNo END)) as OrderInEvalCount, COUNT(DISTINCT (CASE WHEN db_Task = 2 AND db_Deferral = 0 THEN db_OrderNo END)) as OrderInDfcCount, COUNT(DISTINCT (CASE WHEN db_Task IN (3, 4) AND db_SeqNo NOT IN (37, 39) AND db_Deferral = 0 THEN db_OrderNo END)) as OrderWipCount, COUNT(DISTINCT (CASE WHEN db_Deferral <> 0 THEN db_OrderNo END)) as OrderInPendedCount FROM cte;

SQL Server handles CTEs in a particular way: it inserts the definition of the CTE into the query in each place it sees it. So, your version runs the code for the CTE four times. Although SQL Server can then optimize the query, it doesn't recognize that four chunks are doing the same thing . . . and so it does much more work.

When a CTE is only referred to once in a query, then using a CTE has no impact on performance.