提高 SQL 查询性能 [英] Increase SQL Query Performance
本文介绍了提高 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 秒
How to increase the performance of the above query. It is taking 44 secs . wanna make it less than 10 secs
谢谢
推荐答案
Receive_date
和 status
是否都有索引?(不是每个的索引,组合)
Do you have an index on both Receive_date
and status
? (not an index on each, combined)
还有:
- 您在表中有 4 次接触,这意味着查询将至少扩展 O(4n).通过使用 COUNT(CASE) 您可以删除
Delivered
和UnDelivered
子查询 - 也不需要简单的计数子查询
- 您需要 GROUP BY.您的 DISTINCT 可以解决这个问题
- BETWEEN 是
>=
和<=
这对于带时间的日期来说通常是不正确的
- You have have 4 touches in the table which means the query will scale at least O(4n).
By using COUNT(CASE) you can remove
Delivered
andUnDelivered
subqueries - The simple count subquery isn't needed either
- You need GROUP BY. YOur DISTINCT is a work around for that
- BETWEEN is
>=
and<=
which isn't the usually correct for dates with times
为了清楚起见,我在这里使用了一个子查询,但这并不重要:
I've used a subquery here for clarity but it doesn't matter:
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
编辑,没有子查询.
我从一个子查询开始,因为我认为它比预期的更复杂并且没有费心把它拿出来......
I started with a subquery because I thought it's be more complex than expected and didn't bother taking it out...
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)
这篇关于提高 SQL 查询性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文