with clause select statement take long time to run.

Question

with clause select statement take long time to run.

kshatriya24

SSC Eights!

Points: 887
More actions
March 23, 2014 at 9:02 pm

#284913

Query takes long time to run. Can someone please help. With Clause reterive all the address for past six months for any duplicate complaint for OPEN status only.
With Wcon (Location, Complaindate, Repeatdate) as
(
SELECT
CAST(LOCATIONADDRESS AS VARCHAR(1000)) as 'Location Address',
MIN(c.COMPLAINTDATE) as 'Initial complaint date' , MAX(c.COMPLAINTDATE)
as 'Repeated complaint date'
FROM CASETABLE c
WHERE c.CASETY IN ('WCONS')
AND c.CASESUBTYPE <> 'OTHER'
AND c.RCSTATUS NOT IN ('RESOL' , 'WRSLV') AND
c.CREATIONDATE > DATEADD(month, -6, GETDATE())
GROUP BY LOCATIONADDRESS
HAVING DATEDIFF(DD, MIN(c.COMPLAINTDATE), MAX(c.COMPLAINTDATE)) > 0
)
/* Use following statement to reterive the caseid, status, casesubtype information for all the cases regardless what status is */
/* select statement take long time to run */
selectd.LOCATIONADDRESS as 'Location Address',d.COMPLAINTDATE as 'Complaint Date',d.CASE_ID as 'Case ID',v.LONG_DESC as 'Case Subtype',z.LONG_DESC as 'Status'
from CASETABLE d
join Wcon k
on k.Location = d.LOCATIONADDRESS AND d.CASETY = 'WCONS'
--Get the case subtype description
LEFT JOIN dbo.CASETABLE_SUBTYPE v ON v.CASESUBTYPE = d.CASESUBTYPE
LEFT JOIN dbo.STATUSTBL z ON d.RCSTATUS = z.RCSTATUS
WHERE d.CASETY IN ('WCONS')
AND d.CASESUBTYPE <> 'OTHER' and
z.SETID = 'SHARE' AND
d.CREATIONDATE > DATEADD(month, -6, GETDATE())
order by d.LOCATIONADDRESS , d.COMPLAINTDATE,d.CASE_ID
Any help apperciated.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Have you checked the execution plan? Attached the execution plan so people can help you.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post table definitions, index definitions and execution plans as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kshatriya24 SSC Eights! Points: 887 More actions · Answer 3

Thank you for suggestion. Change the with clause and it worked.