Help with a Select Statement
-
I need to speed up the following statement. I have put it through the Index Tuning Wizard which suggested a couple of index which I applied. But is there some performance to be gained by restructuring the statement:
DECLARE @UpdateCnt int
DECLARE @todaysDate datetime
SET @todaysDate = dateadd(yy,-1,getdate())
SET @todaysDate = dateadd(dd,-1,@todaysDate)
SELECT clm.claimID
INTO #tbl_temp_claims
FROM claims clm
INNER JOIN tblpayments p ON (p.claimid = clm.claimID)
WHERE (clm.addeddate < @todaysDate)
AND (clm.settle_date < @todaysDate OR clm.settle_date is null)
AND (clm.received110date <@todaysDate OR clm.received110date is null)
AND (clm.received111date < @todaysDate OR clm.received111date is null)
AND (clm.received113date < @todaysDate OR clm.received113date is null)
AND (clm.injurystatusid = 1)
GROUP BY clm.claimID
HAVING MAX(p.datereceived) < @todaysDate
UNION
SELECT claimid
FROM claims clm
WHERE claimid not in
(SELECT claimid FROM tblpayments p
WHERE p.claimid = clm.claimid)
AND (clm.addeddate < @todaysDate)
AND (clm.settle_date < @todaysDate OR clm.settle_date is null)
AND (clm.received110date <@todaysDate OR clm.received110date is null)
AND (clm.received111date < @todaysDate OR clm.received111date is null)
AND (clm.received113date < @todaysDate OR clm.received113date is null)
AND (clm.injurystatusid = 1)
/*
UPDATE Claims
SET injuryStatusid = 4
FROM claims clm
INNER JOIN #tbl_temp_claims t ON t.claimid = clm.claimID
*/
SET @UpdateCnt = @@rowcount
SELECT @UpdateCnt as UpdateCnt
drop table #tbl_temp_claims
GO
Arthur Lorenzini
Arthur Lorenzini -
Try This
UPDATE Claims
SET injuryStatusid = 4
FROM claims clm,(SELECT clm.claimID
INTO #tbl_temp_claims
FROM claims clm
INNER JOIN tblpayments p ON (p.claimid = clm.claimID)
WHERE (clm.addeddate < @todaysDate)
AND (clm.settle_date < @todaysDate OR clm.settle_date is null)
AND (clm.received110date <@todaysDate OR clm.received110date is null)
AND (clm.received111date < @todaysDate OR clm.received111date is null)
AND (clm.received113date < @todaysDate OR clm.received113date is null)
AND (clm.injurystatusid = 1)
GROUP BY clm.claimID
HAVING MAX(p.datereceived) < @todaysDate
UNION
SELECT claimid
FROM claims clm
WHERE claimid not in
(SELECT claimid FROM tblpayments p
WHERE p.claimid = clm.claimid)
AND (clm.addeddate < @todaysDate)
AND (clm.settle_date < @todaysDate OR clm.settle_date is null)
AND (clm.received110date <@todaysDate OR clm.received110date is null)
AND (clm.received111date < @todaysDate OR clm.received111date is null)
AND (clm.received113date < @todaysDate OR clm.received113date is null)
AND (clm.injurystatusid = 1)) AS t
WHERE
t.claimid = clm.claimID
-
not sure if it will speed it up or not but
(clm.settle_date < @todaysDate OR clm.settle_date is null)
could be
isnull(clm.settle_date,@TomorrowsDate) < @todaysDate)
-
If none of that helped (and the temp table should have) - try ordering your 'and' clauses so that the most restrictive one is performed first; that will limit the amount of data that the other ones have to grind through. That's worked for me in the past on huge data sets.
Thanks, and don't forget to Chuckle
Thanks, and don't forget to Chuckle -
You may want to try with UNION ALL...
also try....
(I did not test for absolute correctness)
SELECT DISTINCT clm.claimID
INTO #tbl_temp_claims
FROM claims clm
LEFT OUTER JOIN tblpayments p
ON clm.claimID = p.claimid
WHERE clm.injurystatusid = 1
AND ISNULL(clm.addeddate, @TomorrowsDate) < @todaysDate
AND ISNULL(clm.settle_date, @TomorrowsDate) < @todaysDate
AND ISNULL(clm.received110date, @TomorrowsDate) < @todaysDate
AND ISNULL(clm.received111date, @TomorrowsDate) < @todaysDate
AND ISNULL(clm.received113date, @TomorrowsDate) < @todaysDate
AND ISNULL(p.claimid, clm.claimID) = clm.claimID -- This Gets the rows from the UNION
Once you understand the BITs, all the pieces come together
-
Above, (UNION ALL) I meant something like...
Select Distinct ClaimID
INTO #tbl_temp_claims
From (SELECT clm.claimID
FROM claims clm
INNER JOIN tblpayments p
ON (p.claimid = clm.claimID)
WHERE .....
UNION ALL
SELECT claimid
FROM claims clm
WHERE claimid not in
(SELECT claimid FROM tblpayments p
WHERE .... )
) A
Once you understand the BITs, all the pieces come together
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply