CODEWITHSUNDEEP
sql.netsql-server
Matt M
Matt M

Reputation: 1435

Executing stored procedures in succession from inline SQL

I've run across code where someone wants to create a dynamic table, run a select statement to populate that table, then run a series of stored procedures using that table's data. All of this is done using inline SQL from .NET code.

What they've basically done is (pseudo code):

declare table;

insert IDs into table

exec storedprocedure table
exec storedprocedure table
exec storedprocedure table
exec storedprocedure table

In my opinion, and I don't have anything to stand on, is that this probably isn't as efficient as creating a stored procedure that does all of this and the .NET code just invokes that encapsulating stored procedure.

Are there performance hits or gains from either approach?

Upvotes: 0

Views: 188

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Are there performance hits or gains from either approach?

Not normally.

The only real difference (which is very unlikely to matter) is the size of the request data sent to the SQL Server, and the cost of hashing the TSQL batch to determine if there's a cached plan for it.

Once a cached plan is found for the TSQL batch, execution is the same as if an RPC request had invoked a stored procedure.

Another potential issue if this batch is run at a very high frequency is that the temp table metadata will not be cached. So if you're running this 1000s of times/sec and have a single tempdb file you can get metadata page latch contention in TempDb.

This is the kind of thing that sort of mattered a little a very long time ago, but which now is only noticable in edge cases.

Upvotes: 2

Related Questions