Post reply

How can I offload the compute of SUM operation ?

  • March 18, 2019 at 2:00 am

    #374647

    Hi,

    I have an interesting case. I have SQL resources governor configured with two different type of workloads : batch and users. Batch workload is limited to 20%. I have a batch job that is performing poorly because the performance are throttled. I would like to improve the performance of the queries before touching resource governor. I found that one of the query executed by the job has very high CPU time. The query is the following:

    SELECT SUM(T1.BALANCEPAYABLE)
        ,SUM(T1.TTV)
        ,SUM(T1.FARECASH)
        ,SUM(T1.FARECREDIT)
        ,SUM(T1.TAXCASH)
        ,SUM(T1.TAXCREDIT)
        ,SUM(T1.COMMISSIONAMOUNT)
        ,SUM(T1.TAXONCOMMISSIONAMOUNT)
        ,SUM(T1.PENALTYAMOUNT)
        ,SUM(T1.COMMISSIONVAT)
        ,SUM(T1.MARKETSUPPORT)
        ,SUM(T1.VENDAMOUNTCUR)
    FROM MYTABLE T1
    WHERE (
            (
                (PARTITION = 5637144576)
                AND (DATAAREAID = N'MATT')
                )
            AND (FILEREFRECID = @P1)
            )

    I do have an index on PARTITION, DATAAREAID, FILEREFRECID.

    Is there any way I could compute reduce the compute for the SUM operation ?

    Thank you

  • March 18, 2019 at 2:06 am

    #2024746

    I am also considering a covered index.

  • March 18, 2019 at 9:16 am

    #2024771

    Gamleur84 - Monday, March 18, 2019 2:06 AM

    I am also considering a covered index.

    How many rows are in the table? How many rows match the search criteria? What does the execution plan tell you, is the expected index being used?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 18, 2019 at 10:37 am

    #2024793

    ChrisM@Work - Monday, March 18, 2019 9:16 AM

    Gamleur84 - Monday, March 18, 2019 2:06 AM

    I am also considering a covered index.

    How many rows are in the table? How many rows match the search criteria? What does the execution plan tell you, is the expected index being used?

    To add to that, how many columns are in the table, what is the average width of the rows, and what is the page density and condition of the statistics for the indexes (including the clustered index you already have)?

    Also, how long is your query running for?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply