Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Unanswered: performance of update query

    I am using the following update query in SP. But this query is taking long time to execute . Is there any way to tune this? PS_ARB_OB_CHG_SUM and PS_ARB_OB_SKU_REPO REPO contains huge records


    Update PS_ARB_OB_CHG_SUM
    Set PROCESSED_FLG ='IGNR',
    PROCESS_DTTM = getdate()
    from PS_ARB_OB_CHG_SUM PACS
    Inner Join PS_ARB_OB_CHG_TMP TMP ON PACS.PMI_MESSAGE_ID = TMP.PMI_MESSAGE_ID
    WHERE
    PACS.PROCESSED_FLG= 'N' And
    NOT Exists
    (Select 'X' from PS_ARB_OB_SKU_REPO REPO
    Where ( TMP.PMI_MESSAGE_ID = REPO.ARB_CREATED_MSG_ID
    OR TMP.PMI_MESSAGE_ID = REPO.ARB_LASTUPD_MSG_ID
    OR TMP.PMI_MESSAGE_ID = REPO.ARB_HOLD_MSG_ID))

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The performance bottleneck is most likely located here :
    Code:
    (Select 'X' from PS_ARB_OB_SKU_REPO REPO
    Where ( TMP.PMI_MESSAGE_ID = REPO.ARB_CREATED_MSG_ID
    OR TMP.PMI_MESSAGE_ID = REPO.ARB_LASTUPD_MSG_ID
    OR TMP.PMI_MESSAGE_ID = REPO.ARB_HOLD_MSG_ID))
    Replace it by
    Code:
    (Select 'X' 
    from PS_ARB_OB_SKU_REPO REPO
    Where TMP.PMI_MESSAGE_ID = REPO.ARB_CREATED_MSG_ID
    UNION ALL
    Select 'X' 
    from PS_ARB_OB_SKU_REPO REPO
    Where TMP.PMI_MESSAGE_ID = REPO.ARB_LASTUPD_MSG_ID
    UNION ALL
    Select 'X' 
    from PS_ARB_OB_SKU_REPO REPO
    Where TMP.PMI_MESSAGE_ID = REPO.ARB_HOLD_MSG_ID)
    Check if it improves performance.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm not so familiar with Microsoft SQL Server. So, I might miss the point.

    I couldn't see why PS_ARB_OB_CHG_SUM was repeated in from clause,
    because the column values of joined table PS_ARB_OB_CHG_TMP was not used in set clause.

    I thought the following statement might be equivalent to your update statement.
    Code:
    Update PS_ARB_OB_CHG_SUM PACS
       Set PROCESSED_FLG = 'IGNR'
         , PROCESS_DTTM  = getdate() 
     WHERE PROCESSED_FLG = 'N'
       AND EXISTS
          (SELECT 0
            FROM  PS_ARB_OB_CHG_TMP TMP
            WHERE PACS.PMI_MESSAGE_ID = TMP.PMI_MESSAGE_ID
          )
       AND NOT EXISTS
          (Select 0
            from  PS_ARB_OB_SKU_REPO REPO 
            Where PACS.PMI_MESSAGE_ID
                  IN ( REPO.ARB_CREATED_MSG_ID
                     , REPO.ARB_LASTUPD_MSG_ID
                     , REPO.ARB_HOLD_MSG_ID
                     )
          )
    ;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •