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
With such amount of data, does you have enough global buffers configured?
Is it only one slow place, or you have any others as well?
How big is your database, indexes globals and globals buffer?
Hello,
1. A deeper look on how you implement ed your indexes According to the "cost" it looks that the query is not optimized.
2. Do you have an index on "TranEffectDate" on "SDS_DATA._Transaction" table ?
3. I would recommend that you run this query (after improve of the "cost") with parallel processing.
In 2017 and above you can use %Parallel mentioned here: https://docs.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=GSQLOPT_optquery#GSQLOPT_optquery_parallel
Off the top of my mind I'm not positive how to incorporate into your query, but I have achieved enormous performance improvements using %Parallel in sub queries so it may be worth a few simple 'show plan' tests. As mentioned above ensuring you have the proper indexes would be my first step here as well.