Performance and Tempdb questionw with Complex Query

[pabowen]

Hello everyone, first of all thanks for your help up front.

I have a complex update statement that runs once a month against a large db, it was never very fast but we recently moved from SQL 2000 to 2005. The speed has drastically decreased, and it is filling up the Tempdb. On occasion the system is failing due to this.

I am looking for any indications as to what is happening. I believe it has to do with my joining to select statements. There are three instances, one of the statements returns only 700 rows, the other two are the same sql statement and they return 504k rows.

Could anyone shed some light on any missteps or improvements they can suggest?

Here is the code (sorry it is long):

Update skill_event set xprof = 3
from skill_event 
inner join skill skill on skill_event.yskillkey = skill.yskillkey
inner join skill_profile_dtl on skill_event.yskillkey = skill_profile_dtl.code
inner join 
		(Select skill_profile_dtl.yprofkey, skill.yskillkey, skill.xskillcode from skill skill
		inner join skill_profile_dtl skill_profile_dtl on skill.yskillkey = skill_profile_dtl.code
		where substring(skill.xskillcode, 1, 3) <> 'stq') 
		as NonSignOffSkills on skill_profile_dtl.yprofkey = NonSignOffSkills.yprofkey

inner join skill_event skill_event2 on NonSignOffSkills.yskillkey = skill_event2.yskillkey 
	and skill_event.yuniquekey = skill_event2.yuniquekey
inner join skill_profile_dtl skill_profile_dtl2 on skill_event2.yskillkey = skill_profile_dtl2.code
	and skill_profile_dtl.yprofkey = skill_profile_dtl2.yprofkey

inner join 
		(Select max(xdate) MaxXDate, yuniquekey, yskillkey from skill_event group by yuniquekey, yskillkey) 
		as LatestSkill on skill_event2.yskillkey = LatestSkill.yskillkey 
		and skill_event2.yuniquekey = LatestSkill.yuniquekey
		and skill_event2.xdate = LatestSkill.MaxXDate

left outer join skill_event skill_event3 on skill_profile_dtl2.equiv1 = skill_event3.yskillkey 
	and skill_event.yuniquekey = skill_event3.yuniquekey

inner join 
		(Select max(xdate) MaxXDate, yuniquekey, yskillkey from skill_event group by yuniquekey, yskillkey) 
		as LatestRecurrent on LatestRecurrent.yskillkey = skill_event3.yskillkey 
		and LatestRecurrent.yuniquekey = skill_event3.yuniquekey
		and skill_event3.xdate = LatestRecurrent.MaxXDate
where 
-- following lines ensure only sign off skills with a current qualified status (4) are set to 3 (Expired)
substring(skill.xskillcode, 1, 3) = 'stq'
and skill_event.xprof = 4 

and
-- Processes skills with no recurrent event
(
 (
  skill_event2.xprof <= 8
  and (skill_profile_dtl2.equiv1 is null or skill_profile_dtl2.equiv1 = 0)
  and (skill_profile_dtl2.must_have = 1)
  )
-- Processes skills with recurrent events

or 
  (
   skill_event2.xprof<=8
	and (skill_profile_dtl2.equiv1 is not null and skill_profile_dtl2.equiv1 <> 0)
	and (skill_profile_dtl2.must_have = 1)
	and (skill_event3.xprof <> 9 or skill_event3.xprof is null)
  )
)

Thanks,
Patrick

 

[tlbroadbent]

First - Are all columns used in joins and where clauses indexed? If not, make it so.

Second - Use of functions in where clauses can degrade performance, especially if the column is indexed. Eliminate the SUBTRING function. Change

substring(skill.xskillcode, 1, 3) = 'stq'

TO skill.xskillcode LIKE 'stq%'

everywhere it is used.

Third - Make sure statistics are up-to-date on the database.

Fourth - Often, you can improve performance by converting the sub-queries to temporary tables. That is take the sub-queries out of the update statement. Use them to insert into temporary tables. Then join the temporary tables where the sub-queries were.

Fifth - Review the query execution plan to find the highest cost parts of the query. Focus on these parts. Check for table or index scans rather than index seeks.

Sixth - Use the Database Engine Tuning Advisor to further tune the query and indexes.

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)

 

[gmmastros]

I'd like to re-iterate Terry's point number 3. This is critical. I encourage you to run sp_updatestats .

Also, I recommend you look at the compatibility mode for your database?

In SQL Server Management Studio...

Right click your database -> Properties
Click Options, check your compatibility mode.

You should be running under SQL 2005 compatibility, unless you have a compelling reason not to.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom