1. Markus Eckert
  2. PowerBuilder
  3. Monday, 5 March 2018 15: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.

Accepted Answer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 6 March 2018 12:14 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Markus,This looks like expected behaviour when DisableBind=1 for SQLCA (default setting for SNC driver).

My test: SELECT count(*) INTO userCount FROM SysUsers WHERE Name LIKE :namePattern;

SQLCA.DBParm SQL Syntax (SQLCA.SQLPreview)
"Database='...',TrustedConnection=1" ... WHERE Name like 'S%'
"Database='...',TrustedConnection=1,DisableBind=1" ... WHERE Name like 'S%'
"Database='...',TrustedConnection=1,DisableBind=0" ... WHERE Name like ?

 

HTH /Michael

Comment
  1. Markus Eckert
  2. Tuesday, 6 March 2018 14:34 PM UTC
Hi Michael



That's exactly what I was looking for. Thanks a lot!



Regards,

Markus

  1. Helpful
  1. Michael Kramer
  2. Tuesday, 6 March 2018 15:49 PM UTC
Thanks, Marcus.



Additional performance related properties for the SNC driver:





"Transaction" tab page



BindSPInput=1 -- or your DIsableBind=0 has no impact on stored procedure calls

Block

StaticBind=1 (turned on by default)

Lock





"System" tab page



SPCache

​PacketSize









Anyways, have a look at online help's Connecting to Your Database > Working with Native Database Interfaces > Using Microsoft SQL Server and Connecting to Your Database > Using Embedded SQL > Using Embedded SQL with Microsoft SQL Server. There may be a few goodies relevant to your setup. EX: How to handle spatial data types or the HierarchyID data type.



Enjoy, /Michael

  1. Helpful
  1. Alfredo Aldama
  2. Friday, 9 March 2018 20:02 PM UTC
​You can improve the performance / response time by making some changes:



Recalculate statistics.

Create appropriate indexes in the table.

Create a stored procedure with internal variables, so the optimizer will take an average and not the values of the first execution.

Put the query in cache of the database.



By performing these actions the parse of the query will be imperceptible.

  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 5 March 2018 16:01 PM UTC
  2. PowerBuilder
  3. # 1

Hi Markus;

   That is what the DataWindow does automatically when you use Retrieval Arguments in the DW's SQL definition.

Regards .. .Chris

Comment
  1. Markus Eckert
  2. Tuesday, 6 March 2018 07:20 AM UTC
Hi Chris



I think either we misunderstand each other, or there's different behavior on different configurations.



With my Configuration (SQL Native Client 11 on SQL Server 2012), the Datawindow substitutes the Values for the Retrieval Argument placeholders in the DW's SQL definition. Meaning the query the Datawindow sends to the Database has no more parameters - so one Datawindow might send millions of different (but very similar) queries like "SELECT c.family_name, c.first_name, p.street, p.postalcode, p.town FROM Customers c JOIN PostalAddress p on p.id = c.address WHERE c.id = 1" or "... WHERE c.id = 2" or "... WHERE c.id = 248414" depending on the value of the parameter.



For all but the simplest of queries, this will lead to each query being seen as different and a new execution plan will have to be generated, which is a waste of database CPU time.



Regards,

Markus

  1. Helpful
  1. Markus Eckert
  2. Tuesday, 6 March 2018 14:35 PM UTC
Seems like the DisableBind Setting in the Transaction is what I was looking for.

  1. Helpful
  1. Chris Pollach @Appeon
  2. Wednesday, 7 March 2018 14:51 PM UTC
Yes, the DisableBind =1 setting will prevent an extra DESCRIBE operation from happening and definitely improve your DB execution speed. I never think about that with SS's native driver as that is its default setting.



 

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.