locked
Slow performance using IN - another way? RRS feed

  • Question

  • Hi All

    I am getting very slow performance when running a query which uses an IN clause

    Here is a code snippet:

    Declare @RowCount int
    Declare @SourceID int
    
    Set @RowCount = (Select count(*) from Dimension.ClaimReportingStatus)
    Set @SourceID = 2
    
    IF @RowCount = 0
    BEGIN
    					
    					--OpeningPosition
    					Insert into Dimension.ClaimReportingStatus
    					(SourceId,	ClaimRef,		ReportingYear,	  CurrentPeriod,	ReportingStatusReview,	  ReviewPeriod,	   ReportingStatusCurrent)
    					Select SourceId, ClaimRef, 2014, 201411, CommutationStatus, 201412, CommutationStatus
    					From Dimension.Claims DC
    					where _EffectiveFrom = '2014-01-01 00:00:00'
    END
    ELSE
    					--Incremental Load
    					With MySourceData AS(
    										Select
    										Convert(varchar,C.SourceId) + '_' + ClaimRef + '_' + convert(varchar,Year(Getdate())) AS Comparison,
    										c.SourceID, 
    										ClaimRef, 
    										Year(GETDATE()) AS ReportingYear,
    										ClosingPeriod as CurrentPeriod,
    										CASE WHEN Commutationstatus = 'C' then 'C' ELSE 'O' END AS ReportingStatusReview,
    										Convert(varchar,YEAR(GETDATE())-1) + '12' as ReviewPeriod, 
    										CASE WHEN Commutationstatus = 'C' then 'C' ELSE 'O' END AS ReportingStatusCurrent
    										FROM Dimension.Claims C
    										INNER join Reference.ClientSource rc on C.SourceId = RC.SourceId
    										INNER join etl.source es on rc.ClientName = es.Sourcename
    										where rc.SourceId = @SourceID
    										),
    					MyDestinationComparison AS(
    										Select Convert(Varchar,SourceID) + '_' + ClaimRef + '_' + Convert(varchar,ReportingYear) as Comparison2 from Dimension.ClaimReportingStatus where sourceid = @SourceID)
    
    
    										Insert into Dimension.ClaimReportingStatus(SourceId,	ClaimRef,		ReportingYear,	  CurrentPeriod,	ReportingStatusReview,	  ReviewPeriod,	   ReportingStatusCurrent)
    																			Select SD.SourceId, SD.ClaimRef, SD.ReportingYear, SD.CurrentPeriod, SD.ReportingStatusReview, SD.ReviewPeriod, SD.ReportingStatusCurrent 
    																			From MySourceData SD
    																			where SD.Comparison NOT IN(Select Comparison2 from MyDestinationComparison)

    The initial position works fine, but when running the incremental - I can select both halves of the CTE in 1m41 for 3m rows and 45secsw for the second part - but when trying to insert only rows not in the destination it just hangs at 99% cpu for what seems forever, I left it run for 35mins then cancelled it

    Is there another way to re-write this "IN" part of my clause

    Monday, August 24, 2015 1:15 PM

Answers

  • Try to use not Exists instead.
    • Marked as answer by James OHara Monday, August 24, 2015 1:50 PM
    Monday, August 24, 2015 1:35 PM