CODEWITHSUNDEEP
sqlsql-serverstored-procedurescursor
Mike Flynn
Mike Flynn

Reputation: 24325

Cursor in Stored Procedure Performance Issues

I found a cursor being used in the below SQL and dynamic SQL. Profile brings up quite a bit of execution plans and I think it has to deal with this cursor. Is this a bad choice of SQL?

SET @SelectStmtSubHeader = 'SELECT DISTINCT
        dbo.dsb_testID(sh.GPCustomerID) AScursor -- RIGHT HERE
         PONumber,
        sh.GPCustomerID,
        .....

Upvotes: 1

Views: 563

Answers (2)

Jordan
Jordan

Reputation: 2758

Cursors are nearly always a bad choice to be avoided if alternatives exist in set logic.

SQL is based around set logic. They aren't meant to be iterated through like a collection.

The SQL Optimizers are usually pretty good at finding clever ways to retrieve your data. A cursor is a relatively unsophisticated tool. ANSI SQL does require it though, so it's usually present.

Here is a good example from Sybase

Cursor Performance Example

Upvotes: 3

MatBailie
MatBailie

Reputation: 86706

That's not an example of a cursor.

A cursor needs to be...

DECLARE this_is_a_cursor CURSOR
FOR
  SELECT
    stuff
  FROM
    a_query

The snipped code you've shown appears to use a scalar function to derive a value, which it aliases to the word cursor. But having a field called cursor doesn't make it a cursor.

Upvotes: 4

Related Questions