-
Markus Eckert
- PowerBuilder
- Monday, 5 March 2018 03:20 PM UTC
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)
Scenario
We have a (embedded or DW) SQL query like this:
SELECT col1, col2
FROM tbl
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.
Suggestion
If there was an option to have PB send the statement using sp_executesql, the DBMS would have to compile far fewer different statements.
EXECUTE sp_executesql
N'SELECT col1, col2
FROM tbl
WHERE col3 = @para_1
AND col4 = @para_2',
N' @para_1 int, @para_2 int',
@para_1 = ,
@para_2 =
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.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.