Direct Execution

Direct execution is the most basic way to execute a statement. An application builds a character string containing an SQL statement and submits it for execution using the SQLExecDirect function. When the statement reaches the server, Microsoft® 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 every time it is executed, which adds overhead if the statement is executed a number of times.

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

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

SQL Server 7.0 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 SQL Server 7.0, the SQL Server ODBC driver uses sp_executesql to transmit the SQL statement or batch specified on SQLExecDirect. SQL Server 7.0 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 all 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 whenever the opportunity arises.

To use a statement

To execute statements directly

To prepare and execute statements

See Also