如何加快我使用union的查询 [英] How to quicken my query which uses union

问题描述

以下查询需要更长的时间。

The below query taking longer time.

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

请帮忙让它运行快速



我尝试过：


带有QI的


as

（选择来自tr_QuoteInfo qp的QuoteID，LOBID，Status，CarrierID，Premium，其中QuoteID不在（从pre_otein不为空的tr_QuoteInfo中选择PreQuoteID）和LOBID = 1

union

选择来自tr_QuoteInfo的QuoteID，LOBID，Status，CarrierID，Premium，其中PreQuoteID不为空且LOBID = 1）



选择CQ.AssignTo，Count（qi.QuoteID）作为TotalQotes，Count（qi.Status！= 4然后qi.QuoteID结束时的情况）为LostQuotes，Count（qi.Status = 4然后qi的情况。 QuoteID结束）作为更新， Sum（当qi.Status = 4然后qi.premium结束时的情况）作为CARClosedPremium，Sum（qi.Status！= 4然后qi.premium结束时的情况）作为CARLostPremium

来自tr_CRM_QuoteInfo CQ加入QI on CQ.QuoteID = qi.QuoteID和qi.LOBID = cq.LOBID



在QI上加入tr_MotorPraposalReqRes MPR.QuoteID = MPR.QuoteID和QI.LOBID = MPR.LOB和QI.CarrierID = MPR.CarrierID





其中DATEDIFF（dd，getdate（），convert（date，MPR.PolicyExpDate，103） ））0到60之间

分组由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

推荐答案

跟进以上两条评论：



如果你有SQLPRFILER，它会帮助你指出它在做什么叫做表扫描 - 这意味着它正在查看表中的每一条记录：a非常慢的事件，如果一个表是任何大小。



无论有没有分析器，你可以采取一个很好的猜测什么会加快你的查询：使用索引关键的elds - 即WHERE子句中的那些 - 将消除这些表扫描中的一个或多个。索引列可能会导致速度急剧增加。没有什么是免费的：你放入的每个索引占用硬盘空间并减慢插入速度，因为每个新记录都必须更新。



如果你认为这是问题，从你的非查询开始。当你在那个小空间里做大量的工作时，我也会看看DATEDIFF区域。即使在执行查询时将getdate（）设置为常量也可以为您节省为每个值重复的步骤。 CONVERT对您的执行时间也没有帮助。相反，为什么不创建一个BETWEEN子句，而是在你的日期和你的范围的最终日期之间，两个值都是事先创建的（@startDate，@ endDate）并保存一些步骤。 />


这一切都加起来。

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.

这篇关于如何加快我使用union的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！