May 16, 2012 at 12:18 pm
Hi everyone.
My query (below) returns results within 5 seconds or less in SQL Server Management Studio. I even declared and set parameters to mimic the performance it would have in SQL Server Reporting Services.
When I place it into a report, it takes over 20 minutes to run!
I'm at a loss, and would appreciate any help or advice!
Some notes:
The unions are because the tables I'm pulling from are not normalized (ew!).
I'm using Visual Studio 2008
I had this problem initially, and it would take 30 mins in SSMS (as well as SSRS!), and I've cut it down to 5 seconds or less.
Declare @subid varchar(14)
set @subid = '200502005149'
SELECT c.SUB_ID as ' Subscriber ID',
c.Diagnosis,
d.FHDXDESC as Description,
c.Source,
c.PROV_NAME as 'Provider Name',
c.DOS as 'Date of Service',
1 as 'Reported'
FROM (
SELECT m.SUB_ID, m.Diagnosis, 'Medical' as Source, m.PROV_NAME,CONVERT(datetime, md.BEG_DATE_OF_SERV) AS DOS
FROM (
/*Begin medical encounters Union */
select Sub_ID, PRIMARY_DIAG_ICD as Diagnosis, 'Medical' as Source,
PROV_NAME, Claim_ref_num
from SQL4.Encounter.dbo.medhdr_new_format
Union
select Sub_ID, Second_DIAG_ICD as Diagnosis, 'Medical' as Source,
PROV_NAME, Claim_ref_num
from SQL4.Encounter.dbo.medhdr_new_format
Union
select Sub_ID, Tertiary_DIAG_ICD as Diagnosis, 'Medical' as Source,
PROV_NAME, Claim_ref_num
from SQL4.Encounter.dbo.medhdr_new_format
Union
select Sub_ID, DIAG_4TH as Diagnosis, 'Medical' as Source,
PROV_NAME, Claim_ref_num
from SQL4.Encounter.dbo.medhdr_new_format
Union
select Sub_ID, DIAG_5TH as Diagnosis, 'Medical' as Source,
PROV_NAME, Claim_ref_num
from SQL4.Encounter.dbo.medhdr_new_format
Union
select Sub_ID, DIAG_6TH as Diagnosis, 'Medical' as Source,
PROV_NAME, Claim_ref_num
from SQL4.Encounter.dbo.medhdr_new_format ) as m
INNER JOIN SQL4.Encounter.dbo.meddet_new_format AS md
ON md.CLAIM_REF_NUM = m.CLAIM_REF_NUM
Union
/*Begin hospital encounters Union */
select sub_ID, diagnosis, source, prov_name, convert(datetime, beg_date_serv) as DOS
from
(
select Sub_ID, Primary_Diag_Icd as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num
From SQL4.Encounter.dbo.hosphdr_new_format
Union
select Sub_ID, Second_Diag_Icd as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num
From SQL4.Encounter.dbo.hosphdr_new_format
Union
select Sub_ID, tertiary_Diag_Icd as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num
From SQL4.Encounter.dbo.hosphdr_new_format
Union
select Sub_ID, Diag_4th as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num
From SQL4.Encounter.dbo.hosphdr_new_format
Union
select Sub_ID, Diag_5th as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num
From SQL4.Encounter.dbo.hosphdr_new_format
Union
select Sub_ID, Diag_6th as Diagnosis, 'Hospital' as Source, Prov_Name, Claim_ref_num
From SQL4.Encounter.dbo.hosphdr_new_format
)h
inner join SQL4.Encounter.dbo.hospdet_new_format hd on h.Claim_Ref_Num = hd.claim_ref_num
)c
inner join SQL2.DIam_725.diamond.JDIAGNM0_Dat d on d.FHDXCODE = c.diagnosis
where (c.Diagnosis IS NOT NULL) AND (c.Diagnosis <> '999.9') AND (c.Diagnosis <> 'DXINV') AND (c.Diagnosis <> '') and (sub_id = @SUbid +'00' or sub_id = @SUbid + '01')
and dos >= '01-01-2010'
May 16, 2012 at 12:42 pm
Sounds like parameter sniffing. I don't have the links handy, so you will need to try searching this site with those keywords. Or, hopefully, someone with the links will come around and post them here as well.
May 16, 2012 at 2:27 pm
Lynn Pettis (5/16/2012)
Sounds like parameter sniffing. I don't have the links handy, so you will need to try searching this site with those keywords. Or, hopefully, someone with the links will come around and post them here as well.
It seems like that is the case...other people are reporting something similiar. The only work around I have found is to create a stored procedure and declare variables inside of the stored procedure and assign the incoming parameters to the variables.
It's really strange, because I've done nearly an identical query on another table with the same parameter without issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply