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.
I signed up for the Hortonworks Certified Associate exam last Thursday. Figured if I sign up, I'd have to take the test. And if I tak...
Saw a post today on Twitter, " Microsoft releases CNTK, its open source deep learning toolkit, on GitHub " This is big news. Be...
It seems like open source applications are the mainstream today. So many new products delivered through Aache foundation. Some do this. S...