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 may 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.
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.
To troubleshoot SQL Parameterization, use the following general steps: