Performance stored procedure update multi-coulmn sql

Question

I have a stored procedure the every 5 min run in sqlserver high cost iO/cpu do you have any idea to tune this query

ALTER PROCEDURE [dbo].[doc_Letters_SyncBulkUpdate]
@TrackerStatusSync BIT
AS
 UPDATE doc_Letters SET
 [CentralDeptNo] = tempLetters.[CentralDeptNo],
 [IncomingNo] = tempLetters.[IncomingNo],
 [IncomingDate] = tempLetters.[IncomingDate],
 [SequenceNo] = tempLetters.[SequenceNo],
 [Subject] = tempLetters.[Subject],
 [Summary] = tempLetters.[Summary],
 [Keywords] = tempLetters.[Keywords],
 [DeliverFrom] = tempLetters.[DeliverFrom],
 [DeliverTo] = tempLetters.[DeliverTo],
 [DeliverDate] = tempLetters.[DeliverDate],
 [DeliverComments] = tempLetters.[DeliverComments],
 [SourceFlag] = tempLetters.[SourceFlag],
 [Type] = tempLetters.[Type],
 [LetterNo] = tempLetters.[LetterNo],
 [Security] = tempLetters.[Security],
 [LetterType] = tempLetters.[LetterType],
 [Indicator] = tempLetters.[Indicator],
 [AttachmentCount] = tempLetters.[AttachmentCount],
 [BodyFileCount] = tempLetters.[BodyFileCount],
 [WordDocCount] = tempLetters.[WordDocCount],
 [SenderTitle] = tempLetters.[SenderTitle],
 [ToReceiverList] = tempLetters.[ToReceiverList],
 [Date] = tempLetters.[Date],
 [PartitionFactor] = tempLetters.[PartitionFactor],
 [Priority] = tempLetters.[Priority],
 [RegisterarUserFullName] = tempLetters.[RegisterarUserFullName],
 [RegisterationDate] = tempLetters.[RegisterationDate],
 [Archived] = tempLetters.[Archived],
 [ModificationServerGuid] = tempLetters.[ModificationServerGuid],
 [ModificationDate] = tempLetters.[ModificationDate],
 [Age] = tempLetters.[Age],
 DelivererCellPhone=tempLetters.DelivererCellPhone,
 TrackingStatusID=
 CASE
 WHEN @TrackerStatusSync = 1 THEN doc_Letters.TrackingStatusID
 ELSE tempLetters.[TrackingStatusID] 
 END 
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [SecurityID] = security.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_SecurityLevels security ON security.[Guid] = tempLetters.[SecurityGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [LetterTypeID] = letterType.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_Permanents letterType ON letterType.[Guid] = tempLetters.[LetterTypeGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [IndicatorID] = indicators.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 doc_Indicators indicators ON indicators.[Guid] = tempLetters.[IndicatorGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [SecretarialID] = secretarials.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_Departments secretarials ON secretarials.[Guid] = tempLetters.[SecretarialGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [PriorityID] = priority.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_Priorities priority ON priority.[Guid] = tempLetters.[PriorityGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [RegisterarUserID] = users.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_Users users ON users.[Guid] = tempLetters.[RegisterarUserGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
IF(@TrackerStatusSync=1)
BEGIN
 UPDATE doc_Letters SET
 [TrackingStatusID] = trackers.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_TrackerStatuses trackers ON trackers.[Guid] = tempLetters.[TrackingStatusGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
END
 UPDATE doc_Letters SET
 [FirstRootInstanceOwnerID] = staff.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_Staff staff ON staff.[Guid] = tempLetters.[FirstRootInstanceOwnerGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [FirstRootInstanceOwnerSecretarialID] = firstRootInstanceOwnerSecretarial.[ID]
 FROM 
 doc_Letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON tempLetters.[Guid] = doc_Letters.[Guid] INNER JOIN
 com_Departments firstRootInstanceOwnerSecretarial ON firstRootInstanceOwnerSecretarial.[Guid] = tempLetters.[FirstRootInstanceOwnerSecretarialGuid]
 WHERE 
 tempLetters.[Age] >= doc_Letters.[Age]
 UPDATE doc_Letters SET
 [FirstRootInstanceID] = ISNULL(firstRootInstance.[ID], 0)
 FROM 
 doc_Letters letters INNER JOIN 
 doc_LettersSyncTemp tempLetters ON letters.[Guid] = tempLetters.[Guid] LEFT JOIN
 doc_LetterInstances firstRootInstance ON firstRootInstance.[Guid] = tempLetters.[FirstRootInstanceGuid]

• Gert Arnold: A good start would be Database Engine Tuning Advisor.
• iSR5: please post doc_Letters table schema. We need to know the columns.

Answer 1

It seems you need a view or a temporary table. But it would be best for you to use a view instead. so you might need to create a view first :

CREATE VIEW doc_Letters_view AS 
(
 SELECT 
 [CentralDeptNo] = tempLetters.[CentralDeptNo]
 , [IncomingNo] = tempLetters.[IncomingNo]
 , [IncomingDate] = tempLetters.[IncomingDate]
 , [SequenceNo] = tempLetters.[SequenceNo]
 , [Subject] = tempLetters.[Subject]
 , [Summary] = tempLetters.[Summary]
 , [Keywords] = tempLetters.[Keywords]
 , [DeliverFrom] = tempLetters.[DeliverFrom]
 , [DeliverTo] = tempLetters.[DeliverTo]
 , [DeliverDate] = tempLetters.[DeliverDate]
 , [DeliverComments] = tempLetters.[DeliverComments]
 , [SourceFlag] = tempLetters.[SourceFlag]
 , [Type] = tempLetters.[Type]
 , [LetterNo] = tempLetters.[LetterNo]
 , [Security] = tempLetters.[Security]
 , [LetterType] = tempLetters.[LetterType]
 , [Indicator] = tempLetters.[Indicator]
 , [AttachmentCount] = tempLetters.[AttachmentCount]
 , [BodyFileCount] = tempLetters.[BodyFileCount]
 , [WordDocCount] = tempLetters.[WordDocCount]
 , [SenderTitle] = tempLetters.[SenderTitle]
 , [ToReceiverList] = tempLetters.[ToReceiverList]
 , [Date] = tempLetters.[Date]
 , [PartitionFactor] = tempLetters.[PartitionFactor]
 , [Priority] = tempLetters.[Priority]
 , [RegisterarUserFullName] = tempLetters.[RegisterarUserFullName]
 , [RegisterationDate] = tempLetters.[RegisterationDate]
 , [Archived] = tempLetters.[Archived]
 , [ModificationServerGuid] = tempLetters.[ModificationServerGuid]
 , [ModificationDate] = tempLetters.[ModificationDate]
 , [Age] = tempLetters.[Age]
 , DelivererCellPhone = tempLetters.DelivererCellPhone
 , TrackingStatusID = tempLetters.[TrackingStatusID]
 , [SecurityID] = security.[ID]
 , [LetterTypeID] = letterType.[ID]
 , [IndicatorID] = indicators.[ID]
 , [SecretarialID] = secretarials.[ID]
 , [PriorityID] = priority.[ID]
 , [RegisterarUserID] = users.[ID]
 , [FirstRootInstanceOwnerID] = staff.[ID]
 , [FirstRootInstanceOwnerSecretarialID] = firstRootInstanceOwnerSecretarial.[ID]
 , [FirstRootInstanceID] = ISNULL(firstRootInstance.[ID], 0
 , [Age] = tempLetters.[Age]
 , [Guid] = tempLetters.[Guid]
 FROM 
 doc_LettersSyncTemp tempLetters
 LEFT JOIN com_SecurityLevels security ON security.[Guid] = tempLetters.[SecurityGuid]
 LEFT JOIN com_Permanents letterType ON letterType.[Guid] = tempLetters.[LetterTypeGuid]
 LEFT JOIN doc_Indicators indicators ON indicators.[Guid] = tempLetters.[IndicatorGuid]
 LEFT JOIN com_Departments secretarials ON secretarials.[Guid] = tempLetters.[SecretarialGuid]
 LEFT JOIN com_Priorities priority ON priority.[Guid] = tempLetters.[PriorityGuid]
 LEFT JOIN com_Users users ON users.[Guid] = tempLetters.[RegisterarUserGuid]
 LEFT JOIN com_TrackerStatuses trackers ON trackers.[Guid] = tempLetters.[TrackingStatusGuid]
 LEFT JOIN com_Staff staff ON staff.[Guid] = tempLetters.[FirstRootInstanceOwnerGuid]
 LEFT JOIN com_Departments firstRootInstanceOwnerSecretarial ON firstRootInstanceOwnerSecretarial.[Guid] = tempLetters.[FirstRootInstanceOwnerSecretarialGuid]
 LEFT JOIN doc_LetterInstances firstRootInstance ON firstRootInstance.[Guid] = tempLetters.[FirstRootInstanceGuid]
)

Then use it in your store procedure, so your store procedure would be something like :

UPDATE doc_Letters
SET 
 [CentralDeptNo] = doc_Letters_view.[CentralDeptNo]
, [IncomingNo] = doc_Letters_view.[IncomingNo]
, [IncomingDate] = doc_Letters_view.[IncomingDate]
, [SequenceNo] = doc_Letters_view.[SequenceNo]
, [Subject] = doc_Letters_view.[Subject]
, [Summary] = doc_Letters_view.[Summary]
, [Keywords] = doc_Letters_view.[Keywords]
, [DeliverFrom] = doc_Letters_view.[DeliverFrom]
, [DeliverTo] = doc_Letters_view.[DeliverTo]
, [DeliverDate] = doc_Letters_view.[DeliverDate]
, [DeliverComments] = doc_Letters_view.[DeliverComments]
, [SourceFlag] = doc_Letters_view.[SourceFlag]
, [Type] = doc_Letters_view.[Type]
, [LetterNo] = doc_Letters_view.[LetterNo]
, [Security] = doc_Letters_view.[Security]
, [LetterType] = doc_Letters_view.[LetterType]
, [Indicator] = doc_Letters_view.[Indicator]
, [AttachmentCount] = doc_Letters_view.[AttachmentCount]
, [BodyFileCount] = doc_Letters_view.[BodyFileCount]
, [WordDocCount] = doc_Letters_view.[WordDocCount]
, [SenderTitle] = doc_Letters_view.[SenderTitle]
, [ToReceiverList] = doc_Letters_view.[ToReceiverList]
, [Date] = doc_Letters_view.[Date]
, [PartitionFactor] = doc_Letters_view.[PartitionFactor]
, [Priority] = doc_Letters_view.[Priority]
, [RegisterarUserFullName] = doc_Letters_view.[RegisterarUserFullName]
, [RegisterationDate] = doc_Letters_view.[RegisterationDate]
, [Archived] = doc_Letters_view.[Archived]
, [ModificationServerGuid] = doc_Letters_view.[ModificationServerGuid]
, [ModificationDate] = doc_Letters_view.[ModificationDate]
, [Age] = doc_Letters_view.[Age]
, DelivererCellPhone = doc_Letters_view.DelivererCellPhone
, TrackingStatusID = CASE WHEN @TrackerStatusSync = 1 THEN doc_Letters_view.[TrackingStatusID] ELSE doc_Letters.[TrackingStatusID] END
, [SecurityID] = doc_Letters_view.[SecurityID]
, [LetterTypeID] = doc_Letters_view.[LetterTypeID]
, [IndicatorID] = doc_Letters_view.[IndicatorID]
, [SecretarialID] = doc_Letters_view.[SecretarialID]
, [PriorityID] = doc_Letters_view.[PriorityID]
, [RegisterarUserID] = doc_Letters_view.[RegisterarUserID]
, [FirstRootInstanceOwnerID] = doc_Letters_view.[FirstRootInstanceOwnerID]
, [FirstRootInstanceOwnerSecretarialID] = doc_Letters_view.[FirstRootInstanceOwnerSecretarialID]
, [FirstRootInstanceID] = doc_Letters_view.[FirstRootInstanceID]
FROM 
 doc_Letters
JOIN doc_Letters_view ON doc_Letters_view.[Guid] = doc_Letters.[Guid]
WHERE 
 doc_Letters_view.[Age] >= doc_Letters.[Age]

A few other notes to optimize it, when using Views, you're taking advantage of the view's statistics to get the best possible execution plan. This is not enough though, you'll need to create indexes on Age and also another on Guid for doc_Letters and doc_LettersSyncTemp tables, and include all needed columns.

Another thought, if you can avoid the UPDATE and depend on the view instead, do that, it would be faster. As you're clearly only need a view to show the the record status based on Age which either an integer or datetime. So, you're updating it every 5 minutes !, while if you used a view, it would give you the same results, but there is no UPDATE action on the main table. Which would be faster!.

You can also use computed columns, which are some what partial views, these would be updated every time you query the table. The only disadvantage on that and on the view, is that the values are not stored since they will be updated on each time you querying it. It would be feasible to apply it on status columns, but it's not a good solution for data that meant to be stored, and logged when there is an update like user information or financial data.