SQL Server Magazine

Note that the columns the SELECT statement returns match those you specified in the enumeration. Then, when it was time to reference the Recordset Fields collection, you would execute the following code:

StrMyName = Rs(enuCatsQuery.CatName)

This way, the code is "human readable" and still binds at design time, so it's considerably faster.

But you have another way to avoid late binding. A long discussion on the vbdata-l@peach.ease.lsoft.com list services resulted in an approach I call prebinding, which combines two techniques. This approach won't help in cases in which you reference the Field object only once, but in client/server applications, prebinding works nicely. With this technique, you create individually named Field objects and set these objects to the Recordset object's Fields collection items. To code it, you first set up a named Field object for each Field you'll manage:

Dim fldName as ADODB.Field
Dim fldType as ADODB.Field
Dim fldSize as ADODB.Field

You'll incur some overhead in creating individual Field objects, but you have to weigh this one-time penalty against its benefit in dramatically improved performance.

After you've opened the Recordset, you set the named Field objects just once to refer to the desired columns in the SELECT query:

If fldName is Nothing then
Set fldName = RS!CatName
Set fldType = RS!CatType
Set fldSize = RS!CatSize
End if

You can use quoted string referencing here, or even the bang operator. Because you're using them only once, they won't make much difference in performance. Then, when it's time to reference the Field objects (post query), simply use the prebound variables:

strName = fldName
strType = fldType
strSize = fldSize

This prebinding approach can yield even better performance than you get with the ordinal reference (RS(0)) approach.

Client/Server, Middle-Tier, and ASP Strategies
When you start implementing code in other than client/server situations, you have another set of considerations to include in your search for performance. Some of these have nothing to do with ADO—they're related to COM. A recent Microsoft white paper states that it's faster to execute an ADO operation (Connect, query, process) in a Windows 2000 ASP page than to call a COM component to execute the same code. This finding is unsurprising; when you call an external COM component from VB (a piece of code outside your process space), the work going on behind the scenes to access the COM component and pass control to it is amazingly complex and slow. You don't have to avoid calling COM components to run your ADO code, but you can't simply encapsulate individual ADO operations into lots of little COM components and call them one at a time when you need them. Instead, try building an entire logic tree in the COM component so that in a single invocation, the COM component can perform most (if not all) of the ASP functionality in one round-trip—or as few trips between your ASP code and COM as possible. I think you'll find that running ADO (or any) code in binary (e.g., the COM component) is faster than running (ASP-) interpreted code. So, you need to find ways to avoid the expense of getting to and from the COM component.

When you can't leave a Command object or prebound Field object around to use and reuse as needed, consider techniques that avoid unnecessary object creation. In this case, calling stored procedures as Connection methods makes even more sense. Using early binding to reference Field objects can also help performance. And remember to clean up after yourself: Close the Recordset and Connection objects, and set them to Nothing when you're finished.

For best code and coder performance, remember these basics: Leverage the connection pool and asynchronous connections, reduce the number of round-trips your ADO code makes, choose an early COM-binding technique, avoid expensive and unnecessary ADO objects such as the Recordset and Command objects, and use the Return Status or Output parameters instead of rowsets. Always try to write intelligent queries and capitalize on stored procedures whenever possible. And tell ADO what you want it to do in detail to prevent it from having to guess—specify an explicit ADO CommandType, and use options such as adExecuteNoRecords.

I suggest you take this and all other programming advice with a grain of salt. The work we do, the code we write, and the systems we create are very complex, and many constantly changing factors influence them. With this in mind, if you see a technique here that looks interesting, try it out. If it works, implement it, and test it again. If it still works, great. If it doesn't, let me know what you tried and what did or didn't work.

End of Article

Prev. page     1 2 [3]     next page -->