I am looking for the total balance for all accounts that had a negative balance as of a specific point in time. I've already got the code below, and it works, but works horribly.
The transactions table contains 20 million rows. The query below takes about 30 seconds which isn't bad, but there's a second part in that I need to repeat @report_date for the first of every month since the inception of the database, which balloons the execution time to ~30 minutes.
This is on MSSQL2008 and I'm not getting any missing indexes warnings on the execution plan, but I suspect my problem is still with the indexes so I'm intentionally leaving them off of here.
CREATE TABLE transactions( transaction_id int, account_id int, department_id int, location_id int, post_date date, amount money ); SELECT t2.department_id, t2.location_id, SUM(t2.credit_balances) FROM ( SELECT t1.department_id, t1.location_id, t1.account_id, SUM(t1.amount) as credit_balances FROM transactions t1 WHERE t1.post_date < @report_date GROUP BY t1.department_id, t1.location_id, t1.account_id HAVING SUM(t1.amount) < 0 ) t2 GROUP BY t2.department_id, t2.location_id;
The table contains 38 distinct
department_id,location_id combinations, and 4.5 million distinct