2
\$\begingroup\$

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]
\$\endgroup\$
2
  • \$\begingroup\$ A good start would be Database Engine Tuning Advisor. \$\endgroup\$ – Gert Arnold May 14 '20 at 7:14
  • \$\begingroup\$ please post doc_Letters table schema. We need to know the columns. \$\endgroup\$ – iSR5 Jun 10 '20 at 1:03
1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.