提高SQL查询性能

Question

Sql:

select distinct DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0) as Date,
(select count(*) from Raw_Mats A where DateAdd(Day, DateDiff(Day, 0, A.Receive_date), 0)=DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0)) as Total,
(select count(*) from Raw_Mats B where DateAdd(Day, DateDiff(Day, 0, B.Receive_date), 0)=DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0) and B.status='Solved') as Delivered,
(select count(*) from Raw_Mats C where DateAdd(Day, DateDiff(Day, 0, C.Receive_date), 0)=DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0) and C.status='Pending') as UnDelivered
from Raw_Mats m where m.Receive_date between '2011-07-01' and '2011-07-21'

如何提高上述查询的性能。需要44秒。想让它小于10秒

感谢

Answer 1

您对Receive_date和status都有索引吗？(不是每个的索引，组合(

还有：

• 表中有4个触摸，这意味着查询的比例至少为O(4n(。使用COUNT(CASE(可以删除Delivered和UnDelivered子查询
• 也不需要简单的count子查询
• 你需要GROUP BY。你的DISTINCT是一个变通办法
• BETWEEN是>=和<=，这对于带时间的日期来说通常不正确

为了清楚起见，我在这里使用了一个子查询，但这并不重要：

select
   DateOnly as Date,
   COUNT(*) AS Total,
   COUNT(CASE WHEN status='Solved' THEN 1 END) AS Delivered,
   COUNT(CASE WHEN status='Pending' THEN 1 END) AS UnDelivered
from
   (
   SELECT
       DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0) as DateOnly,
       status
   FROM
      Raw_Mats
   WHERE
      Receive_date >= '2011-07-01' AND Receive_date < '2011-07-21'
   ) T
 GROUP BY
   DateOnly

编辑，不带子查询。

我从一个子查询开始，因为我认为它比预期的更复杂，所以没有费心把它取出来。。。

select
   DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0) as Date,
   COUNT(*) AS Total,
   COUNT(CASE WHEN status='Solved' THEN 1 END) AS Delivered,
   COUNT(CASE WHEN status='Pending' THEN 1 END) AS UnDelivered
from
   Raw_Mats
WHERE
   Receive_date >= '2011-07-01' AND Receive_date < '2011-07-21'
GROUP BY
   DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0)

Answer 2

分而治之：只需将sql的每个部分作为一个单独的语句进行尝试，就会发现哪个部分速度较慢。如果你有子选择和函数，那么服务器很有可能需要临时表来执行选择，如果你没有足够的内存(或者没有一个大的数据集，或者没有配置sql server来执行选择(，这些临时对象会交换到磁盘，这也会使速度变慢。

Answer 3

子查询太多了，伙计！去掉其中的一些，这会有所帮助。此外，您不应该在sql中同时使用两边的函数。

例如：

where DateAdd(Day, DateDiff(Day, 0, A.Receive_date), 0)=
              DateAdd(Day, DateDiff(Day, 0, m.Receive_date), 0) 

在这种特定情况下，数据库引擎必须遍历所有行来评估DateDiff(Day, 0, A.Receive_date) and DateAdd(Day, DateDiff(Day, 0, A.Receive_date), 0)，然后将其与右侧进行比较，这也是一个函数！这简直是一场灾难。

另外，您在Receive_date上有索引吗？如果不添加它。