Nested loop performance issues

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 2000 Forums

Transact-SQL (2000)

Nested loop performance issues

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-07 : 08:49:47

Ian writes "Hi,

I wrote an ad-hoc report for a customer that had as one of its criteria a need to limit the output to only those jobs that had had any activity logged against them during 2004. Our DB has several tables where such activity can be logged, so I ended up using several WHERE EXISTS clauses and self-joins to achieve this. Unfortunately, these nested loops end up being extreme resource hogs and my query takes nearly 30 minutes to run! If someone could suggest any alternatives to this self-join method, I would appreciate it. When I look at the estimated execution plan in Enterprise Manager, the nested loops take up about 60% of the total resources...

Here is the query (a long one!) - the nested loops are at the end:

SELECT distinct SUBMITS.submit_id as Candidate_ID, UPPER(SUBMITS.lastname + ', ' + SUBMITS.firstname) as Applicant_Name,

(case /* If the status is numeric in the history table, return the value stored in the TBLLKUPStatus
status table, else show the status in the history table */
when TBLLKUPSTATUS.StatusId is null then History.Status
else TBLLKUPSTATUS.StatusName
end) Last_Status,

CAST(HISTORY.lastdate as VARCHAR(11)) as Last_Status_Date,

JOBS.job_id as Job_ID, JOBS.title as Job_Title, JOBS.req as Job_Req, JOBS.totalpositions as Total_Positions,

(select JOBS.totalpositions - (select count(place_id) from Placement where job_id = Jobs.job_id)) as Open_Positions,

CLIENTS.clientname as Department,

(case
when EEODETAILEDTABLE.sex IS NULL
then 'Unknown'
else EEODETAILEDTABLE.sex
end) Sex,

(case
when EEODETAILEDTABLE.Profile_Id = 1 then 'American Indian or Alaskan Native'
when EEODETAILEDTABLE.Profile_Id = 2 then 'Asian'
when EEODETAILEDTABLE.Profile_Id = 3 then 'Black or African American'
when EEODETAILEDTABLE.Profile_Id = 4 then 'Hispanic or Latino'
when EEODETAILEDTABLE.Profile_Id = 5 then 'Native Hawaiian or Pacific Islander'
when EEODETAILEDTABLE.Profile_Id = 6 then 'White'
when EEODETAILEDTABLE.Profile_Id = 7 then 'American Indian/Alaskan Native and White'
when EEODETAILEDTABLE.Profile_Id = 8 then 'Asian and White'
when EEODETAILEDTABLE.Profile_Id = 9 then 'American Indian/Alaskan Native and Black'
when EEODETAILEDTABLE.Profile_Id = 10 then 'Black/African American and White'
when EEODETAILEDTABLE.Profile_Id = 11 then 'Non Disclosed Race'
else 'Unknown'
end) Race,

JOBS.state Job_State, JOBLIBRARY.jobcode as Job_Code, JOBLIBRARY.salarygrade as Salary_Grade, USERS.userlastname +', ' + USERS.userfirstname as Recruiter_Name,
MANAGERS.lastname + ', ' + MANAGERS.firstname as Manager_Name,

(select MAX(lastdate) from history
where status in ('1', 'Submitted, sent Job Description, and sent Comments', 'Submitted and sent Comments',
'Submitted and sent Job Description', 'Submitted') and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Submit,

(select MAX(CAST(lastdate as VARCHAR(11))) from history
where status = '3' and job_id = Jobs.job_id and submit_id = Submits.submit_id)
as Screen,

(select MAX(CAST(lastdate as VARCHAR(11))) from History
where status = 'Sent To Manager' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Sent_to_Mgr,

(select MAX(CAST(lastdate as VARCHAR(11))) from History
where status = '4' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Interview,

(select MAX(CAST(lastdate as VARCHAR(11))) from History
where status = '7' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Declined,

(select MAX(CAST(lastdate as VARCHAR(11))) from History
where status = '5' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Offer,

(select MAX(CAST(lastdate as VARCHAR(11))) from History
where status = '110' and job_id = Jobs.job_id and submit_i

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 09:28:23

well one option is to do an inner or left join for each value in select:

select t1.col1, t2.col2, t3.col3, ....
from Table1 t1
inner join Table1 t2 on (t1.Id = t2.Id)
inner join Table1 t3 on (t1.Id = t3.Id)
...
where ...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-07 : 10:47:22

you need to crosstab your dates, not select from your history table over and over. There's probably 100 examples here at the site on how to do this.

Also, use a lookup table to translate your numbers to text descriptions! never use a long case like that. that's why you have a relational database, to store related data.

Also -- why are you casting your dates to VARCHAR's and then selecting the max? What date format does this give? if it is m/d/yy then you are getting wrong results in your query.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-07 : 10:50:13

more info on the crosstab:

Replace all this:


(select MAX(CAST(lastdate as VARCHAR(11))) from History 
where status = 'Sent To Manager' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Sent_to_Mgr,

(select MAX(CAST(lastdate as VARCHAR(11))) from History 
where status = '4' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Interview,

(select MAX(CAST(lastdate as VARCHAR(11))) from History 
where status = '7' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Declined,

(select MAX(CAST(lastdate as VARCHAR(11))) from History 
where status = '5' and job_id = Jobs.job_id and submit_id = Submits.submit_id) as Offer,

with this subquery:


select job_id, submit_id, 
   MAX(CASE WHEN status='Sent To Manager' THEN lastdate ELSE Null END) as Sent_to_Mgr,
   MAX(CASE WHEN status='4' THEN lastdate ELSE Null END) as Interview,
   MAX(CASE WHEN status='7' THEN lastdate ELSE Null END) as Declined,
   MAX(CASE WHEN status='5' THEN lastdate ELSE Null END) as Offer
from
  history
group by job_id, submit_id

and just join to that subquery on job_id and submit_id.

Also: what is up with status being a single digit in some cases and a text description in others? You really need to look at this database design and try to make it more consistent.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources