Inner Workings of the Command Object | Ado Examples and Best Practices

Team-Fly

The Command object's biggest benefit is performance. Not only does it make your queries run efficiently, but it also makes you work more efficiently. That is, using the Command object can reduce the length of time it takes to code, debug, test, and deploy complex parameter-based queries—including queries executing stored procedures. For example, when accessing SQL Server 7.0, the ODBC and OLE DB providers have been tuned to access the new sp_executesql system stored procedure. There's quite a write-up on this in SQL Server Books Online,^[1] and I have summarized it here.

Basically, the Command object is driven from the CommandType argument that instructs ADO how to transmit your query to the data provider. Suppose you have a parameter-based ad hoc query^[2] that you wish to execute.

 Select author, au_id, year_born from authors where year_born = ?

You placed a parameter marker (?) where you want ADO to insert the parameter, so you're ready to have ADO execute this query. For this query, ADO manufactures an SQL statement that looks like this:

 sp_executesql N'Select author, au_id, year_born from authors where year_born = @P1', N'@P1 int', 1947^[3]

The sp_executesql system stored procedure was introduced in Microsoft SQL Server version 7.0. The MDAC developers want us to use it instead of the EXECUTE statement to execute a query string. The support for parameter substitution makes sp_executesql more versatile than EXECUTE; it also makes sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.

ADO and the SQL Server data provider have also implemented another (proprietary) interface to handle server-side cursors. These are implemented as system-level sp_cursor stored procedures that open, fetch, close, and perform various other operations on your data. When using the default server-side CursorLocation setting, you'll find that many queries are executed using these stored procedures.

Tip

When working with SQL Server, it's essential that you turn on and leave on the Profiler (or SQL Server 6.5 Trace) while you are tuning your code. Leaving it on shows you exactly what's being sent to SQL Server in as much detail as you can handle (and sometimes more). Turn it off when you're happy with your code's performance.

Substituting Parameter Values

The sp_executesql procedure and the sp_cursor stored procedures support the substitution of parameter values for any parameters specified in the Transact-SQL string—unlike the (obsolete) TSQL EXECUTE statement. Transact-SQL strings generated by sp_executesql are more similar to the original SQL query than those generated by the EXECUTE statement, which gives the SQL Server query optimizer a better chance to match the Transact-SQL statements from sp_executesql with execution plans from the previously executed statements. This dramatically reduces the need to compile a new execution plan with each execution of your parameter query. That's good.

With the TSQL EXECUTE statement, all parameter values must be converted to character or Unicode and made part of the Transact-SQL string, as shown in this code example:

 DECLARE @IntVariable INT DECLARE @SQLString NVARCHAR(500) /* Build and execute a string with one parameter value. */ SET @IntVariable = 35 SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' + CAST(@IntVariable AS NVARCHAR(10)) EXEC(@SQLString) /* Build and execute a string with a second parameter value. */ SET @IntVariable = 201 SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' + CAST(@IntVariable AS NVARCHAR(10)) EXEC(@SQLString)

If the statement is executed repeatedly, a completely new Transact-SQL string must be built for each execution, even when the only differences are in the values supplied for the parameters. This generates extra overhead in several ways:

The ability of the SQL Server query optimizer to match the new Transact-SQL string with an existing execution plan is hampered by the constantly changing parameter values in the text of the string, especially in complex Transact-SQL statements.
The entire string must be rebuilt for each execution.
Parameter values (other than character or Unicode values) must be cast to a character or Unicode format for each execution.

In contrast, sp_executesql supports the setting of parameter values separately from the Transact-SQL string.

 DECLARE @IntVariable INT DECLARE @SQLString NVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) /* Build the SQL string once. */ SET @SQLString =N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level' /* Specify the parameter format once. */ SET @ParmDefinition = N'@level tinyint' /* Execute the string with the first parameter value. */ SET @IntVariable = 35 EXECUTE sp_executesql @SQLString, @ParmDefinition,@level = @IntVariable /* Execute the same string with the second parameter value. */ SET @IntVariable = 32 EXECUTE sp_executesql @SQLString, @ParmDefinition,@level = @IntVariable

This sp_executesql example accomplishes the same task as the TSQL EXECUTE example shown earlier, but with these additional benefits:

Because the actual text of the Transact-SQL statement does not change between executions, the query optimizer should match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.
The Transact-SQL string is built only once.
The integer parameter is specified in its native format. Conversion to Unicode is not required.

Note

For SQL Server to reuse the execution plan, object names in the statement string must be fully qualified.

Reusing Execution Plans

In earlier versions of SQL Server, the only way to reuse execution plans was to define the Transact-SQL statements as a stored procedure and have an application execute the stored procedure. The sp_executesql procedure can be used instead of stored procedures when executing a Transact-SQL statement a number of times—especially when the only variation is in the parameter values supplied to the Transact-SQL statement. Because the Transact-SQL statements themselves remain constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution. Existing ODBC applications ported to SQL Server 7.0 automatically acquire the performance gains without having to be rewritten. For more information, see "Using Statement Parameters" in Books Online.

The Microsoft OLE DB Provider for SQL Server also uses sp_executesql to implement the direct execution of statements with bound parameters. Applications using OLE DB or ADO gain the advantages provided by sp_executesql without having to be rewritten.

The ADO Command object does not use sp_executesql to execute ad hoc queries that don't have parameters or that simply reference parameter-less stored procedures.

^[1]Books Online is the copious set of help topics and examples that ships with SQL Server in lieu of printed documentation. Most (if not all) of it is also available through MSDN via subscription or online.

^[2]An ad hoc query is simply a hard-coded SQL query or action. Using these queries is not a good idea for performance, maintainability and security reasons. If you can, use a stored procedure instead, but many developers depend on them, at least initially.

^[3]Note the "N" prefix on the generated code (N'Select au…). This uppercase N indicates that the following quoted string is in Unicode format. Unicode data is stored using 2 bytes per character, as opposed to 1 byte per character for character data. For more information, see "Constants" in Books Online.

Team-Fly