-1

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

Improve this question
3
  • 3
    Edit you question please. Mark you code properly Commented Jul 20, 2011 at 7:24
  • 2
    Please also describe your schema. Commented Jul 20, 2011 at 7:26
  • 1
    which database are you using? mysql? sqlsever? Commented Jul 20, 2011 at 7:29

3 Answers 3

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

One question: why did you use subquery instead of querying the table?
@niktrs: for some more clarity
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.