Increase SQL Query Performance

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'

How to increase the performance of the above query. It is taking 44 secs . wanna make it less than 10 secs

Thanks

Edit you question please. Mark you code properly – Codium Jul 20 '11 at 07:24 — Codium, Jul 20 '11 at 07:24
Please also describe your schema. – rkaregaran Jul 20 '11 at 07:26 — rkaregaran, Jul 20 '11 at 07:26
which database are you using? mysql? sqlsever? – Bohemian Jul 20 '11 at 07:29 — Bohemian, Jul 20 '11 at 07:29

gbn · Accepted Answer · 2011-07-20T09:01:45.643

Do you have an index on both Receive_date and status? (not an index on each, combined)

Also:

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 and UnDelivered 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

Edit, without subquery.

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)

One question: why did you use subquery instead of querying the table? — niktrs, Jul 20 '11 at 08:59

score 0 · Answer 2 · answered Jul 20 '11 at 07:32

Divide and conquer: Just try each part of your sql as a separate statement and you'll find out which part is slow. If you have sub-selects and functions there is a good chance, that the server need temp-tables to perform the select, if you haven't got enough memory (or a large dataset or configured your sql server to do so), this temp-objects are swapped to disk, which makes it slow too.

score 0 · Answer 3 · edited Aug 24 '15 at 13:45

Too many sub queries man! Get rid of some of them and it will help. Also you should not use functions on both sides in your sqls.

For example:

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

In this specific case the db engine will have to go through all the rows to evaluate DateDiff(Day, 0, A.Receive_date) and DateAdd(Day, DateDiff(Day, 0, A.Receive_date), 0) then compare it with the right hand side which also is a function! This simply is a disaster.

Also, do you have indexes on Receive_date? If not add it.

Increase SQL Query Performance

3 Answers3

Linked