Direct Execution

Direct execution is the most basic way to execute a statement. An application builds a character string containing a Microsoft® SQL Server™ statement and submits it for execution using the SQLExecDirect function. When the statement reaches the server, SQL Server compiles it into an execution plan and then immediately runs the execution plan.

Direct execution is commonly used by applications that build and execute statements at run time and is the most efficient method for statements that will be executed a single time. Its drawback with many databases is that the SQL statement must be parsed and compiled each time it is executed, which adds overhead if the statement is executed multiple times.

When connected to versions of SQL Server earlier than 7.0, direct execution should be used:

• When a statement is likely to be executed fewer than four times.
  
  
• To call stored procedures.

SQL Server 2000 significantly improves the performance of direct execution of commonly executed statements in multiuser environments. For SQL Server 7.0 applications, using SQLExecDirect with parameter markers for commonly executed SQL statements can approach the efficiency of prepared execution.

When connected to an instance of SQL Server 2000, the SQL Server ODBC driver uses sp_executesql to transmit the SQL statement or batch specified on SQLExecDirect. SQL Server 2000 has logic to quickly determine if an SQL statement or batch executed with sp_executesql matches the statement or batch that generated an execution plan that already exists in memory. If a match is made, SQL Server simply reuses the existing plan rather than compile a new plan. This means that commonly executed SQL statements executed with SQLExecDirect in a system with many users will benefit from many of the plan-reuse benefits that were only available to stored procedures in earlier versions of SQL Server.

This benefit of reusing execution plans only works when several users are executing the same SQL statement or batch. Follow these coding conventions to increase the probability that the SQL statements executed by different clients are similar enough to be able to reuse execution plans:

• Do not include data constants in the SQL statements; instead use parameter markers bound to program variables. For more information, see Using Statement Parameters.
  
  
• Use fully qualified object names. Execution plans are not reused if object names are not qualified.
  
  
• Have application connections as possible use a common set of connection and statement options. Execution plans generated for a connection with one set of options (such as ANSI_NULLS) are not reused for a connection having another set of options. The SQL Server ODBC driver and the OLE DB Provider for SQL Server both have the same default settings for these options.

If all statements executed with SQLExecDirect are coded using these conventions, SQL Server can reuse execution plans when the opportunity arises.

To use a statement

ODBC

To execute statements directly

ODBC

To prepare and execute statements

ODBC

See Also

Building Statements at Run Time

sp_executesql