I have been reading about the performance between subqueries and domain functions when applied in a query although both are slow,
I've always found that in the discussion it is the subquery that has an extra edge in terms of performance aspect.
However, recently I've created a running sum on a query intended to be used as the recordset of a form: I have tried these two(2) methods:
Method A - Domain Function
Method B - Subquery
Performance Result:
Method A - .05 seconds
Method B - 20 seconds
Both does the job as intended.
But method B (subquery) is excruciatingly slow and sometimes "very seldom" it crashes ms access 2007.
Now, basing from the various articles and discussions i have found in the web; it is the subquery which is supposed to be faster than the two.
What am i doing wrong with my subquery snippet, why has it become as slow as molasses? Is there anything i can do to make this query any faster?
I've always found that in the discussion it is the subquery that has an extra edge in terms of performance aspect.
However, recently I've created a running sum on a query intended to be used as the recordset of a form: I have tried these two(2) methods:
Method A - Domain Function
Code:
SELECT tblReceivables.*, DSum("Credit","tblReceivables","TransactionID = " & [TransactionID] & " AND ReceivableID <= " & [ReceivableID])-NZ(DSum("Debit","tblReceivables","TransactionID = " & [TransactionID] & " AND ReceivableID < " & [ReceivableID] & " AND [Active] = 0"),0) AS Balance
FROM tblReceivables
ORDER BY tblReceivables.ReceivableID DESC;Method B - Subquery
Code:
SELECT tblReceivables.*, (SELECT SUM(Credit) FROM tblReceivables as REC WHERE REC.TransactionID = tblReceivables.TransactionID AND REC.ReceivableID <= tblReceivables.ReceivableID) - NZ((SELECT SUM(Debit) FROM tblReceivables as REC WHERE REC.TransactionID = tblReceivables.TransactionID AND REC.ReceivableID < tblReceivables.ReceivableID AND Active = False),0) AS Balance
FROM tblReceivables
ORDER BY tblReceivables.ReceivableID DESC;Performance Result:
Method A - .05 seconds
Method B - 20 seconds
Both does the job as intended.
But method B (subquery) is excruciatingly slow and sometimes "very seldom" it crashes ms access 2007.
Now, basing from the various articles and discussions i have found in the web; it is the subquery which is supposed to be faster than the two.
What am i doing wrong with my subquery snippet, why has it become as slow as molasses? Is there anything i can do to make this query any faster?
