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?
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).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments