I recently discovered a report that ran for over 7 hours on a regular basis.
So I opened up the code.
Turns out the database is read only, replicated, no permissions to add indexes.
So I reviewed the Stored Procedure.
First I consolidated the code as 1 Sproc called another.
Then formatted the code so it was legible.
Then my co-worker and I reviewed the query for table scans.
Sure enough, he suggested to use Tables Variables instead of direct calls to the database.
So I did that, in seven places, and add Clustered indexes.
The thing about it these reside in memory as opposed to temp tables which get written to disk.
I also added a few indexes to the existing #Temp tables, as well as cleaned up the code.
When the new code ran, I was expecting it to run for a while.
When results returned in a few minutes I was real surprised.
So next step will be to validate the data and push new code to production.
I believe the code is getting cached to the SSRS server, however, not for long.
Soon the user will be able to query the replicated data within minutes instead of waiting for the report to refresh over night.
Another way speed up the query is to use Derived Tables, which embeds the temp table in actual code within the SQL statement, I've seen good results with this before.
And there you have it.