Optimize a stored procedure using subquery-CodePudding

I'im working on optimization of stored procedure that uses a subquery. I need to replace this subquery to get less execution time.

code:

SELECT t1.*,
       detail= case when ( DC_ID in ( select DC_ID 
                                      from TableFinale1 
                                      where frais is not null 
                                      and WDT is not null 
                                      and type <> 'CDFS'
                                      )
                         )
                    then 'OK'
                    Else '' 
                    End       
into TableFinale2
From TableFinale1 t1

CodePudding user response：

You could switch to a JOIN, BUT... this will cause duplicate T1 records if there are more than 1 DC_ID that have a non-null frais, a non-null WDT, and a type<>'CDFS. So it's not exactly 1:1 unless your data fits the mold.

SELECT t1.*, CASE WHEN t2.DC_ID IS NOT NULL THEN 'OK' END
FROM TableFinale1 t1
   LEFT OUTER JOIN TableFinale1 t2
       ON t1.DC_ID = t2.DC_ID
       AND t2.frais is not null 
       and t2.WDT is not null 
       and t2.type <> 'CDFS'

CodePudding user response：

It is hard to predict performance changes without a query plan. If there are no indexes on TableFinale1.DC_ID, maybe a prepared filtered and indexed temporary table will help. Assuming DC_ID bigint:

create table #temp(DC_ID bigint not null primary key)

insert #temp 
    select distinct DC_ID 
    from TableFinale1 
    where 
        frais is not null 
        and WDT is not null 
        and type <> 'CDFS'
        
select t1.*, case when #temp.DC_ID is not null then 'OK' else '' end as [detail]
into TableFinale2
From 
    TableFinale1 t1
    left join #temp on #temp.DC_ID=t1.DC_ID