Power of ‘OVER’ Clause and Window Functions in SQL Server
In systems with high transaction rate, the performance is one of the main concerns. A problem can be solved in various ways which all of them are correct but when implementing them in a production environment the difference will be noticeable in terms of resource usage. We always try to find out solutions that would improve system performance. I am going to explain how SQL Server’s ‘OVER’ clause impact on our performance.
Lately, we have been struggling with a problem in our project which manages thousands of financial transactions per day. My task was related to a part of the project where a transaction must be registered. From a system design perspective, every user has an account where the actual balance is stored. A transaction consists of multiple operations such as deposit and withdrawal.
To register a transaction we should execute these tasks in order: (To simplify the problem I have ignored any validations)
1- Pick one transaction detail from input data.
2- Register it in the database.
3- Update balance of related account.
4- Go to step 1.
Note: The whole operation must be done as a single database transaction which means either all operation should be successful or all of them should fail.
Suppose the state of accounts are like below and we want to register a transaction contains multiple deposit and withdrawal.
Consider the following details in the transaction.
Withdraw 1000 from A1 account
Withdraw 2000 from A1 account
Withdraw 2000 from A1 account
Withdraw 15000 from A1 account
Withdraw 5000 from A2 account
Withdraw 7000 from A3 account
Deposit 25000 into A4 account
Deposit 5000 into A5 account0
Deposit 2000 into A1 account
After registering these operations, the form of the database will be the following.
Changes on accounts:
Changes on account transactions:
As you can see, after insertion of each detail record, the final account balance must be inserted too and also there might be multiple deposit and withdrawal on the same account which all of them must be registered in order.
Solution 1 — Using Cursors
Go through input items one by one and register every detail following by an update on the related account balance.
SQL Server cursors are slow and resource consuming. It is recommended to avoid using cursors unless there is no other solution. You will see another solution without using cursors.
Solution 2 — ‘OVER’ clause and Window Functions:
These features give us a lot of abilities and you can read more about them on this link. Using them you can do some operations on some sets of data during a select command which are really hard to implement without them.
I am going to insert transaction details using an Insert … Select … statement instead of a cursor which is by far more efficient. But the main issue is calculating new balance after each deposit or withdrawal. Because new balance should be obtained from previous transaction details on that account, not the account balance.
The problem will be solved if we calculate the current row balance based on the balance of the previous row.
New Balance = Previous Balance + Current Amount
And how it would be possible using ‘OVER’ clause?
First of all, join input rows with Account table to access account balance in each details record then calculate the new balance in every record with the following formula:
New Balance = Account balance + Sum of all previous transaction details’ amount on this account + current row amount
You can see the full source code below:
Result of the Select statement is like the following:
Now I explain some important parts of the above query.
Partition By I.AccountId: Builds multiple sets of data rows that are grouped by AccountId.
Rows Between Unbounded Preceding And Current Row: Defines a range into the partitioned set that includes all previous rows until current row.
Sum(I.Amount) Over: accumulates all amounts of the defined range.
Now it just a simple Insert into the TransactionDetails table to complete this step.
In the next step, all related accounts will be updated by a single Update statement from input data which are grouped by AccountId.
To clear the difference between these solutions in terms of performance, I executed each solution 3000 times repeatedly and for 3 tries. You can see the noticeable difference in the statistics below.
Using Cursor statistics:
Using ‘OVER’ clause statistics:
‘OVER’ clause and Window Functions may be complicated at first glance but when you master their concept and syntax you can improve the performance of your systems considerably.
Hope this article will be helpful.
