Does Sql Server cache stored procedure execution plans when calling from c#?

Question

I am executing a stored procedure with one parameter in a foreach loop. I get the result and do some extra processing after that. Here's the code:

foreach(var id in GetIds())
{
    var result = ExecuteStoreProcedureForResult(id);
    //do extra processing with the result
}

When I execute the sp in directly from Query Analyzer the first time it takes 4-5 seconds. Every time after that the query the procedure returns in milliseconds, even with different ID parameters — much faster than the first time.

My question here is if I execute this sp from code, will the query plan be cached and return results faster after the first execution., in same way it did with Query Analyzer?

I am opening the connection once, executing the sp for each parameter, and finally closing the connection. Will that make a difference?

All suggestions appreciated.

Query Analyzer indicate Sql Server 2000, which is no longer supported. It's irresponsible to still use that version of Sql Server. In later versions, you connect with Sql Server Management Studio. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 16, 2014 at 19:24

Joel Coehoorn · Accepted Answer · 2014-04-16 19:25:10Z

2

Query execution caching is done at the SQL server level, it does not matter which programming language calls the stored procedure.

As for the suggestion, you should be fine running an SP multiple time, I do suggest you not to close connection yourself.You haven't showed the code but I advise you to take advantage of IDisposable rather than manually opening and closing connection each time which could be quite expensive.

edited Apr 16, 2014 at 19:25

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

answered Apr 16, 2014 at 19:16

meda

45.5k14 gold badges96 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Does Sql Server cache stored procedure execution plans when calling from c#?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related