• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Access DSum slowing down query dramatically

Hi

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?
Thanks
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

0
Murray Brown
Asked:
Murray Brown
1 Solution
 
peter57rCommented:
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'
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Excellent. Had done something similar. Its amazing how DSum affects performance
0
 
Patrick MatthewsCommented:
All of the domain aggregates are hell on performance.  You should avoid using them unless there is no other way :)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much. I was trying to be too clever and the performance didn't sit well with the client :)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Patrick,

  <<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).

Jim.



 
0
 
Patrick MatthewsCommented:
Jim,

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

:)

Patrick
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you for the advice. Much appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now