sqlsql-serverperformancequery-tuning

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'

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

Thanks


Solution

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

    Also:

    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)