Is there a way to have PB execute Embedded SQL or Datawindow queries in a parametrized form? (Apart from rebuilding the entire application to use Stored Procedures)
We have a (embedded or DW) SQL query like this:
SELECT col1, col2
WHERE col3 = :para_1
AND col4 = :para_2
At runtime, PB will parse this text, substitute :para_1 and :para_2 with the values of those parameters and send the entire String to the DB.
The problem with this approach is, that (at least for MS SQL Server), unless Forced Parametrisation is enabled, this results in a new query for every combination of parameters which all have to be analyzed and optimized by the database.
If there was an option to have PB send the statement using sp_executesql, the DBMS would have to compile far fewer different statements.
N'SELECT col1, col2
WHERE col3 = @para_1
AND col4 = @para_2',
N' @para_1 int, @para_2 int',
Obviously, there could be other problems with parameter sniffing with this solution, but there's also many scenarios where this could lead to a massive performance increase.
In our example, we have many queries where query compilation takes more time than the actual query execution, let alone the massive amount of memory wasted in the query cache.