Performance Considerations: Using Command Objects Wisely
A common performance problem with Command objects is instantiating (creating), executing, and destroying them in a single Command_Click event, a middle-tier component, or on a Web page. This is a problem, because in these cases, the object setup expense is paid each time the Command is executed instead of once. Because you can't expect to scale an application or component that leaves Command objects lying around waiting for potential clients, we have to construct them "just in time." While there are certainly cases in which you must construct a Command object, you don't want to expend the resources to instantiate and prepare the object unless it is necessary. Of course, in the middle tier (Microsoft Transaction Server or ASP), you may have no choice. In this case, you really want to think out your query strategy to determine whether it's really necessary to create/use/destroy a Command object each time the method or ASP page is referenced—especially when other cheaper strategies are available. While Command objects make it easier to manage procedure parameters, the extra overhead of creating them might not pay off in better performance.
I used to think that the fundamental purpose of the Command object was to help ADO create a temporary stored procedure to run your query more quickly. However, I have seen ADO change its approach to the creation and execution of temporary stored procedures to leverage SQL Server and other providers' capabilities to deal more efficiently with repeated ad hoc query invocations.
The Command object's Execute method also constructs the Recordset object for you. That is, you can't specify the LockType, CursorType, CacheSize, MaxRecords, or other interesting (but performance robbing) Recordset properties ahead of time—you have to take the defaults. The default Recordset is a read-only, forward-only, CacheSize=1 "firehose" (or cursorless) result set that is very efficient, so this behavior works for a high-performance application. However, if you code the Options argument to the Execute, you can tell ADO that the query does not return rows at all (as shown next), which helps improve performance.
Set rs = cmd.Execute(Options:=adExecuteNoRecords)
Any information that you can give ADO to help it know how to process the "stuff" in the CommandText property helps performance. As with the Recordset object's Open method options, which I discuss in Chapter 6, you really must tell ADO whether or not this is the name of a stored procedure, a table, the name of a file, or just random SQL. No, adCmdTableDirect is not a valid choice for most providers. The CommandType default is adCmdUnknown, which means ADO will have to guess, which takes time.
In many cases, you can improve performance by avoiding client-to-server round-trips. For example, if you combine a number of related operations together in a single query, you can save quite a bit of system and backend overhead. However, not all providers support multiple operations—SQL Server does, but Jet/Access does not. I use this technique to perform bulk inserts or to execute a series of commands bound in a transaction. You have to understand how to deal with the more complex result sets that ADO generates using this technique. But that's not hard.
Team-Fly |
EAN: 2147483647
Pages: 106