Query Long run time

Question

Query Long run time

Denby

Hall of Fame

Points: 3120
More actions
April 4, 2007 at 4:22 pm

#177557

Ok pple.
i Created a Query thats taking abt 45 secs to run..
is there anything i can do to optimize it's performance.
view query as follows:

SELECT DISTINCT
                      dbo.Patient_Demographics.KPAIDS_NO, dbo.Patient_Demographics.Gender, dbo.Patient_Demographics.LastName,
                      dbo.Patient_Demographics.FirstName, CONVERT(varchar(10), dbo.Patient_Demographics.Date_Of_Birth, 121) AS [Date Of Birth], CONVERT(varchar(10),
                      dbo.Patient_Demographics.Registration_Date, 121) AS [Registration Date], dbo.Clinic_Sites_Lookup.Clinic_Sites,
                      dbo.Patient_Demographics.Age_Years, DATEDIFF(yyyy, dbo.Patient_Demographics.Date_Of_Birth, GETDATE()) AS Age,
                      dbo.GetCDCList(dbo.Patient_Demographics.KPAIDS_NO) AS CDC, dbo.Registry_Dates.Mode_Of_Transmission, CONVERT(varchar(10),
                      dbo.Registry_Dates.Confirmed_HIV_Infection, 121) AS [confirm HIV Infection], DATEDIFF(mm, dbo.Registry_Dates.Confirmed_HIV_Infection,
                      dbo.GetFirst_CDC_Date(dbo.Patient_Demographics.KPAIDS_NO)) AS Hiv_Diag_And_Clin_Diag_Diff, CONVERT(varchar(10), dbo.Registry_Dates.Death,
                      121) AS Death, CASE HAART WHEN HAART THEN 'Y' ELSE 'N' END AS [ON ARV]
FROM         dbo.Registry_CDC_Category LEFT OUTER JOIN
                      dbo.Patient_Demographics ON dbo.Registry_CDC_Category.KPAIDS_NO = dbo.Patient_Demographics.KPAIDS_NO LEFT OUTER JOIN
                      dbo.Registry_Dates ON dbo.Patient_Demographics.KPAIDS_NO = dbo.Registry_Dates.KPAIDS_NO LEFT OUTER JOIN
                      dbo.Clinic_Sites_Lookup ON dbo.Patient_Demographics.Initiation_Institution = dbo.Clinic_Sites_Lookup.Clinic_ID LEFT OUTER JOIN
                      dbo.Patient_CDC_Clinical_Diagnosis ON dbo.Patient_Demographics.KPAIDS_NO = dbo.Patient_CDC_Clinical_Diagnosis.KPAIDS_NO LEFT OUTER JOIN
                      dbo.Temp_ARV_Combination ON dbo.Patient_Demographics.KPAIDS_NO = dbo.Temp_ARV_Combination.KPAIDS_NO
WHERE     (dbo.Patient_Demographics.Mother_Or_Child = 1) AND (dbo.GetCDCList(dbo.Patient_Demographics.KPAIDS_NO) IS NOT NULL) AND (DATEDIFF(yyyy,
                      dbo.Patient_Demographics.Date_Of_Birth, GETDATE()) >= 10) AND (dbo.Patient_Demographics.Registration_Date BETWEEN '2002-Jan-01' AND
                      '2006-Dec-31') AND (dbo.Clinic_Sites_Lookup.Clinic_Sites <> 'St. Ann Outreach') AND (dbo.Clinic_Sites_Lookup.Clinic_Sites <> 'St. James Outreach')
hopefully this is readable enough...
thx guys...

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Since we don't have your data, it's hard to determine why it's taking a long time. Put the query into Query Analyzer then, on the menu bar, click on Query>Display Estimated Execution Plan.

See what that has to say. Are there Index Seeks? They are bad. Index Scans are good. One of the first causes of long queries is a lack of proper indexes.

-SQLBill

Sergiy SSC Guru Points: 110208 More actions · Answer 2

SQLBill,

actually Index Seeks are good. Index Scans are bad.

You confused a little.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Even if the perfect indexes were present, it's not likely they'd be used because of the calculations on columns in the WHERE clause... there's even a UDF in there...

"Hopefully this is readable enough"?!?!?! Everybody wants a quick answer but won't spend 10 minutes correctly formatting what I assume will become production code?!!??! You really need to read the following... there IS actually a message buried in it. Sorry to be so mean but enough is enough!

And owner.word.no owner.word.its owner.word.not owner.word.easily owner.word.readable owner.word.take owner.word.a owner.word.peak owner.word.at owner.word.bookson owner.word.line owner.word.for owner.word.how owner.word.to owner.word.use owner.word.table owner.word.aliases owner.word.also owner.word.learn owner.word.how owner.word.to owner.word.indent owner.word.and owner.word.breaklines owner.word.at owner.word.major owner.word.key owner.word.words.

And, you haven't told us how many rows are in each table of the query nor how many rows it returns. If it's returning a half million rows from multiple 30 million row tables, then 45 seconds isn't bad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Yeah, I know I got it backwards.

I guess I was typing without thinking....too busy of a day. Thanks for pointing it out and correcting me.

-SQLBill