Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to speed up select...
Finance | Project Operations, Human Resources, ...
Answered

How to speed up select sum

(0) ShareShare
ReportReport
Posted on by Tony Mok 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?
 
Categories:
Development / Customization / SDK
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,155 Moderator 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 225,155 Moderator 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 225,155 Moderator 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 225,155 Moderator 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 225,155 Moderator 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

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

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

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Jahanvi Profile Picture

Jahanvi 34

#2
PJ JohnPaul Profile Picture

PJ JohnPaul 5

#3
SudhanshuMSID Profile Picture

SudhanshuMSID 1

Overall leaderboard

Featured topics

Test Comment 26-09-2024 16:14pm

Product updates

Dynamics 365 release plans