More information

EnableSQLStatementParams

About SQL Parameterization

When the application (netFORUM) sends a query to the SQL Server, the first thing SQL Server must do is develop an execution plan of how to most efficiently return the requested data. The execution plan makes data-retrieval very efficient, but generating this plan takes time. SQL Parameterization is a technique of coding SQL statements, such as queries or updates, so that the database reuses its execution plan for queries that are similar.

The SQL Server can see two queries that are similar as two completely different queries and then generate a separate execution plan for each. Many of the queries generated by netFORUM (or any application) are identical except for a few of the filtering values in the WHERE clause.

Example:
SELECT * FROM md_dynamic_form WHERE dyn_key=’ C5E88CB1-F386-46D1-83E8-0DDB7C000DD6’
And
SELECT * FROM md_dynamic_form WHERE dyn_key=’ 4A949D97-A1DF-42E3-9BF4-29A7A2E4E3D7’

SQL Parameterization replaces the previous two statements with the following one:

Example: SELECT * FROM md_dynamic_form WHERE dyn_key=@KeyValue
Specify @KeyValue when you run the query.

In this case, the SQL Server sees it as the same query every time and re-uses the same execution plan, even if the application executes the statement with different values. This way, netFORUM can execute the same or similar database statements repeatedly with high efficiency.

Why Does netFORUM Use SQL Parameterization?

netFORUM uses SQL Parameterization to improve performance. With parameterization, the SQL server needs only to compile and optimize a statement once, while the application can supply definitions for the parameters and execute the statement multiple times.

Troubleshooting

To troubleshoot SQL Parameterization, use the following general steps:

  1. Enable SQL Parameterization across all queries by setting the EnableSQLStatementParams system option to 65536.
  2. If you encounter a problem with a parameterized query, turn on SQL Trace Comments.
  3. Look at the error log. Look for the Area Name connected to the error.
  4. Go to the EnableSQLStatementParams help topic and find the same Area Name.
  5. Turn off parameterization for that Area.
  6. Does netFORUM perform as expected?
    If no, repeat steps three through five, choosing a new Area.
    If yes, leave parameterization off for that Area and contact Abila Support.