Access DSum slowing down query dramatically

Posted on 2011-10-06
Medium Priority
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:Murray Brown
LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 36922870
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

by:Murray Brown
ID: 36923146
Excellent. Had done something similar. Its amazing how DSum affects performance
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36923250
All of the domain aggregates are hell on performance.  You should avoid using them unless there is no other way :)
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

by:Murray Brown
ID: 36923383
Thanks very much. I was trying to be too clever and the performance didn't sit well with the client :)
LVL 58
ID: 36923744

  <<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 93

Expert Comment

by:Patrick Matthews
ID: 36928447

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



Author Comment

by:Murray Brown
ID: 36928888
Thank you for the advice. Much appreciated

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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