Cache version: Cache for Windows (x86-64) 2017.2.1 (Build 801_3U)
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
DECLARE QRS CURSOR FOR 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 , - ? , CURRENT_DATE ) GROUP BY A . RecNo , T . SDSInstID , T . TranEffectDate
Relative cost = 119327452
- Call module C, which populates temp-file B.
- Read temp-file B, looping on the hashing subscript.
- For each row:
- Output the row.
- Call module B, which populates bitmap temp-file A.
- Read bitmap temp-file A, looping on ID.
- For each row:
- Read master map SDS_Data._Transaction.IDKEY, using the given idkey value.
Read index map SDS_Data.DataFeed.BSBAccountNoIndex, using the given %SQLUPPER(BankAccountBSB) and %SQLUPPER(BankAccountNo), and looping on ID.
For each row:
Read master map SDS_Data.DataFeed.IDKEY, using the given idkey value.
Check distinct values for RecNo, %SQLUPPER(SDSInstID), and TranEffectDate using temp-file B,
subscripted by a hashing of these values.
For each distinct row:
Add a row to temp-file B, subscripted by the hashing,
with node data of InternalTXID, TranEffectDate, %SQLUPPER(SDSInstID), and RecNo.
Update the accumulated count(InternalTXID) in temp-file B,
subscripted by the hashing.
- Read bitmap index SDS_Data._Transaction.TranEffectDateIndex, looping on TranEffectDate and bitmap chunks.
- For each bitmap chunk:
- OR the bitmap chunk into bitmap temp-file A.
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,