February 23, 2009 at 9:28 am
Attached is the execution plan for the query. I checked the missing indexes and fragmentation of the indexes, they are fine but don't know why this query taking
long time.
Attached is the estimated execution plan for the query. Please convert attached file from DOC to SQLPlan extenstion.
Appreciated for the help.
February 23, 2009 at 9:40 am
Any possibility of posting the code for the query? That usually helps in these cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2009 at 9:54 am
Please post table and index definitions as well.
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
February 23, 2009 at 10:00 am
GSquared (2/23/2009)
Any possibility of posting the code for the query? That usually helps in these cases.
I agree it would be more polite and easier if the SQL was posted, but you can get it from the execution plan. You can right-click anywhere in the plan (in SSMS) and Select Properties and the statement is in the Properties view. Or you can open the sqlplan in notepad and get the statement from the XML.
Just based on a quick glance at the I don't see any specific issues because I see mostly index seeks and you most expensive operation is a Clustered Index Seek on TRANSAC_LIVE.
I would suggest that you convert your query to ANSI standard JOIN syntax instead of having everything in the where clause.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 23, 2009 at 10:15 am
I am extremely sorry about that. Here is the code. I think some user running this old fashion query.
When user runs this query, it returns results pretty quickly. When she excludes the columns DN33031.CREATION_USERNAME in the select list and execludes (DN33031.CREATION_USERNAME='TEPIC')) from where clause, it takes a really long time to process.
SELECT DISTINCT DN200.GROUP_NUMBER, PATIENT.MRN, INVOICE.INV_NUM, TRANSAC.TRANSACTION_ZZ, DN1.PROCEDURE_CODE, DN504.CATEGORY_NAME, ]DN33031.CREATION_USERNAME
FROM IDX.dbo.DN1 DN1, IDX.dbo.DN19 DN19, IDX.dbo.DN19 DN19_1, IDX.dbo.DN2 DN2, IDX.dbo.DN200 DN200, IDX.dbo.DN33031 DN33031, IDX.dbo.DN504 DN504, IDX.dbo.DN77 DN77, IDX.dbo.DN77 DN77_1, IDX.dbo.INVOICE INVOICE, IDX.dbo.PATIENT PATIENT, IDX.dbo.TRANSAC TRANSAC
WHERE TRANSAC.GROUP_ZZ = INVOICE.GROUP_ZZ AND TRANSAC.INVOICE = INVOICE.INVOICE AND TRANSAC.PATIENT = INVOICE.PATIENT AND INVOICE.GROUP_ZZ = DN200.Record_Number AND INVOICE.ZGW_ORIG_FSC = DN19.Record_Number AND INVOICE.FSC = DN19_1.Record_Number AND PATIENT.PATIENT = INVOICE.PATIENT AND PATIENT.PATIENT = TRANSAC.PATIENT AND PATIENT.GROUP_ZZ = INVOICE.GROUP_ZZ AND TRANSAC.PX_CODE = DN2.Record_Number AND DN19.REPORTING_CATEGORY__1 = DN77.Record_Number AND DN19_1.REPORTING_CATEGORY__1 = DN77_1.Record_Number AND TRANSAC.PX_CODE = DN1.Record_Number AND DN1.HMO_COVERAGE_CATEGORY = DN504.Record_Number AND TRANSAC.ZGW_TES_TXN_CREAT_INI = DN33031.Record_Number AND ((INVOICE.INV_CRE_DT Between {ts '2008-12-04 00:00:00'} And {ts '2009-01-07 00:00:00'}) AND (DN200.GROUP_NUMBER='4') AND (DN77.CATEGORY_MNEMONIC='HMO') AND (Not DN77_1.CATEGORY_MNEMONIC='RHMO') AND (Not DN504.MNEMONIC='OV') AND (DN33031.CREATION_USERNAME='TEPIC'))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply