0

I have a cursor that does this in it's loop:

EXEC dbo.[ProcessHostVendorItems] @ItemId = @ItemId, @VendorNo =... etc

Would it be any benefit (likely via caching query plans?) to change this to:

DECLARE @SQL = "dbo.[ProcessHostVendorItems] @ItemId = @ItemId, @VendorNo =... etc"
sp_executesql @sql

I don't think it would help much since stored procedures are faster than inline SQL normally, right? I'm just running into dev constraints on what I can/can't touch in our 'core' so, I need to make up performance where I can.

This SP is hammered in a pretty big cursor on a regular basis so, even meager performance gains would be a plus.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Beau D'Amore
  • 3,174
  • 5
  • 24
  • 56
  • It could be faster if you built this dynamic sql with a select query executed all in one go (no cursor), the procedure actually being called will not gain any performance at all. – M.Ali Nov 04 '15 at 19:58
  • 1
    ***NO*** - a stored procedure isn't inherently faster than a properly parametrized inline SQL query. They both share the same steps to be analyzed and have an execution plan devised, their execution plan is cached in the same fashion. The "inherent" speed advantage of a stored procedure is a myth - no truth to it – marc_s Nov 04 '15 at 20:38
  • That's what I figured. I'm moving on to attempt a UDF/Cross Apply combo as a SET operation versus iteration. Thanks. – Beau D'Amore Nov 04 '15 at 21:23

0 Answers0