How to quicken my query which uses union

Question

0.00/5 (No votes)

See more:

The below query taking longer time.

SQL

with  QI 
as
(select QuoteID, LOBID, Status,CarrierID, Premium from tr_QuoteInfo qp where QuoteID  not in (select PreQuoteID from tr_QuoteInfo where PreQuoteID is not null)  and LOBID =1
union
select  QuoteID, LOBID, Status, CarrierID, Premium from tr_QuoteInfo where PreQuoteID is not null  and LOBID =1) 
 
select CQ.AssignTo,Count(qi.QuoteID) as TotalQotes,Count(case when qi.Status != 4 then qi.QuoteID end) as LostQuotes, Count(case when qi.Status= 4 then qi.QuoteID end) As Renewed, Sum(case when qi.Status= 4 then qi.premium end) as CARClosedPremium ,  Sum(case when qi.Status != 4 then qi.premium end) as CARLostPremium  
 from tr_CRM_QuoteInfo CQ  join QI on CQ.QuoteID = qi.QuoteID and qi.LOBID = cq.LOBID 

 join tr_MotorPraposalReqRes MPR on QI.QuoteID=MPR.QuoteID and QI.LOBID=MPR.LOB and QI.CarrierID=MPR.CarrierID 


where DATEDIFF(dd,getdate(),convert(date,MPR.PolicyExpDate,103)) between 0 and 60  
Group by CQ.AssignTo

Please help to make it run Quick

What I have tried:

with QI
as
(select QuoteID, LOBID, Status,CarrierID, Premium from tr_QuoteInfo qp where QuoteID not in (select PreQuoteID from tr_QuoteInfo where PreQuoteID is not null) and LOBID =1
union
select QuoteID, LOBID, Status, CarrierID, Premium from tr_QuoteInfo where PreQuoteID is not null and LOBID =1)

select CQ.AssignTo,Count(qi.QuoteID) as TotalQotes,Count(case when qi.Status != 4 then qi.QuoteID end) as LostQuotes, Count(case when qi.Status= 4 then qi.QuoteID end) As Renewed, Sum(case when qi.Status= 4 then qi.premium end) as CARClosedPremium , Sum(case when qi.Status != 4 then qi.premium end) as CARLostPremium
from tr_CRM_QuoteInfo CQ join QI on CQ.QuoteID = qi.QuoteID and qi.LOBID = cq.LOBID

join tr_MotorPraposalReqRes MPR on QI.QuoteID=MPR.QuoteID and QI.LOBID=MPR.LOB and QI.CarrierID=MPR.CarrierID

where DATEDIFF(dd,getdate(),convert(date,MPR.PolicyExpDate,103)) between 0 and 60
Group by CQ.AssignTo

Posted 9-May-16 22:00pm

Member 12456650

Updated 10-May-16 0:26am

Add a Solution

Comments

Herman<T>.Instance 10-May-16 4:21am

Have you checked with a sqlprofiler what might cause the delay? Or the execution plan?

Tomas Takac 10-May-16 4:25am

You need to look at the query plan otherwise this is just guessing. I see other problematic things like NOT IN or the final WHERE clause where you do some really crazy stuff.

Member 12456650 10-May-16 5:55am

I'm new to SQL.

is there any way to avoid 'NOT IN' and final 'Where' ?

CHill60 10-May-16 6:37am

You apparently have the column [PolicyExpDate] defined as a string - it should be of type Date or DateTime. That will make the final WHERE run quicker.
The summation depending on qi.[Status] might be better done with a PIVOT and often you can avoid the NOT IN by using an INNER JOIN. It would be a lot easier to help if you shared a few lines of data from each of the tables or create a SQL Fiddle[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2016-05-10T00:26:00

Following up on the two comments, above:

If you have SQLPRFILER, then it will help you by pointing out where it is doing what is called a "table scan" - which means it's looking at every single record in the table: a very slow event if a table is of any size.

With or without the profiler, you can take a decent guess at what would speed up your query: Use indices on key fields - that is those in the WHERE clauses - which will eliminate one or more of these table scans. INDEXing a column can cause a very dramatic increase in speed. Nothing's for free: every index you put in takes up space on your hard drive and slows down insertions because each new record must be updated.

If you think it's the problem, start with your not-in query. I'd also look at the DATEDIFF area as you do a great deal of work in that little space. Even setting getdate() to a constant gotten just as the query is executed can save you a step that's repeated for every value. The CONVERT is not helpful to your execution time, either. Why not, instead, create a "BETWEEN" clause, instead, and where it's between your date and the 'final date' for your range, both values being created in advance (@startDate, @endDate) and save some steps.

It all adds up.