CODEWITHSUNDEEP
sql-serverstored-proceduresprepared-statement
Fanda
Fanda

Reputation: 3786

Makes sense to call stored procedure as prepared statement?

I have SQL Server stored procedure to insert or update some data (do some logic). I want to call it from .net application multiple times to insert/update many rows.

Does it make sense to call stored procedure as prepared statement, when stored procedure is already compiled and expecting just parameters?

Upvotes: 0

Views: 752

Answers (1)

marc_s
marc_s

Reputation: 755013

Any stored procedure, like a parametrized query, too, will have to be parsed once before it's executed by SQL Server.

During that parsing, an execution plan is determined and stored in the plan cache of SQL Server, and any subsequent calls to that stored procedure (or parametrized query) will reuse that cached execution plan. That cached execution plan stays in memory until SQL Server restarts, or until the cache has to be cleared out due to memory pressure.

So basically - there's no way around the fact that the stored procedure (or parametrized query) will have to be parsed once (to determine the execution plan) - once that's done, the execution plan is in memory and can be reused. This also means: the first call to a stored procedure (since the last SQL Server restart) will always be a bit slower - after all, more work needs to be performed - and any subsequent calls should be noticeably faster.

Upvotes: 2

Related Questions