July 22, 2016 at 4:51 am
Hello All,
We have a table who have lots of data in it so we created table partition with date column with clustered index on date column.
We are updating some columns of this table task wise on daily basis.
For e.g.
Task 1 : Updating 2 columns
Task 2 : Updating 5 columns
Task 3 : Updating 3 columns
Below is the example of Task 1 :
UPDATE [SAC].[dbo].[SO]
SET [IntUpdtDtm] = 'Jul 20 2016 7:08AM'
,[ProvDimID] = prov.PDID
FROM [SAC].[dbo].[SO] observ
INNER JOIN [SS].[dbo].[WCD] cd on cd.ODGUID= observ.ODGUID
AND Observ.EDtm >= 'Aug 6 2013 3:55PM' AND Observ.EDtm <= 'Aug 1 2016 12:00AM'
INNER JOIN [SAC].[dbo].[SPDim] prov ON prov.ProvGUID = cd.UserGUID
AND cd.CreatedWhen BETWEEN prov.StartDtm AND prov.EndDtm
1) Previously we were updating all the columns in one go which was taking long time so now we are now updating in Tasks (1,2,3) wise.
2) Because of the clustered index for the table partition. The query execution plan takes 11 to 18 % for clustered index update and that is for every Tasks (1,2,3)
3) We are also updating this table records month/ quarter wise in loop so every Task also repeats for 2 or 3 times
Is there any other way to make performance for this update statements (Tasks 1,2,3 )
Any suggestion would be great help.
Thanks
July 22, 2016 at 4:59 am
Is the clustered index on the column you're updating? That's going to cause major pain. Aside from that, can't really help without seeing the (actual) execution plan.
John
July 22, 2016 at 5:04 am
We are not updating the date column on which we have clustered index. I think even though it will update as we are updating other columns
July 22, 2016 at 5:08 am
Excellent. So which column is the clustered index on? Please post the execution plan as requested, plus DDL (including indexes) for all tables.
John
July 22, 2016 at 8:29 am
FROM [SAC].[dbo].[SO] observ
INNER JOIN [SS].[dbo].[WCD] cd on cd.ODGUID= observ.ODGUID
AND Observ.EDtm >= 'Aug 6 2013 3:55PM' AND Observ.EDtm <= 'Aug 1 2016 12:00AM'
INNER JOIN [SAC].[dbo].[SPDim] prov ON prov.ProvGUID = cd.UserGUID
AND cd.CreatedWhen BETWEEN prov.StartDtm AND prov.EndDtm
Try simplifying your joins to only use the actual columns the two tables have in column. Move the date ranges down to your where clause. My rule of thumb is that joins are only for connecting columns in common between two tables . Keep the joins simple.
FROM [SAC].[dbo].[SO] observ
INNER JOIN [SS].[dbo].[WCD] cd on cd.ODGUID= observ.ODGUID
INNER JOIN [SAC].[dbo].[SPDim] prov ON prov.ProvGUID = cd.UserGUID
WHERE Observ.EDtm >= 'Aug 6 2013 3:55PM' AND Observ.EDtm <= 'Aug 1 2016 12:00AM'
AND cd.CreatedWhen BETWEEN prov.StartDtm AND prov.EndDtm
Please let us know if this helps.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 22, 2016 at 8:51 am
Second thought: Do your tables have indexes on ODGUID,ProvGuid, and UserGuid (where those are the first columns in the index) ?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply