加快这个SQL查询
问题描述:
我有以下(相当传统,做了一年或两年前)SQL查询。 SQL所在的网页(在ASP.NET的SqlDataSource/GridView中使用)非常慢,我已经确定了这个查询的缓慢程度 - 看起来是因为子选择。我已经尝试过使用连接来希望加快速度,但我无法使其工作。有任何想法吗?加快这个SQL查询
我不会把整个查询放在这里,只是因为我在没有互联网访问的机器上工作,所以我将无法复制和粘贴,而大部分只是从主表中选择。
SELECT ...,
CASE
WHEN di.Total = di.Delivered THEN 'Received'
ELSE 'Not Received' END AS 'Status',
...
FROM Deliveries AS d
LEFT OUTER JOIN (
SELECT Delivery,
COUNT(*) AS Total,
COUNT(CASE WHEN Status = 2 THEN 1 END) AS Delivered
FROM DeliveryItems
GROUP BY Delivery
) AS di ON d.ID = di.Delivery
任何提示?
答
SELECT Delivery,
COUNT(*) AS Total,
COUNT(CASE WHEN Status = 2 THEN 1 END) AS Delivered
FROM DeliveryItems
GROUP BY Delivery
这部分可以通过开沟CASE语句来改进。也许尝试连接两次,一次是针对总数,一次是针对已过滤的计数。
SELECT ...,
CASE
WHEN d2.Total = d1.Delivered THEN 'Received'
ELSE 'Not Received' END AS 'Status',
...
FROM Deliveries AS d
LEFT OUTER JOIN (
SELECT Delivery,
COUNT(*) AS Delivered
FROM DeliveryItems
WHERE Status = 2
GROUP BY Delivery) d1 ON d.Id = d1.Delivery
LEFT OUTER JOIN (
SELECT Delivery,
COUNT(*) AS Total
FROM DeliveryItems
GROUP BY Delivery) d2 ON d.ID = d2.Delivery
您可以从查询分析器添加执行计划吗?这通常会告诉你导致减速的步骤。 – Keith 2011-05-09 08:23:18
您在DeliveryItems.Delivery和Deliveries.ID上有正确的索引吗? – spender 2011-05-09 08:25:23
重新标记 - ASP.Net的东西是不相关的,这是纯粹的SQL – RichardW1001 2011-05-09 08:40:30