SQL API Cursors

• Ray Herring

  SSCertifiable

  Points: 5533

  More actions

  November 19, 2008 at 9:03 am

  #383423

  Hi All,

  Environment

  SQL 2000 SP4, Standard and Enterprise on Windows 2003 also Standard and Enterprise. Sometimes clustered.

  Background

  I have a C++ application that uses OLEDB to connect to several databases. All (well I think All) of the application SQL is located in “Accessor” classes. The SQL mostly consists of parameterized queries. What I see in profiler for these queries is similar to the following (edited for space)

  Profiler EventText Data - Abridged

  RPC:StartingDeclare @P1 int Set @P1=NULL ….. Exec sp_CursorOpen @P1 output, N’Select Site, VisitId, …. from VisitTable….where VisitId = @P2’, @P2 Ouput …….

  CursorOpen

  RPC CompletedDeclare @P1 (same as above starting)

  RPC Completedexec sp_CursorOption

  RPC Completedexec sp_CursorFetch

  RPC Completedexec sp_Cursor

  RPC Completedexec sp_CursorClose

  The sp_Cursor, sp_CursorFetch, and sp_CursorOption events may appear multiple times and in various orders. Sometimes the cursor is also used for inserts and updates.

  My questions relate to efficiency. Basically what I want to know is, are these API Cursors as efficient, less efficient, or more efficient than calling stored procedures? I see these API Cursor sequences a lot. I have a profiler run from a high end production site that has 23,000 Cursor Open events in 10 minutes. I estimate that as much as half of the database access is via a dozen or so specific instances of these.

  All of the events from CursorOpen to CursorClose typically complete in one or two ms. I do occasionally see the fetch statements run longer.

  So, Is it worth my time and effort to track down the source of these queries and convince the developer to use a stored procedure?

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  November 19, 2008 at 10:21 am

  #900627

  What you have posted looks like what the ADO objects do when you open recordset objects. They open cursors and iterate through them as needed.

  Is this efficient? Usually not - especially when compared to writing procedures that can do set-based updates.

  Is it a place of concern? Not necessarily. It does (usually) effectively use the built in locking mechanisms in the MSSQL engine - which are often ignored when developers create custom stored procedures causing concurrency issues.

  So, the answer really depends on the application. It is not a situation where the development is clearly done wrong.