We have an application where in I have 65 GB of Data in MSSQL Server.
with around 250 tables and 1000 stored procedures and functions.
Now the application is complete DB specific with almost all the logic coded in procedures and functions. Some of the Stored procs take as long as over 4-5 minutes to execute. Now we have been given the task of optimizing/re-engineering these slow running stored procs.
We have not much info about the project/schema/design but we have access to the schema and data and we fortunately have to deal with just a module to optimize which is slow. (But that deals with many SPs and functions running over 1000 of lines.. encompassing application logic..)
My question is how do I get started with such a project. We have been set some unrealistic deadline of coming up with fixes in 2-3 days and i have already spent a day in setting things up!
What should be the approach:
- Suggest increase in hardware infrastructure.
- Re-engineer app (push some of the computations to the app side) make it less DB-centric ?
- Ask for more time (how much) to optimize this ? Funny thing is we are not the original coders and have very less idea about the App i.e. whats coded in the SPs and functions.