CODEWITHSUNDEEP
sql-serverado.net
mns
mns

Reputation: 683

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

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.

Upvotes: 1

Views: 320

Answers (1)

meda
meda

Reputation: 45500

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.

Upvotes: 2

Related Questions