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
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.