Post reply

SQL API Cursors

  • 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?

  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply