CODEWITHSUNDEEP
sqlperformancet-sqlstored-procedures
Bob L
Bob L

Reputation: 47

T-SQL optimizing performance of various stored procedures question

so I have written several stored procedures that act on individual rows of data by taking in an ID number. I would like to keep several stored procedures that can call this stored procedure at different levels of my database scheme. For instance, when a row is inserted I call this stored procedure. When something else is modified I would like to call this stored procedure for each line. This is so I can have one set of base code that can be called everywhere else but that acts on different amounts of data. I have been able to produce this result with Cursors, but I am told these are very inefficient. Is there any other way to produce this kind of functionality without sacrificing performance? Thanks.

Upvotes: 1

Views: 232

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300559

Yes. Use standard joins to operate on sets rather than RBAR (Row By Agonising Row). i.e. Rather than call a function for each row, design a join that performs the required operation on every applicable row as a set operation.

I often see devs use the 'function operates on a each row', and although this seems to be the obvious way to encapsulate logic, it doesn't perform well on SQL Server or most DB engines.

In some circumstances, a table-valued function can be used effectively (MS SQL Server).

(BTW, you are correct in saying cursors are inefficient).

Upvotes: 1

Related Questions