Need Help Speeding Up a SQL Server Query

Question

I have a stored procedure that creates quite a few temp tables in memory. I have the following query which takes an extremely long time to run (7 minutes).

select 
  a.DEPT,
  a.DIV,
  a.PART,
convert(datetime,convert(varchar(2),datepart("mm",a.Release_Date))+'/1/'+ convert(varchar(4),datepart("yyyy",a.Release_Date)),101) as rptng_mnth
from @tmpReportData3 a
where not exists
(select distinct DEPT,DIV,PART from @tmpReportData4 b
where a.DEPT = b.DEPT and a.DIV = b.DIV and a.PART = b.PART)
order by rptng_mnth

Is there a way to speed this up?

http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan look at the execution plan, create indexes, etc... — Doon, Oct 08 '15 at 14:37
you don't needs the distinct in the subquery. Also check if the slow part is due the table variables creation. Last check mem pressure issues and you cna test using temp tables instead variables — jean, Oct 08 '15 at 14:40
did you check the execution plan where you spend most of the time? and its hard to help you if you don't tell about count of rows, unique, primary keys — A ツ, Oct 08 '15 at 14:43
1) your using a subselect where a left join would probably be more appropriate (LEFT JOIN b ON WHERE b.dept IS NULL. 2) Make sure your two tables are indexed by the 3 columns you are joining on and by the column you're ordering by. — AlVaz, Oct 08 '15 at 14:50
Let me try to answer the questions:1. I can't change or test the plan because I don't have access to SQL Agent. 2. The whole SP starts off by reading a single table and then creating multiple temp tables in memory. The original table is about 100K rows. 3. How do you do the LEFT JOIN...WHERE IS NULL when you need to match multiple columns? Would it be: LJ a.1=b.1, a.2=b.2, a.3=b.3 Where ??? — FlyFish, Oct 08 '15 at 15:00
Does it actually use a lot of table _variables_ instead of temporary tables? — Morpheus, Oct 08 '15 at 15:07

score 1 · Accepted Answer · answered Oct 08 '15 at 15:20

This is your query, with the unnecessary select distinct removed from the subquery:

select a.DEPT, a.DIV, a.PART,
       convert(datetime,convert(varchar(2),datepart("mm",a.Release_Date))+'/1/'+ convert(varchar(4),datepart("yyyy",a.Release_Date)),101) as rptng_mnth
from @tmpReportData3 a
where not exists (select DEPT, DIV, PART
                  from @tmpReportData4 b
                  where a.DEPT = b.DEPT and a.DIV = b.DIV and a.PART = b.PART
                 )
order by rptng_mnth;

Your performance problem is probably caused by the not exists. Writing the query using left join might provide some benefit. But, the easiest approach is to switch from using a table variable to a temporary table, #tmpReportData4. Then add an index on the temporary table: #tmpReportData4(dept, div, part).

Using temp table instead of table variable, and adding in an index dropped the execution time down to milliseconds. Awesome, thanks! — FlyFish, Oct 08 '15 at 15:53
@FlyFish . . . From 7 minutes to 1 second, so that's better than 99.7% improvement. Cool. — Gordon Linoff, Oct 08 '15 at 20:59

score 1 · Answer 2 · answered Oct 08 '15 at 15:33

A good start would be to change the "not in" to a left join.

You might also consider using "#" (rather than "@") temp tables, because you can index #-tables.

Can you include the complete stored procedure?

select 
  a.DEPT
 ,a.DIV
 ,a.PART
 ,convert(datetime,convert(varchar(2),datepart("mm",a.Release_Date))+'/1/'+ convert(varchar(4),datepart("yyyy",a.Release_Date)),101) as rptng_mnth
from
  @tmpReportData3 a
  left join @tmpReportData4 b on b.dept = a.dept and a.div = b.div and a.part = b.part
where b.dept is null
order by
  a.rptng_mnth

score 0 · Answer 3 · answered Oct 08 '15 at 15:06

0

Try to re-create the sp, but no use any temp table, no use cursors.. that works for me. Also you can post your whole sp code. :)

answered Oct 08 '15 at 15:06

Erick Lanford Xenes

1,467
2
20
34

Need Help Speeding Up a SQL Server Query

3 Answers3