Performance Issues with Disconnected Recordsets
This article will establish performance differences between disconnected and connected recordsets when using them with Microsoft Access and Microsoft SQL Server, and uncover potential pitfalls when designing your system for multi-database access.Author: Jens G. Balchen
IntroductionIn a recent project I was working on, the client wanted a database system where data would be maintained locally on his machine, and then transferred to a web server in batches. For the client application, I used Microsoft Access and a standard set of ADO routines I have developed. These routines use disconnected recordsets, since their primary use have been in transactional, server-side, stateless components. I saw no potential dangers in using disconnected recordsets in a client application.
As it turns out, there is a bug in the MFC data access library. You cannot have more than 40 fields in a recordset and at the same time perform batch updates on that recordset. Since disconnected recordsets use batch updates, this bug caused my client application to crash whenever it tried to update a record. The solution was to use connected recordsets instead, which do not require batch updates. A curious side effect to this, to our client's immense pleasure, was a significant decrease in database access times.
I decided to perform some tests on connected vs. disconnected recordsets using both Microsoft Access and Microsoft SQL Server as database servers. The test code, test results and my conclusion make out this article.
Writing the test codeThe test code was kept very simple. A form containing two command buttons formed the framework, and the code beneath each button had only minor differences. The basic code looks like this:
Private Sub cmdDisconnected_Click() Dim Connection As ADODB.Connection Dim Rs As ADODB.Recordset Dim a As Variant Dim Opening As SETimer Dim Traversing As SETimer Set Opening = New SETimer Set Traversing = New SETimer Set Connection = New ADODB.Connection Set Rs = New ADODB.Recordset Opening.StartTimer ' Open a database connection using a client library cursor. With Connection .ConnectionString = "(whichever connection string you like)" .CursorLocation = adUseClient .Open End With ' Open a recordset with lots of data. With Rs .CursorType = adOpenStatic .LockType = adLockBatchOptimistic .Open "SELECT * FROM Files", Connection End With ' Disconnect recordset. Set Rs.ActiveConnection = Nothing Opening.StopTimer ' Close connection. Connection.Close Set Connection = Nothing Traversing.StartTimer ' Traverse entire recordset. Do While Not Rs.EOF ' Get a text (BLOB) field to increase data load. a = Rs("Description") Rs.MoveNext Loop Traversing.StopTimer ' Close recordset. Rs.Close Set Rs = Nothing txtTimes = txtTimes & Opening.TimeMS & vbTab & Traversing.TimeMS & vbCrLf End SubThe code for cmdConnected_Click looks the same, except for some important differences:
' Open a database connection using a server library cursor. With Connection .ConnectionString = "(whichever connection string you like)" .CursorLocation = adUseServer .Open End With ' Open a recordset with lots of data. With Rs .CursorType = adOpenStatic .LockType = adLockOptimistic .Open "SELECT * FROM Files", Connection End With ' The recordset isn't disconnected and the connection ' isn't closed before the traversing starts.As you can see, I performed two tests -- one recorded the time used to open and populate the recordset, and one recorded the time used to traverse the entire recordset, including some basic data access. The output into txtTimes was tab-separated to ease the migration to Microsoft Excel (to do some basic graph creation).
The test resultsSince both Microsoft Access and Microsoft SQL Server perform data caching, subsequent tests show little variation in data access times. However, a comparison between database servers and access methods show huge differences.
Microsoft SQL Server Access Times
These test results clearly show the advantage of disconnected recordsets with Microsoft SQL Server. In average, the basic open, traverse, and close is done 10 times faster than with connected recordsets.
Microsoft Access Access Times
As you can see, disconnected recordsets show a slight performance degrade with Microsoft Access, but the most important thing about these data is the significant increase in speed with connected recordsets. The access time has been reduced to 1 / 15 of the time used with Microsoft SQL Server, and is only 2 / 3 of the time used with disconnected recordsets.
ConclusionDisconnected recordsets provide a fast and convenient way to access data in stateless, transactional enviroments, and work very well with Microsoft SQL Server. This simple test does not take into account factors such as cross-thread marshalling that typically apply to larger systems, but nonetheless, disconnected recordsets prove the most efficient way to access SQL Server data. Connected recordsets seem to work best with Microsoft Access. The access times are significantly reduced, and I suspect the decrease will be even larger when the recordset contains more fields.
When you write applications meant to run with both database servers, give this topic some consideration. If you chose the wrong kind of data access, your application could suffer from slow access times.
Editor: Jens G. Balchen
Last update: 2020-02-17
Copyright 1995-2020 VBI