SQL query with Count function running slow
Cache version: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U)
Good Afternoon,
I have a co-worker who is trying to run the below query via ODBC. The issue is that the query appears to be running extremely slow (nearly 2 hours).
SELECT A.RecNo, T.SDSInstID, T.TranEffectDate, COUNT(T.InternalTXID) as NoofTransactions
FROM SDS_DATA._Transaction T
INNER JOIN SDS_DATA.DataFeed A
on A.BankAccountBSB = T.BankAccountBSB
and A.BankAccountNo = T.BankAccountNo
WHERE T.TranEffectDate >= DATEADD(yy,-1,CURRENT_DATE)
GROUP BY A.RecNo, T.SDSInstID, T.TranEffectDate
Below is the generated query plan
|
|
Numbers wise there are approximately 50,611 datafeed records and 12,098,782 transaction records. The transaction table gorws by approx. 50,000 records a day.
Would anyone here have any advice as to how I can increase performance for this query?
Thanks in advance,
Jeremy