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 department_id,location_id,account_id
.
dept,location
combinations anddept,location,account
combinations?post_date,department_id,location_id,account_id INCLUDE (amount)
You'd need to maintain a temp table with clustered PK ondepartment_id,location_id,account_id
and a balance_to_date column. Then extract a month at a time and merge into that table. And calculate your monthly total fordepartment_id, location_id