CODEWITHSUNDEEP
sqlsql-server
Von Abanes
Von Abanes

Reputation: 716

optimize queries with a NOT IN (select...)

-- tf_wfget_appvr_records
create function [dbo].[tf_wfget_appvr_records] ( @cls_id int, @start_date datetime, @end_date datetime,  @approver_id int, @co_id varchar( 5))
returns @wfapp_approver_status
table
( app_recgid bigint,
  app_status varchar(1),
  app_status_desc varchar( 80)
)
as
begin
 insert into @wfapp_approver_status
 ( app_recgid, app_status, app_status_desc)
 select a.recgid, a.status, p.action
 from cm_wfapp a
  inner join cm_process p on a.proc_id = p.recgid
 where a.co_id = @co_id and a.cls_id = @cls_id  
  and a.apply_date between @start_date and @end_date
  and a.approver_id = @approver_id

 -- insert others from cm_wftrn
 insert into @wfapp_approver_status
 ( app_recgid, app_status, app_status_desc)
 select distinct a.recgid, a.status, p.action
 from cm_wftrn c
  inner join cm_process p on c.proc_id = p.recgid and p.action is not null
  inner join cm_wfapp a on a.recgid = c.wfapp_id 
 where a.co_id = @co_id and a.cls_id = @cls_id  
  and a.apply_date between @start_date and @end_date
  and c.wfapp_id not in ( select app_recgid from @wfapp_approver_status ) 
  and c.appr_id = @approver_id 
return
end
GO

It contain NOT IN constraint which is taking lot of time to extract result from database. The Database is huge obviously.

How can I optimize this query? Is there a way to optimize queries with a NOT IN (select...) ?

Please help thanks.

Upvotes: 0

Views: 76

Answers (1)

M.Ali
M.Ali

Reputation: 69524

Replace your NOT IN with NOT EXISTS Operator something like this....

 insert into @wfapp_approver_status
 ( app_recgid, app_status, app_status_desc)
 select distinct a.recgid, a.status, p.action
 from cm_wftrn c
  inner join cm_process p on c.proc_id = p.recgid and p.action is not null
  inner join cm_wfapp a on a.recgid = c.wfapp_id 
 where a.co_id = @co_id and a.cls_id = @cls_id  
  and a.apply_date between @start_date and @end_date 
  and c.appr_id = @approver_id 
  and not exists ( select 1 
                   from @wfapp_approver_status 
                   WHERE c.wfapp_id = app_recgid)

Upvotes: 2

Related Questions