Powerbuilder Embedded / Datawindow SQL generates SQL with wrong datatypes

Question

I'm using powerbuilder 10.2

I have a simple select statement that returns one result from a table with 3 million rows.

SELECT SOME_DATA
INTO :ls_data
FROM SOME_TABLE
WHERE PARAM1 = :ls_param
AND PARAM2 = :ls_param2;

When I run the query in application, it takes about 2 seconds but when I run it in SSMS, the result returns in less than 100 milliseconds. And I've made a pretty interesting discovery when I captured the query being ran from powerbuilder application with SQL profiler:

exec sp_executesql N'SELECT SOME_DATA FROM SOME_TABLE WHERE PARAM1 =@P1 AND PARAM2 =@P2 ',N'@P1 nvarchar(10),@P2 nvarchar(3)',N'112223',N'44252525'

The where clauses PARAM1 and PARAM2 are defined as VARCHAR type yet powerbuilder is somehow thinking that it is a NVARCHAR column. This is causing the bottleneck in our performance.

Is there a way of forcing powerbuilder to generate sql of type varchar instead of nvarchar?

Edit:

I tried to run the above query in datastore to see if there would be any difference. It generates almost identical query and still suffers from the same problem. I'm guessing this problem isn't limited to embedded SQL only

Edit2:

Looking deeper into the problem, SQL Server's sp_executesql only accepts unicode types (ntext,nchar,nvarchar) as parameter, which is why I'm assuming powerbuilder defaults to nvarchar. So I guess my question now becomes how to prevent powerbuilder from using sp_executesql and use something else like EXECUTE(@SQL). Or any other ideas would be appreciated.

TtT23 · Accepted Answer · 2015-02-04 13:28:52Z

Alright after long hours of analysis, I've finally discovered the issue.

tldr; Powerbuilder bug. Well, to be more precise, a huge limitation of the platform. Set DisableBind=1 in your database connection string

Long Answer:

In the database connection string, there is an option known as DisableBind to bind variables in an SQL statement to its supported datatype. Detailed information can be found in the documentation. Anyhow, when disablebind is set to 0, all of the SQL queries with WHERE clause being supplied by the program are wrapped in sp_executesql. Unfortunately, Powerbuilder does not have a concise way of determining whether to wrap the parameters as VARCHAR or NVARCHAR, so if Unicode option is turned on, Powerbuilder defaults to NVARCHAR when generating sp_executesql statement.

Once you enable the disablebind option, all of the queries are executed natively instead through sp_executesql, so the aforementioned problem does not happen. Unfortunately for our application, enabling this option introduced some breaking change, so we ended up changing the database datatypes from varchar to nvarchar to solve the issue. This increased our application's performance by at least 20%, more than 70% in certain cases.

Hopefully, this will help anyone else who might experience this obscure issue. Or better yet, avoid Powerbuilder at all costs. This thing is like cancer. I'm glad SAP is slowly killing it.

Collectives™ on Stack Overflow

Powerbuilder Embedded / Datawindow SQL generates SQL with wrong datatypes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related