1. dale walker
  2. PowerBuilder
  3. Friday, 7 February 2020 01:56 AM UTC

Issue: Extremely slow query performance using ADO.NET connection to a SQL Server 2016 database using Appeon PB v2019 b2082.

Querying ~700 rows from SQL Server 2016 DB into a grid datawindow which is taking approx 2-3 minutes to complete (from compiled runtime or PB UI).   Exact SQL executing using SQL Server Management Studio takes less than a second to pull the rows.  

Any recommended performance DBParms for ADO.NET that may help?    Also tried using the native SNC driver which is slightly faster (1-2 minutes), but this driver imposes a significant runtime distribution issue since the 200+ app users do not have sql client installed on the their workstations....

shouldn't take this long to query 700 rows from SQL Server?   No performance issues pulling data from Oracle v11/12 databases using native driver.

sample connection profile

[MYDB SECTION]
DBMS=ADO.Net
LogId=userid
LogPassword=pwd
DBParm="DataSource='myservername',Namespace='System.Data.SqlClient',CommandTimeout=180,Isolation='RU'"
Prompt=FALSE
AutoCommit=FALSE
NewLogic=EAS

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 02:05 AM UTC
  2. PowerBuilder
  3. # 1

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

Comment
There are no comments made yet.
dale walker Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 02:11 AM UTC
  2. PowerBuilder
  3. # 2

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

Comment
  1. Chris Pollach @Appeon
  2. Friday, 7 February 2020 04:51 AM UTC
Wow, that's weird. Something is really off kilter! Normally, 700 rows should take 1-2 seconds. This sounds really strange. Try taking the time at the RetrieveStart event and at the RetrieveEnd event of the DC / DS control. That will give you the overall time to process the Select. The time from the RetrieveStart event to the 1st RetrieveRow event will be the time SS takes to process the SQL statement & get the 1st packet ready to send.
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 06:18 AM UTC
  2. PowerBuilder
  3. # 3

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

Comment
There are no comments made yet.
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 09:09 AM UTC
  2. PowerBuilder
  3. # 4

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. 

Comment
There are no comments made yet.
John Raghanti Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 13:13 PM UTC
  2. PowerBuilder
  3. # 5

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.

Comment
  1. Chris Pollach @Appeon
  2. Friday, 7 February 2020 16:29 PM UTC
Hi John .. good point! I am now using SS2019 for my testing (just upgraded from SS2017 last December), so that is probably why my SS performance has been and is, super great.
  1. Helpful
There are no comments made yet.
dale walker Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 18:23 PM UTC
  2. PowerBuilder
  3. # 6

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.

 

 

Comment
There are no comments made yet.
dale walker Accepted Answer Pending Moderation
  1. Friday, 7 February 2020 18:28 PM UTC
  2. PowerBuilder
  3. # 7

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

Comment
  1. Armeen Mazda @Appeon
  2. Friday, 7 February 2020 19:42 PM UTC
I agree with John. This is important troubleshooting step to figure out if it is somehow related to the ADO.NET driver or not.
  1. Helpful
  1. dale walker
  2. Friday, 7 February 2020 20:06 PM UTC
John/Armeen, yes I've tried using the SNC native SQL Server driver in my PB dev. Performance does appear to be slightly faster (10-15%) over the ADO.NET, but still this is too long.



Michael, nothing extra in the DW (ie, no DDDW, etc). Exact same performance 1 column vs 10 columns in the result set grid. I've also tried the following combinations in SQLCA DBParm settings with no effect:



StaticBind=1 (default) or 0

DisableBind=1 (default) or 0



SQLCA.DBParm = "DataSource='myservername',Namespace='System.Data.SqlClient',CommandTimeOut=180,DelimitIdentifier='No',Isolation='Read Uncommitted',StaticBind=1,DisableBind=1"

  1. Helpful
  1. Armeen Mazda @Appeon
  2. Sunday, 9 February 2020 18:30 PM UTC
Ok then it sounds like the problem is the not the fact you are using ADO.NET driver.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 8 February 2020 01:16 AM UTC
  2. PowerBuilder
  3. # 8

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

Comment
  1. Chris Pollach @Appeon
  2. Saturday, 8 February 2020 03:05 AM UTC
I have no idea Miguel. Normally like yourself, when SS queries bog down "in general" is when I run the clear stats. That condition normally affects all clients the same way.
  1. Helpful
  1. dale walker
  2. Monday, 10 February 2020 22:25 PM UTC
chris I was thinking the same given the different plan behavior between ADO.NET and SMS. For some reason the ADO.NET connection was trying to use a different plan, which the updated stats corrected.



In reading more about this slow SQL behavior on the inerweb for other external sources sounds this can get very deep. But appears not all connection options set by SMS are exposed such that these could be easily set the same via DBParm (ie, ARITHABORT, etc) and ADO.NET connection.
  1. Helpful
  1. dale walker
  2. Monday, 10 February 2020 22:25 PM UTC
Thanks all for your help! Much appreciated.
  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.