Performance gains in stored procs for long running transactions

Question

I have several long running report type transactions that take 5-10 minutes. Would I see any performance increase by using stored procs? Would it be significant?

each query runs once a night.

Answer 1

yes, the query plan for stored procs can be optimized and even if it can't procs are preferred over embedded sql

"would you see any performance improvement" - the only way to know for certain is to try it

in theory, stored procedures pre-parse the sql and store the query plan instead of figuring out each time, so there should be some speedup just from that, however, i doubt it would be significant in a 5-10 minute process

if the speed is of concern your best bet is to look at the query plan and see if it can be improved with different query structures and/or adding indices et al

if the speed is not of concern, stored procs provide better encapsulation than inline sql

Answer 2

Probably not. Stored procs give you the advantage of pre-compiled SQL. If your SQL is invoked infrequently, they this advantage will be pretty worthless. So if you have SQL that is expensive because the queries themselves are expensive, then stored procs will gain you no meaningful performance advantage. If you have queries that are invoked very frequently and which themselves execute quickly, then it's worth having a proc.

Answer 3

Most likely not. The performance gains from stored procs, if any (depends on your use case) are the kind that are un-noticable in the micro -- only in the macro.

Reporting-type queries are ones that aggregate LOTS of data and if that's the case it'll be slow no matter how the execution method. Only indexing and/or other physical data changes can make it faster.

See:

http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-moder#59932

Answer 4

As others have said, you won't see much performance gain from the stored procedure being pre-compiled. However, if your current transactions have multiple statements, with data going back and forth between the server, then wrapping it in a stored procedure could eliminate some of that back-and-forth, which can be a real performance killer.

Look into proper indexing, but also consider the fact that the queries themselves (or the whole process if it consists of multiple steps) might be inefficient. Without seeing your actual code it's hard to say.

Answer 5

The short answer is: no, stored procedures aren't going to improve the performance. For a start, if you are using parameterised queries there is no difference in performance between a stored procedure and inline SQL. The reason is that ALL queries have cached execution plans - not just stored procedures. Have a look at http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

If you aren't parameterising your inline queries and you're just building the query up and inserting the 'parameters' as literals then each query will look different to the database and it will need to pre-compile each one. So in this case, you would be doing yourself a favour by using parameters in your inline SQL. And you should do this anyway from a security perspective, otherwise you are opening yourself up to SQL injection attacks.

But anyway the pre-compilation issue is a red herring here. You are talking about long running queries - so long that the pre-compliation is going to be insignificant. So unfortunately, you aren't going to get off easily here. Your solution is going to be to optimise the actual design of your queries, or even to rethink the whole way you are aproaching the task.