Approach to speedup DB-centric app

| | August 7, 2015

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:

  1. Suggest increase in hardware infrastructure.
  2. Re-engineer app (push some of the computations to the app side) make it less DB-centric ?
  3. 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.


One Response to “Approach to speedup DB-centric app”

  1. You’ll need to know the problem areas before you can attempt any fixes.

    You say you are just looking at one module to begin with, then I’d suggest using things like SQL Profiler to determine the frequency with which statements are executed and also times taken to execute and use this data as a starting point to see if the logic can be optimised.
    Look for any operations that use cursors that could possibly benefit from a more set based approach.

    As for your three options, I’d say you HAVE to go for (3) because you’ve stated you don’t have a thorough understanding of the app, so you’ll need to gain some further exposure in order to establish where to focus your efforts. I don’t think (1) is a long term solution although it would obviously provide some benefit (how much determines current and proposed specs). You’ll only have an idea if (2) is a valid option once you’ve had a chance to establish the problem areas first.

    Best of luck.

Leave a Reply