Database-Specific Cursor Considerations

[Previous] [Next]

Many client/server database books purport that "cursors are evil," but you've probably realized by now that this isn't your typical client/server database book. Cursors are not inherently bad; in some cases, they're actually appropriate. Part of the problem is that they're widely misunderstood. "Cursors are evil" is an easy message to convey—and it's generally true of server-side cursors—but it's a little simplistic. I'd rather cover the pros and cons of cursors in different scenarios and let you make your own choices.

The terms server-side and client-side can be misleading when applied to cursors and Recordset objects. The terms are simple, but the concepts are not. Rather than asking, "Where is the data in a server-side cursor stored?" perhaps a better way to phrase the question is, "Am I storing the results of my query in the ADO Cursor Engine or not?" It's simple, direct, and to the point.

We recently touched on the subject of client-side Recordsets and how the ADO Cursor Engine maintains your data. Explaining where the cursor in a server-side cursor is located isn't quite as simple. It depends on what type of OLE DB provider or ODBC driver you're using. Let's cover the three most common databases used with ADO—SQL Server, Microsoft Access, and Oracle—and look at what it really means to use a server-side cursor when you work with a Recordset in these databases. I'll also explain where the data in the server-side cursor is located in each one.

Server-Side Cursors with SQL Server

The SQL Server database system supports cursors. When you use a server-side cursor with the SQL Server OLE DB provider or ODBC driver, you're using a cursor that's maintained by SQL Server itself. This is a very powerful feature. There are times when you want to process the results of a query in such a way that you absolutely can't live without an updatable cursor. But most of the time you can, and should, live without using SQL Server's cursors. Here are a few reasons why.

Imagine that SQL Server is an extremely important employee of yours—perhaps the person you ask to analyze large amounts of business data to generate reports for major presentations. Let's call her Syd. You want Syd to work within her specialty. You don't want her to spend her time organizing past reports in file cabinets and managing the library where they're stored. That wouldn't be wise use of Syd's time. Let someone else take care of the grunt work.

Similarly, you don't want SQL Server to get bogged down storing the results of your queries unless absolutely necessary. Think about a simple query in your application that returns a couple hundred records. Perhaps your application needs to continually work with the results of five such queries. A thousand employees, perhaps up to five hundred at any one time, will use your application. This puts a heavy usage load on the database. It makes much more sense for your database system to do what it's designed to do—store your data and process queries—rather than expend its resources storing the results of your queries.

Don't forget that if you're using server-side cursors in your application, you need a live connection to the database as you navigate through your Recordset. Are you sure you want to incur network round-trips each time your code or your user decides to scroll through the recordset? Another consideration is that this type of application doesn't scale well. As you add more users, you add more connections to your database at a linear rate, and the same instance of SQL Server needs to maintain the cursors for each user. Plus, you'll be unable to move to a multitiered application using technologies such as Component Services or Microsoft Transaction Server (MTS). Most multitiered applications require that the middle-tier objects maintain no state. In other words, your clients should not have live connections to your database.

If you're still not convinced that making widespread use of server-side cursors in your ADO and SQL Server application is a bad idea, check with your database administrator. Just prepare to be convincing when you say, "I was just kidding—I wanted to see how you'd react," if you want your database administrator to trust your judgment ever again. Experience has proven that basing your application on server-side cursors with SQL Server causes poor performance and scaling, and may also lead to hair loss and unemployment.

Server-Side Cursors with Access

Both the Access OLE DB provider and the Access ODBC driver support server-side cursors. They're implemented differently from SQL Server's cursors, however. The term "server" is a little misleading when it comes to server-side cursors in Access.

With SQL Server, the database server maintains the cursor while the OLE DB provider and ODBC driver simply access that cursor. With Access, the OLE DB provider and ODBC driver have to do a great deal more work: they have to examine the database file and generate the results of the query. While the database file might be central to your application, each client is directly accessing the database file by means of the OLE DB provider or ODBC driver.

This architecture scales better than SQL Server's cursors. Now don't overreact—I'm not saying you should use an Access database rather than SQL Server. Access databases aren't designed for high-stress, multiuser applications. The point is that as you add more users with this type of architecture, the Access database file and lock file are much busier but you're not tying up the server with the results of cursors. The cursors are maintained in the same process as ADO.

During the ADO 2.0 beta testing, one user noted that his large queries returned much faster with Data Access Objects (DAO) than with ADO, but only with Access databases. It turns out the difference in performance was actually between server-side and client-side cursors. By default, DAO stores the results of queries in what's essentially a server-side cursor. Opening a server-side ADO Recordset took the same amount of time as opening a DAO Recordset. So, why does it take longer to open a client-side ADO Recordset?

When using a client-side cursor with an Access database, you're essentially moving data from the Access cursor engine to the ADO Cursor Engine. (As mentioned earlier, ADO processes the results of the query from a firehose cursor and stores that data in its own Cursor Engine.) This process is more complicated than simply copying a block of memory. The Jet 4.0 OLE DB Provider or ODBC driver processes the query, generates the results, and then has to pass all of this data to the ADO Cursor Engine one record at a time. This process accounts for the difference in performance.

So should you simply not use client-side cursors with Access databases? Not so fast. Some of ADO's functionality (for instance, the ability to persist a Recordset to a file, the batch updating feature, the ability to sort your Recordset, and hierarchies) is available only with client-side Recordsets. Plus, if you're using a keyset or dynamic cursor to store the results of your Access queries, the OLE DB provider or ODBC driver still needs to examine the database every time you step outside the number of cached records specified by the Recordset's CacheSize property.

While you might see a difference in performance between client-side and server-side cursors when you open the Recordset, as the beta tester in our example did, that's not a comprehensive test. When you consider what you do with the cursor during its lifetime—scrolling back and forth, updating, and so forth—you're normally going to see similar performance between the two cursors. I myself generally use client-side cursors with Access databases.

Server-Side Cursors with Oracle

Recently, Oracle added support for cursors outside of a stored procedure. However, to the best of my knowledge, there are no OLE DB providers or ODBC drivers that take advantage of this new feature of Oracle databases. Yet the Microsoft ODBC driver for Oracle has always supported cursors. You can open up a Recordset object with a CursorLocation property value of adUseServer and get a keyset cursor with the Microsoft ODBC driver for Oracle. How is this possible?

The Microsoft ODBC driver for Oracle actually implements the cursor in a way that is somewhat similar to how the Access OLE DB provider and ODBC driver implement cursors. For a keyset cursor, the Microsoft ODBC driver for Oracle requests keyset information for the records that satisfy the query's criteria and then retrieves those records whenever the Recordset requests more data.

This architecture places less stress on the database server than does SQL Server's implementation of cursors. The data in the cursor is cached in the Microsoft ODBC driver for Oracle, and the Oracle server does what it does best—it maintains data and processes queries.

As of Microsoft Data Access Components (MDAC) 2.1, the Microsoft OLE DB Provider For Oracle does not implement cursors. You can only retrieve data out of a firehose cursor. If you need a Recordset that supports scrolling and/or updating, you can still use the Microsoft OLE DB Provider For Oracle if you use the ADO Cursor Engine to provide that functionality.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net