Prev: Add Calculated Series in SSRS not there
Next: SQL profiler - command utility not stopping on its own.
From: Eregnon on 23 Jun 2010 12:15
I have this query I'm running for a client. I changed the select to select *
to make things clearer. The problem is the last line in the where clause -
down at the bottom:


select ak.*
FROM ihs_charge_accession_key ak
join IHS_Charge_Accession_File a on ak.Billing_Enc_ID = a.Billing_Enc_ID
LEFT OUTER JOIN IHS_Charge_Demo_MSH m ON ak.Billing_Enc_ID = m.Billing_Enc_ID

LEFT OUTER JOIN IHS_Charge_RTF_Stage r ON a.accession_number = r.
accession_number
LEFT OUTER JOIN IHS_Charge_Header ch ON ak.Billing_Enc_ID = ch.Billing_Enc_ID
and a.accession_number = ch.accession_number and (r.rtf_file = ch.rtf_file or
isnull(ch.rtf_file,'') = '')

LEFT OUTER JOIN IHS_Charge_Stage s ON ch.charge_header_id = s.
charge_header_id
LEFT OUTER JOIN user_mstr um ON s.modified_by = um.user_id
left outer join ihs_charge_header_status chs on ch.charge_header_id = chs.
charge_header_id
left outer join ihs_charge_status_lookup csl on chs.status_id = csl.status_id

WHERE ISNULL(a.delete_ind, 'N') = 'N'
AND CONVERT(CHAR(8), CONVERT(DATETIME, ISNULL(a.exam_end_datetime, GETDATE()))
, 112) BETWEEN '20100512' AND '20100512'
AND ISNULL(ch.coded_status, 'N') LIKE '%%' AND ISNULL(a.HL7_Import, '') LIKE
'%Y%' AND ISNULL(a.RTF_Import, '') = 'Y'
AND ch.process_timestamp >= '05/18/2009' and ch.process_timestamp < dateadd(d,
1,'05/18/2010')

If I comment out the last line, the query runs in about 2 seconds. With that
line in, it takes about 30 seconds! The table is does have an index on
process_timestamp. Can anyone tell me why taking out a search criteria would
make it run so much faster?

From: Erland Sommarskog on 23 Jun 2010 18:12
Eregnon (u61232(a)uwe) writes:
> I have this query I'm running for a client. I changed the select to
> select * to make things clearer. The problem is the last line in the
> where clause - down at the bottom:
>
>
> select ak.*
> FROM ihs_charge_accession_key ak
> join IHS_Charge_Accession_File a on ak.Billing_Enc_ID = a.Billing_Enc_ID
> LEFT OUTER JOIN IHS_Charge_Demo_MSH m ON
> ak.Billing_Enc_ID = m.Billing_Enc_ID
> LEFT OUTER JOIN IHS_Charge_RTF_Stage r
> ON a.accession_number = r. accession_number
> LEFT OUTER JOIN IHS_Charge_Header ch
> ON ak.Billing_Enc_ID = ch.Billing_Enc_ID
> and a.accession_number = ch.accession_number
> and (r.rtf_file = ch.rtf_file or isnull(ch.rtf_file,'') = '')
> LEFT OUTER JOIN IHS_Charge_Stage s ON
> ch.charge_header_id = s.charge_header_id
> LEFT OUTER JOIN user_mstr um ON s.modified_by = um.user_id
> left outer join ihs_charge_header_status chs on
> ch.charge_header_id = chs. charge_header_id
> left outer join ihs_charge_status_lookup csl on
> chs.status_id = csl.status_id
>
> WHERE ISNULL(a.delete_ind, 'N') = 'N'
> AND CONVERT(CHAR(8),
> CONVERT(DATETIME, ISNULL(a.exam_end_datetime, GETDATE())), 112)
> BETWEEN '20100512' AND '20100512'
> AND ISNULL(ch.coded_status, 'N') LIKE '%%'
> AND ISNULL(a.HL7_Import, '') LIKE '%Y%'
> AND ISNULL(a.RTF_Import, '') = 'Y'
> AND ch.process_timestamp >= '05/18/2009'
> and ch.process_timestamp < dateadd(d, 1,'05/18/2010')
>
> If I comment out the last line, the query runs in about 2 seconds. With
> that line in, it takes about 30 seconds! The table is does have an index
> on process_timestamp. Can anyone tell me why taking out a search
> criteria would make it run so much faster?

Why not?

First of all, the query looks spooky. You left-join to all tables,
but the WHERE conditions all refer to left-join tables. That essentially
transforms the joins to inner joins. Except that use isnull all over
the place - but in the last condition. Undeniably that could have
something to do with it.

Also, if you add a condition on an indexed column you are giving the
optimizer another choice, and it may walk into that trap, particularly
if statistics are out of date.

I would suggest that you first review the query and reconsider whether
you want outer or inner join. Once you have done this, and if the issue
still persists, it's time to look at the query plans to see what is
going on.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Add Calculated Series in SSRS not there
Next: SQL profiler - command utility not stopping on its own.