Its always a balance when creating reports, you want to produce an amazing report, but you dont want to cripple your server and make it unresponsive for other users whilst it is produced.
After a lot of research, we found that adding:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
as the first line in a stored procedure greatly improves performance for other users as the stored procedure then doesn’t lock tables for reading when it is producing the report.
As a real-world example, we wrote a system that could produce an annual statistical report that had to go through 1.1million records 18 times per row, and for around 40,000 rows, so a staggering 792 billion reads on the database. We optimised the query, but it still took around 40 seconds to produce, and during that time all the CPUs in the database server were at 100% and disk IO went through the roof, and the system performance suffered dramatically. Other users were left with the system appearing to hang for up to a minute whilst one user created the report.
By adding the transaction level statement we not only decreased the query time to under 30 seconds, but it also allowed all other users to continue to use the system and not notice any performance hang in the system as their queries were still be executed as needed since the tables were not locked.