Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. Should I always use a Command object?

A. In short, no. The Command object can be extremely handy, but it's primarily designed for two-tiered client-server applications, which, these days, aren't as fashionable as multitiered applications. If you're building a query that will run in a Component Services (formerly known as Microsoft Transaction Server) component or in Active Server Pages (ASP), there's really no reason to use a Command object.

Q. Should I always use Parameter objects in my query?

A. Again, in short, no. If you're executing your query only once, there's usually little reason to use a parameterized query.

If you know ahead of time that you're going to retrieve information about a particular customer, but you're accepting input from the user to decide which customer, you might be tempted to use this information in a Parameter object. While that's a perfectly acceptable reason for using a Parameter object, bear in mind that you could also simply build your query string and include that information prior to submitting the query.

Many developers will use parameterized queries because they're receiving input from the user and don't want to worry about delimiting the string or date. You'll understand why if you've ever had to build a query like the following:

 SELECT * FROM Authors WHERE Au_LName = 'O''Leary' 

Q. Why should I use Parameters.Append instead of Parameters.Refresh?

A. Unless you're building an ad hoc query tool, you should avoid calling Parameters.Refresh in your application at all costs.

When you call Parameters.Refresh, you're asking the OLE DB provider to supply parameter information that you should already have available to you. If you're not dealing with an ad hoc query tool, you should know what the data type and direction for your parameters are.

There's a natural tendency to believe that writing less code means your application will run faster. While that's true in general, your application will often run faster if you populate the parameters collection yourself. Why? Asking the OLE DB provider to supply information about the parameters for your query incurs network round-trips that could easily be avoided by writing a little more code.

Also, for large database systems, it's actually the database system that has to generate the information that's handed back to ADO. Collecting information about the parameters is often a costly query that examines system tables. Many developers using Visual Basic, Remote Data Objects (RDO), and Oracle complained about poor performance on parameterized queries because the more tables and stored procedures they added to their database, the longer it took to retrieve the parameter information. This is one of the reasons the ADO developers added the Append method to the Parameters collection.

Finally, many OLE DB providers are unable to supply all the information you need for your Parameter objects anyway. SQL Server has no way of indicating whether a parameter is input/output or output-only. Even if you call Parameters.Refresh, you'll need to modify the Direction property of your Parameter object if you want it to be an output-only parameter. The Microsoft Jet 4.0 OLE DB Provider has difficulty determining the length of character-based parameters, and you might need to set the Size property yourself even after calling Parameters.Refresh. (If this information seems overwhelming, revisit the previous two questions.)

Q. What's the deal with Microsoft Access QueryDefs with ADO?

A. The answer to this question could fill an entire book, but I'll give a brief answer here. There is a difference between how the Jet 4.0 OLE DB Provider and the Jet ODBC driver handle QueryDefs (query definitions). Part of the reason for this difference in behavior is that a Jet QueryDef is somewhere between a view and a stored procedure. The other part of the reason is that the OLE DB provider and the ODBC driver were developed by entirely different teams, and the provider and driver libraries each directly access the Access database.

To make a long story short, the Jet 4.0 OLE DB Provider treats a QueryDef as a view, and the Jet ODBC driver treats a QueryDef as a stored procedure. Say that you built a parameterized QueryDef named GetAnOrder for the query SELECT * FROM Orders WHERE OrderID = ?. The Jet 4.0 OLE DB Provider treats the QueryDef like a table. You would use the following code to run the query with the Jet 4.0 OLE DB Provider:

 With qryGetOrder .CommandText = "SELECT * FROM GetAnOrder" .CommandType = adCmdText .Parameters.Append .CreateParameter("pOrderID", adInteger, _ adParamInput) Set .ActiveConnection = cnNorthwind End With 

The ODBC driver for Access treats that same QueryDef like a stored procedure. To run the QueryDef using the ODBC driver for Access, you would use the following code:

 With qryGetOrder .CommandText = "{CALL GetAnOrder (?)}" .CommandType = adCmdText .Parameters.Append .CreateParameter("pOrderID", adInteger, _ adParamInput) Set .ActiveConnection = cnNorthwind End With 

You're also likely to see inconsistencies if you compare schema information returned by the OLE DB provider and the ODBC driver, or if you look at QueryDefs in the Visual Basic DataView window.