Access DSum slowing down query dramatically

Posted on 2011-10-06
Last Modified: 2012-05-12

In the Access query shown below the DSum is slowing it down from 3 seconds to about 40 seconds. Is there a way to get around this?
SELECT Transactions.SupplierName, Transactions.DocType, Transactions.Reference, Transactions.Amount, Nz(DSum("Amount","Transactions","Reference = '" & [Reference] & "' And DocType = 'Payment'"),0) AS Paid, [Amount]-[Paid] AS Due INTO [Q3 Supplier Invoices ALL]
FROM Transactions
WHERE (((Transactions.DocType)="Invoice"))
ORDER BY Transactions.SupplierName, Transactions.Reference;

Open in new window

Question by:murbro
    LVL 77

    Accepted Solution

    You could try building a separate saved query (as below) that calculates the sums and then using a left join from transactions to the saved query.

    Select Reference, sum(Amount) as Paid where DocType = 'Payment'

    Author Closing Comment

    Excellent. Had done something similar. Its amazing how DSum affects performance
    LVL 92

    Expert Comment

    by:Patrick Matthews
    All of the domain aggregates are hell on performance.  You should avoid using them unless there is no other way :)

    Author Comment

    Thanks very much. I was trying to be too clever and the performance didn't sit well with the client :)
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

      <<All of the domain aggregates are hell on performance.  You should avoid using them unless there is no other way :) >>

      This really is not true.  The Domain functions can be as fast or faster then other methods if their used in the way they were intended to be used; queries is NOT one of them.

      They should never be used in a qeury (SQL Statement) as a Domain function in a query is un-optimizable by the query processor.  Poor performance will always result.

      All they represent is a SQL statement anyway, so there is no reason to be using them in a query.

      Domain functions were intended to be used in places where an expression is allowed, but a SQL statement is not (say for a default value property).


    LVL 92

    Expert Comment

    by:Patrick Matthews

    Quite right.  I meant to say "avoid using them IN QUERIES".



    Author Comment

    Thank you for the advice. Much appreciated

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now