Yesterday, I was tasked with a projet to clean up an existing report.
First task was to move the report to a new database which I did.
Second step was to point one of the query databases to a new database, done.
Third, I was supposed to streamline the report because it took 3 hours to run.
After initial review of the stored procedure I studied the WHILE LOOP for a few minutes.
They could easily reproduce the same logic by adding a few lines of code, stripping out the While loop so I dug into the code and applied the changes.
I was shocked when the data returned in 2:30 minutes. After verifying the data, everything looked good.
So we moved to production and the SQL Agent job executed the Stored Procedure as expected and the report looked good.
However, this only satifies the first request. The second request is to modify the existing functionality in addition add some more queries to the report.
So on to the next tasks.
Goal is to move forward and upward.
And so it goes!