DML query Optimization in sql server
I have a question about SQL Server. I have 3 tables :

Targettable
sourcetable
sourcetable2
Target table and source table have 35 columns and all are varchar datatype. I want Implement incremental concept (scd) type 2 in SQL Server using a MERGE statement.

Source table has data : 3 and half core records

Few off condition to implement scdtype 2 concept

I need fetch sourcetable1 table which are records not available sourcetable2 that records only
col4, col7, col8, col9, col10, col11 columns frequently change data we need to maintain history and latest
I have done these steps:

Handled null/empty values for all columns
I Created nonclustered index for col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11 Reason for nonclustered index to fetch records fastly
Apply merge statment to implement scdtype2 concept.
Query looks like this

INSERT INTO Test.[dbo].[targettable]
([col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
,col34,col35)
    SELECT 
       [col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
,col34,col35
      ,[Flag],[RecordValidFromDate],[RecordExpiredDate]
  FROM (
  merge   Test.[dbo].[targettable]  target
  using( 
select * from 
 (select [col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
,col34,col35
,
ROW_NUMBER()over(partition by  col1 order by col5 desc,col6 desc)as rn 
from    Test1.[dbo].[sourcetable] a
where   col2 !='820' and isnull(col3,'') !='')a

where a.rn=1 and not exists 
(select 1 from  Test1.[dbo].[sourcetable2] b where a.col1=b.pcol1)
 ) stag 
on target.[col1]=stag.[col1]
when not matched then
insert ([col1],[col2],[col3],[col4],col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,
         col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col31,col32,col33
         ,col34,col35,[Flag],[RecordValidFromDate],[RecordExpiredDate]
    )values 
        ( CASE WHEN coalesce(ltrim(rtrim(stag.[col1])),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.[col1])) END
        ,CASE WHEN coalesce(ltrim(rtrim(stag.col2)),'') = '' THEN '1800-01-01' ELSE ltrim(rtrim(stag.col2)) END
        , CASE WHEN coalesce(ltrim(rtrim(stag.col3)),'') = '' THEN '1800-01-01' ELSE ltrim(rtrim(stag.col3)) END
        ,  CASE WHEN coalesce(ltrim(rtrim(stag.col4)),'')= '' THEN 'NA' ELSE ltrim(rtrim(stag.col4)) END

         ------similary way I done upto 35 columns to hanndel empty and null value for all columns-----------------------------------------
        , CASE WHEN coalesce(ltrim(rtrim(stag.col35)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col35)) END
        ,'1',getdate(),'1800-01-01'
                  )
when matched and target.flag=1 and 
                 (CASE WHEN coalesce(ltrim(rtrim(target.col4)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col4)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col4)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col4)) END
        or CASE WHEN coalesce(ltrim(rtrim(target.col7)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col7)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col7)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col7)) END
        orCASE WHEN coalesce(ltrim(rtrim(target.col8)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col8)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col8)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col8)) END
        or CASE WHEN coalesce(ltrim(rtrim(target.col9)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col9)) END
        <>CASE WHEN coalesce(ltrim(rtrim(stag.col9)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col9)) END
        or CASE WHEN coalesce(ltrim(rtrim(target.col10)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col10)) END
                <>CASE WHEN coalesce(ltrim(rtrim(stag.col10)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col10)) END
        or  CASE WHEN coalesce(ltrim(rtrim(target.col11)),'') = '' THEN 'NA' ELSE ltrim(rtrim(target.col11)) END
        <> CASE WHEN coalesce(ltrim(rtrim(stag.col11)),'') = '' THEN 'NA' ELSE ltrim(rtrim(stag.col11)) END
                 )
       then update set target.flag='0',target.[RecordExpiredDate]=getdate()
       output $ACTION ACTIONOUT,
       stag.[col1],stag.[col2],stag.[col3],stag.[col4],stag.col5,stag.col6,stag.col7,stag.col8,stag.col9,stag.col10,stag.col11,stag.col12,stag.col13,stag.col14,
stag.col15,stag.col16,
stag.col17,stag.col18,stag.col19,stag.col20,stag.col21,stag.col22,stag.col23,stag.col24,stag.col25,stag.col26,stag.col27,stag.col28,stag.col29,
stag.col30,stag.col31,stag.col32,stag.col33,stag.col34,stag.col35,'1' as flag,getdate() as RecordValidFromDate,'1800-01-01' as [RecordExpiredDate]
       )AS MERGE_OUT
       WHERE MERGE_OUT.ACTIONOUT='UPDATE';


When I ran above query For first time it is executed with in 3 minutes. After that I ran it the 2nd time it takes more than 2 hours, and some time its 1 hour 50 minutes.

Could you please tell me where I need to change query or what step I need to implement in the above query to give best performance and reduce execution time in sql server.
September 11th, 2015 12:22pm

It may cause by the complex execution plan in SQL Server. Please check the plan to get some clue.

Also, you may need split the long query into to two.

1. To make the mergy part into a table variable.

2. To make the insert statement to us the table variable.

I wish it helpful.

Runner288

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 12:51pm

I Created nonclustered index for col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11
> Reason for nonclustered index to fetch records fastly

Fetch rows from which table?

- Covering index in source table with [col1] as leading.

- Unique clustered index in target table.

Optimizing MERGE Statement Performance
https://technet.microsoft.com/en-us/library/cc879317(v=sql.105).aspx

- For the comparison of the columns participating in the history tracking, use the operators EXCEPT / INTERSECT instead all those CASE/COALESCE/OR.

WHEN MATCHED AND T.flag=1 AND EXISTS (
    SELECT T.col4, T.col7, T.col8, ..., T.col11
    EXCEPT
    SELECT S.col4, S.col7, S.col8, ..., S.col11
    ) THEN
    ...

It is readable and it takes care of the columns that are nullable.

- Be sure you have proper indexes (POC - partition / ordering / covering) in the source table to support the ranking (avoid SORT operator in the exec plan). It could help dumping the interesting source rows into a table and add the proper index to support the MERGE operation.

September 11th, 2015 3:43pm

yes I  done given suggestions as well as I changed coalesce to is null function. still I am facing same issue .could you please tell me what steps I need to follow further to give more performance of above query.

Free Windows Admin Tool Kit Click here and download it now
September 12th, 2015 12:41pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics