I would write the raw data to a very basic table without indexes or constraints, not even a primary key.
If you can, insert many rows at once, that's faster than single-row inserts.
If you can afford loosing some data in a catastrophic event, make that an unlogged table, that's faster.
If you can afford loosing some data in a catastrophic event and all inserts can run in a single session, make that a temporary table, that's even faster. Probably not possible, though.
Start a new partition every time unit of your choice (daily?) and aggregate the old partition into permanent storage at some opportune moment.
As you are repeating this query for multiple months then you will be continually re-aggregating the same rows.
For example the rows in the first month will always be brought back by the t1.post_date < @report_date
criteria so will be re-processed for every month.
To avoid this I'd probably consider working through it in an iterative way a month at a time from the start. Dependent on the volatility of historic data I might also consider storing the pre-calculated results in the database rather than re-calculating these each month.
To calculate this at run time you could create a temporary table with the following structure.
CREATE TABLE #balance
(
department_id INT NOT NULL,
location_id INT NOT NULL,
account_id INT NOT NULL,
balance_to_date MONEY NOT NULL,
PRIMARY KEY (department_id, location_id, account_id)
);
You could also consider adding the following index on your transactions
table
ALTER TABLE transactions
ADD post_date_year_month AS (10000 * YEAR(post_date) + MONTH(post_date))
CREATE INDEX ix
ON transactions(post_date_year_month, department_id, location_id, account_id)
INCLUDE (amount)
Then extract a month at a time from transactions
and merge into #balance
(with a when matched then increment, when not matched insert).
The leading post_date_year_month
column means that as long as you write the query sargably the extraction of each month can be done efficiently and the extracted rows for a month will be ordered by department_id, location_id, account_id
making a merge join against #balance
possible without a sort.
Whilst that could benefit this particular query you'd need to assess the utility of this index against your overall workload.
Then calculate the department_id, location_id
totals from #balance
(can leverage the PK order to avoid a sort) and store those somewhere and move onto the next month.
(Or possibly instead of #balance
you could use a "temporary" permanent table balance
and create an indexed view on that to avoid the separate explicit aggregation step and just copy the values straight from that before moving on)
Best Answer
You'll have to create a special table to contain the sum.
Then create triggers on the original table that add values that are inserted, subtract values that are deleted, do both for an update and set the value to zero on truncate.
You can see a small example of that for the
count
aggregate in my blog post. Different from that example you will have to have a summation table with one sum per"PlayerId"
andstatus
.