Hi Dale;

Like the fast Oracle native driver, PB also supports the SS native driver. Just set your DBMS setting as follows:

SQLCA.DBMS = "SNC"

Regards ... Chris

chris, I tried the SNC driver as well.  As I mention in OP it's a little faster but still slow.  

Issue with native SNC driver is runtime distribution.  Our  200+ users do not have SQL Client installed on their workstations.  The ADO.NET driver doesn't require any client runtime distribution

What speed do you see if you run exact same query from PB IDE's DB Painter using exact same DB profile setup as your app?

DB painter uses the same interface files as your app. You should see same slow performance if bottleneck is in PB runtime files or in DB setup. On the other hand, if DB painter executes query fast I'm tempted to look for cause in your app's behavior. Potentially more going on than you expect. Maybe waiting on locked resource or something timing out for strange reasons.

As Chris points out: If you check time between RetrieveStart and RetrieveRow(row = 1) you get a measure for how fast MSSQL executes the query. If you check time between RetrieveRow(row = 1) and Retrieve(...) function returning you get measure for how fast MSSQL returns resultset to your app - and how long your app spends to process the resultset.

AND - There have been couple of instances where apps ran slow because the UI theme runtime files weren't available. And app was slow despite not using the theme feature at all. App ran normal speed when UI theme runtime files available. No code changes required.

HTH /Michael

Hi. 

In addition to what others mentioned, I would check if the autosize width setting is set to the columns in your grid datawindow. The settings may make the retrieval seem slower, because, depending the mode of autosize width, it calculates the size (width) each column should finally have. If there are to many columns, then this procedure's overhead - impact will be bigger. 

Andreas. 

What is your database compatibility level?

We just recently saw some strange behavior with a query on SQL Server 2014. It was taking 10 minutes to retrieve 100 rows. It drove us nuts for a bit. We ended up finding that it was only SQL Server 2014. If we set the compatibility level to SQL Server 2012 or SQL Server 2016, the retrieve issue goes away entirely.

We're guessing that there's some option turned on in SQL 2014 that was causing the behavior, but couldn't track it down that far.

Really great question and good point...this is a vendor supported sql server database and compatibility level is currently set to 2008 (100).   Not ideal.   

Next step, I'm going to try changing this in on our test instance.  Also work with the vendor to better understand any known issues on their side.

Michael, no performance different between dw painter and app runtime.    I'm effectively measuring SQL retrieve duration  between retrievestart and retrieveend events.

Can you try "UPDATE STATISTICS <table> WITH FULLSCAN --" on each implied table? It might make a difference.

The following will sound strange, but might be worth a try:

- Like a year ago, I used a DB ODBC connection to Tibero. I had a very simple embedded SELECT statement in the code with two " = " operators in the WHERE. It was veeeery slow.

- If I would replace the operators with LIKE 'a_fixed_value_no_percentage', then it would be immediate ...

- Never got to the bottom of it since we decided to use the JDBC driver to connect.

As funny as it sounds, could you try with LIKE instead of "="?

Another thing: have you tried to set TRACE-ing on and see if there's something weird in the log file?

Last one: 

I'm supposing that if you put the select of your dw in a stored procedure and assign that procedure to your dw, things would go faster, since you say that running the sql in the management studio is fast?

If that's not the case, then that might be an indication that your problem is not the communication between powerbuilder and the database, but a "network" problem instead?

HTH