Query performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query performance

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-03-09 : 12:22:20

I have a query like below and it takes a aouple of seconds to run

select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0

) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider

group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider

However, if i add a served field( stamped with 1) it takes forever to run.. All of join columns have indexes.. cluster and non-clustered.. and i don;t see any index fregmentaitons...

select a.Registration_Key, ag.Agreement_Type_Name,p.ServiceProvider, served from dbo.Assessment a
INNER JOIN (
select distinct Registration_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Registration_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Registration_Key, p.ServiceProvider
, 1 as served
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '08/01/2007' and '08/31/2007'
and Procedure_CD is not null
and Encounter_Units > 0

) as s2
ON a.Registration_Key = s2.Registration_Key
and p.ServiceProvider = s2.ServiceProvider

group by a.Registration_Key, ag.Agreement_Type_Name, p.ServiceProvider, served

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 12:57:45

Your s1 and s2 derived tables are both using a lot of the same columns and joins?

is there anyway you can use CTE to reference the "main" tables pre-joined?

it looks like these are your primary joins/references used in both. instead of 2 seperate derived tables, use left joins and filter differently?

Not sure why adding 1 as Served would add a significant amount of time to it however..


from  dbo.CD_Provider_Xref p 
				INNER JOIN dbo.CD_Provider_XREF x ON p.Provider_CD = x.Provider_CD
				INNER JOIN dbo.Assessment a		 on p.ProviderCode = a.Provider_CD
				LEFT OUTER JOIN dbo.Encounters e ON e.Provider_CD = p.Provider_CD 
				INNER JOIN dbo.CD_Agreement_Type ag ON ag.Agreement_Type_CD = a.Agreement_Type_CD

Can you not select the main columns needed/referenced as one SELECT?

Poor planning on your part does not constitute an emergency on my part.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2008-03-09 : 13:10:56

thanks for your comment
funny thing is the query ran fine yesterday without any delay..
only difference between yesterday and today is about 180000 records got loaded into a encouners table.. after that I rebuilt the indexes..
do i hae to update the statistic as well? it seems like if i change the encounter begin dt criteria ,the query runs forever..

for example, 7/1/07 ~ 7/31/07 runs 6 sec...
but if i change the data 8/1/07 ~ 8/30/07 it runs forever.. even though the those two months have similar row numbers.

the enconters table is a partioned table.. partioned by encounter begin dt.. can it be affected on the performance?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 13:32:37

yes, update statistics as a start.and then run. The partition may affect it, but if you rebuilt indexes and such..check fragmentation etc...

If this a stored procedure execute dbo.pStoredProcedureName WITH RECOMPILE

I still think it can run a lot faster by use a CTE and not needing 2 subselects. My guess is the query plan, despite indexes is still nailing table scans. I haven't had a lot of experience with 2005 and partiioned tables..so not sure about that impact.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources