SQL Server Performance

CursorType and RecordCount

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by TheGios, Oct 28, 2004.

  1. TheGios New Member

    According to a performance tip found on this website, if a recordset is used only to return data, the following is more performant:

    - CursorType = adForwardOnly
    - CursorLocation = adUseServer
    - LockType = adLockReadOnly
    - CacheSize = 1

    No problem for me BUT with CursorType = adForwardOnly the RecordCount is always -1!!!!!!

    Any solution? What about CursorType = adOpenStatic?

    thanks
  2. Argyle New Member

    I've gotten this question a few times so I've written down a longer reply. I'll paste it here.

    -------------

    You basically have four types of server side cursors and one type of client side cursor when working with ADO.

    A cursor stores the results of your query and depending on what type of cursor you use, you have different functionality and performance. ADO accesses the cursor via the RecordSet object. With server side cursors the data is stored in TempDB in SQL Server, with a client side cursor the data is stored in the ADO's own cursor engine located on the client's memory (say a web server) which usually is much much faster if you don't work with too much data.

    1. Forward-only (adOpenForwardOnly)
    When you use server side cursors this is the default one. By default the CacheSize property will be set to 1. This means that ADO will grab one row each time from the cursor. You can only move forward in it (rs.MoveNext) and you can't do for example rs.RecordCount. This since you only grab one row (or whatever your CacheSize are) at a time and thus don't know how many rows that satisfied your query.

    A forward-only cursor set to read only and cachesize of 1 is also called a firehose cursor. This is default when using ADO. Note that server side cursors also are default.

    2. Static (adOpenStatic)
    When specifying a static cursor, the entire cursor will be populated by the results of the query. All rows will end up in the cursor (and not one by one as with Forward Only) so you can do rs.RecordCount here. You can also move backwards in this cursor type.

    You get a snapshot result so you won't be able to see changes in the data made by other users. As the name applies the data is static (read-only). If you want to update data use a keyset or dynamic cursor. Note that that this only applies if you want to do updates via the RecordSet object in ADO. Best way (in a web environment) is often not to do this but instead grab the data as a client side cursor, disconnect and then issue a new SQL update call in a stored procedure when specific data needs to be changed. But it depends on your application.

    3. Keyset (adOpenKeyset)
    Like a static cursor except that it will only populate the cursor with the primary key of the data that your query generated. This means that the cursor holds information to locate your data, so you can issue updates. The keys themselves will be static so you can do rs.RecordCount.

    You can open a keyset cursor as either updateable or read only. In either way, you will be able to see changes made to this data by other users. You will on the other hand not see new records being added.

    4. Dynamic (adOpenDynamic)
    A bit like a keyset cursor but every time you request data from the cursor, the cursor will reissue the query and populate it with the keys once again. This means that it not only can see changes made to data that satisfied your query but also see new data being added since last time you requested data from the cursor. Here rs.RecordCount can't be trusted since you might end up with a different number of rows each time you go trough the cursor.

    This cursor type is the one putting most stress on your SQL server. Any developer using this cursor type for no good reason should get a kick in the ****.

    While working with any of the server side cursors above you have a constant connection open to the SQL Server. Something like this: ADO <-> OLEDB <-> SERVERSIDE Cursor <-> SQL Server

    If you one the other hand specify that you want to use a client side cursor all data that satisfy your query will first be fetched trough a fire-hose cursor (the quickest way for SQL Server) and then the data will end up in ADO's own cursor engine. Like this: ADO <-> CLIENTSIDE Cursor <-> OLEDB <-> SQL Server

    If it's a web server making the query, resources (memory etc.) on the web server will be used to handle the cursor and not the SQL server. In a web environment with say five load balanced web servers querying a single SQL server, this can be very effective way to minimize stress on the SQL server. Server side cursors are more common with Visual Basic applications that have a constant connection to the SQL Server and client side cursors are more common in web applications (since the operations there are often "fetch and forget"). A web browser can't have a constant connection to the sql server between requests so there is often no need to use server side cursors here unless you fetch a lot of data or are creating a flight booking system or something..

    Client side cursor (Ado cursor engine)
    A client side cursor can only be static. This is the only type that the ADO cursor engine supports. And the functions and methods you can use on a client side cursor are practically the same as on a server side Static cursor (you can do rs.RecordCount etc). Meaning that if you specify that you want to open a client side cursor, it will not matter what cursor type you try to set, because it will always be forced to static.

    What you often do with client side cursors is to disconnect them immediately so you can return the existing SQL connection to the connection pool. If you wanted you could reattach the client side cursor to another connection later and issue for example rs.UpdateBatch.

    ---------

Share This Page