Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. Do you really need every record in your table?

A. So many customers call to complain that the applications they built suddenly started to perform poorly once they moved into production. The most common cause? Queries that retrieve all records and all fields from a table. Of course the query's performance will degrade when you move from a small test database to a full production database. It will continue to degrade as you add more data to the table.

Q. Do you really need every field in your table?

A. Even if you do need to retrieve every record, do you really need to retrieve every field? Too many programmers use SELECT * FROM MyTable simply out of laziness. Others use this simple query because it's the only way they ever learned to query tables. Books and documentation tend to show simple, concise code primarily because a single line of code that takes up multiple lines on a page doesn't look nice, and too much detail can often make an example confusing. As a result, many queries that you'll see in online documentation or in books such as this will use SELECT * FROM MyTable. You'll see a number of queries of this type in this book, but now you know why.

Even if you do need every column from your table, you would probably see better performance if you avoided the wildcard character (asterisk) and listed each field instead—unless the query string is so long that the OLE DB provider or ODBC driver chokes on it.

Q.How do I know when my asynchronous query has completed?

A. Unfortunately, the answer isn't quite as simple as the question. If you're simply using the adAsyncExecute constant in your Recordset.Open call, the ExecuteComplete event will fire when your query completes. If you're using the ADO Cursor Engine's asynchronous fetching feature, things get a little more complex.

In this chapter, we covered the FetchProgress and FetchComplete events for the Recordset object. These events fire when you fetch your data asynchronously. Usually.

Actually, these events fire (as of ADO 2.5) only if your query returns at least the number of records specified in the Initial Fetch Size dynamic property in the Recordset object's Properties collection. What to do if your query returns fewer records? Well, here's some code for the ExecuteComplete and FetchComplete event handlers that works pretty well for me.

The initial test makes sure that the pRecordset object exists. (If you used adExecuteNoRecords as one of the parameters when you created your Recordset object, this pRecordset object is set to Nothing.) The Recordset object's State property is set to adStateOpen or adStateClosed if the query has completed. If the State property returns adStateOpen + adStateFetching, there's still more data for ADO to fetch. Once ADO has fetched the remaining data, the FetchComplete event on the Recordset object will fire.

 'The ExecuteComplete event handler Private Sub cn_ExecuteComplete(...) If Not pRecordset Is Nothing Then If pRecordset.State = adStateOpen Or _ pRecordset.State = adStateClosed Then Debug.Print "cn_ExecuteComplete -- Query has completed" Else pRecordset.State = adStateOpen + adStateFetching Debug.Print _ "cn_ExecuteComplete -- Wait for rs_FetchComplete" End If Else Debug.Print "cn_ExecuteComplete -- Query has completed" End If If Not pError Is Nothing Then Debug.Print pError.Description End If End Sub 'The FetchComplete event handler Private Sub rs_FetchComplete(...) Debug.Print "rs_FetchComplete -- Query has completed" End Sub 

Actually, this code won't tell you exactly when you've fetched all your data if you're running a hierarchical query, but we'll talk more about hierarchical queries in Chapter 14.