Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

How to speed up select sum

(0) ShareShare
ReportReport
Posted on by 577
SettleAmountCur needs to be summed up in VendTrans, and the code is called
select sum(SettleAmountCur) from _vendTrans where _vendTrans.Invoice == invoiceId
 
However, it takes much time(~2500 seconds for 13,000 calls), are there ways to speed up?
 
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    How to speed up select sum
    This was an example to prove you that you that you don't need one query for each invoice. Of course that I didn't mean that you hard-code invoices IDs in code.
     
    If you have a list of IDs, you can range to a query at runtime or join vendTrans with a (temporary) table containing IDs, or maybe combine this query with your other one. I can't be specific because I haven't got almost any information about your scenario.
  • Verified answer
    Tony Mok Profile Picture
    Tony Mok 577 on at
    How to speed up select sum
    I cannot change the code like
    select sum(SettleAmountCur) from vendTrans
        group by Invoice
        where vendTrans.Invoice == 'A'
           || vendTrans.Invoice == 'B';
    as I don't know the specific value, the invoice numbers come from previous while select.
     
    I found out another way to speed up(~2500 seconds > ~700 seconds). I created a tempdb table and insert data by insert_recordset, and use query with addSelectionField of SelectionField::Sum.
  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    How to speed up select sum
    The fact that there are 13000 invoices doesn't necessarily mean that you must run 13000 queries. You can have a single query returning 13000 rows, or something in between.
     
    For example, this gets the sum for every invoice in a single query:
     
    select sum(SettleAmountCur) from vendTrans
        group by Invoice;
     
    Or this query returns numbers for two specific invoices at once.
    ​
    select sum(SettleAmountCur) from vendTrans
        group by Invoice
        where vendTrans.Invoice == 'A'
           || vendTrans.Invoice == 'B';
  • Tony Mok Profile Picture
    Tony Mok 577 on at
    How to speed up select sum
    As there are 13,000 invoices, the calls cannot be reduced.
    I wonder query can increase the speed compared select sum.
  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    How to speed up select sum
    Your code gives you the same result as before. If you didn't filter by InvoiceId, it would return sums for all InvoiceIds, but because you filter InvoiceId by a single value, you get sum just for one, which is what you had before.
     
    I suggested it as an example of how you could potentially reduce the number of individual queries (13000). Your change wouldn't make a difference, because you'd still run 13000 queries.
  • Tony Mok Profile Picture
    Tony Mok 577 on at
    How to speed up select sum
    The code below is what you mean?
    select sum(SettleAmountCur) from _vendTrans group by _vendTrans.Invoice where _vendTrans.Invoice == invoiceId
  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    How to speed up select sum
    No, I didn't say anything about adding "order by". I said you can look at the execution plan to understand what's going on, and mentioned a few things you could possible influence.G There is no sorting, but you're trying to find records by InvoiceId field, therefore it's important to have an index for it.
     
    'group by' may allow you to return data for several invoices at one, grouped by InvoiceId.
  • Tony Mok Profile Picture
    Tony Mok 577 on at
    How to speed up select sum
    The select statement is so simple and I created a stopwatch to count time.
    "Ensure that there is an index, check its fragmentation and ensure that statistics are all right." Does it mean that I add order by in the select statement?
    The query needs to be executed 13,000 as there are 13,000 invoices in a month to create a batch inquiry.
    Can you explain how to use sum with group by?
     
  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    How to speed up select sum
    You can analyze the execution plan to see how the query is executed and what consumes most time. But the query is very simple, so there isn't much to do, IMHO. Ensure that there is an index, check its fragmentation and ensure that statistics are all right.
     
    Also, consider whether you need to execute the query thirteen thousand times. Can you, for example, use sum with group by?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans