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.
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
> 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.
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.