My Query takes Ages but works....How can I speed it up?

  • Thread starter Thread starter John Ortt
  • Start date Start date
J

John Ortt

I have a database in access which is incredibly slow to run. Essentially it
downoads my bank statements from an online bank and adds them all up by date
to give me my balance on any given day. As I don't have the transactions
since I joined the bank there is one "Balance Correction" at the start but
aside from that everything is working.....It is just very slow.

Can anybody think of a more graceful way of doing the following?

SELECT [Q:Data].TransDate, Sum([Q:Data].[TransAmount]*-1) AS
Amount, -1*(SELECT SUM([TransAmount]) FROM [Q:Data] as [Q:Data_1] WHERE
[Q:Data_1].[TransDate] <= [Q:Data].[TransDate] and
[Q:Data_1].[Account]=[Q:Data].[Account]) AS Balance, [Q:Data].Account
FROM [Q:Data]
GROUP BY [Q:Data].TransDate, [Q:Data].Account;

Thanks in Advance,

John
 
Hi,



Can give a try to:

SELECT a.TransDate,
a.Account,
-SUM(a.TransAmount) As Amount,
-SUM(b.TransAmount) AS Balance

FROM [Q:Data] As a INNER JOIN [Q:Data] As b
ON b.Account=a.Account AND b.TransDate<=a.Transdate

GROUP BY a.TransDate, a.Account



In general, joins are faster than correlated sub queries, and x*SUM(y) is
faster than SUM(x*y) when x is a constant. Having an index on TransDate and
another one on Account could help.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top