1. Markus Eckert
  2. PowerBuilder
  3. Friday, 6 April 2018 12:39 PM UTC

Hi

After finding out about the DisableBind=0 setting in an earlier question (https://community.appeon.com/index.php/qna/q-a/parametrized-query-execution) I've been experimenting with it for a while. (using SNC11 on SQL Server 2012)

One particular thing I noticed was that Powerbuilder doesn't seem to recognize reused parameters, instead treating each instance of the parameter as a new distinct value.

 

To illustrate, here's a simple Query with 1 parameter that's used twice:

SELECT count(1) AS cnt
FROM sys.tables t
JOIN sys.triggers tr ON tr.parent_id = t.object_id
WHERE year(t.create_date) = :year
AND year(tr.create_date) = :year

Here's the query that's sent to the database:

exec sp_executesql N'SELECT count(1) AS cnt
FROM sys.tables t
JOIN sys.triggers tr ON tr.parent_id = t.object_id
WHERE year(t.create_date) = @P1
AND year(tr.create_date) = @P2',N'@P1 float,@P2 float',2017,2017

 

Now since more parameters make a query more complex to analyze (thus increasing the risk of a bad execution plan to be generated), I'm wondering if this is the expected behavior or if there's another setting I'm missing.

Regards,

Markus

mike S Accepted Answer Pending Moderation
  1. Friday, 6 April 2018 16:24 PM UTC
  2. PowerBuilder
  3. # 1

"Powerbuilder doesn't seem to recognize reused parameters"

It is the driver (MS SQL Server) that is doing all of that, not powerbuilder.

Comment
There are no comments made yet.
Alfredo Aldama Accepted Answer Pending Moderation
  1. Friday, 6 April 2018 17:34 PM UTC
  2. PowerBuilder
  3. # 2

And what do you think about using a stored procedure? In my experience, the delay of executing a query or SP is of milliseconds, but if the analysis of the query causes an inconvenience, an SP would solve this situation, you can also make an analysis of how your query is executed within the database using a "SHOW PLAN" or equivalent

Comment
  1. Markus Eckert
  2. Wednesday, 11 April 2018 14:48 PM UTC
Yes, SPs are what I would use in a new project, but this application already has thousands of existing DataWindows and Embedded SQL statements.



I've been converting some of the most often used ones to Stored Procedures already, but that's a very time intensive process, whereas switching to DisableBind=0 could've fixed the problem at the source.



But looking at the change in behavior for DW Inserts, the "no Unicode or Unicode all the time" nature of the "NChar Bind" switch and now this duplication of parameters, turning on Binding is just not a viable option anymore.

  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.