sql server - Which is faster in SQL, While loop, Recursive Stored proc, or Cursor? -

which faster in sql, while loop, recursive stored proc, or cursor? want optimize performance in couple of spots in stored procedure. code i'm optimizing formats strings output file.

i'll assume using sql server.

first of all, said in statements, recursive stored procs, while possible, not idea in sql server because of stack size. so, recursive logic break. however, if have 2-3 levels of nesting @ best, might try using recursion or using cte, bit recursive (sql server 2005 , up). once manage wrap head around cte, it's immensely useful technique. haven't measured, i've never had performance issues in few places used cte.

cursors on other hand big performance hogs, (and half internet) recommend not use them in code called often. cursors more classical programming structure, akin foreach in c#, people find easier at, understand , maintain sql code uses cursors data manipulation, on convoluted multiple-inner-select sql monstrosity, it's not worst idea use them in code called once in while.

speaking of while, transfers programming mindset set-based one, procedure-based one, while it's relatively fast , not consume lots of resources, can still dramatically increase number of data manipulation statements issue database itself.

to summarize, if had make complex stored proc performance paramount i'd try:

1. using set-based approach (inner selects, joins, unions , such)
2. using cte (clear , manageable experienced user, bit shady beginner)
3. using control-flow statements (if, while...)
4. using cursors (procedural code, easy follow)

in order.

if code used less often, i'll move 3 , 4 before 1 , 2, but, again, complex scenarios use lots of tables, , lots of relations. of course, ymmv, i'd test whatever procedure make in real-world scenario, measure performance, because, can talk until blue in face fast , slow, until real measurements, it's sex convention @ catholic monastery*

and, not forget, code fast data. there no substitution indexing.

• that analogy lot funnier couple of years ago, it's bit sad.