Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved PowerBuilder with SQL Server db query slow

  1. Advice
0
Votes
Undo
  1. Tim Bruce
    Tim Bruce
  2. PowerBuilder
  3. Friday, 1 September 2023 11:34 AM UTC

PowerBuilder version: 2019 R3 Build 2779

SQL Server 2019

ODBC Driver 17 for Sql Server

I have a query that returns 109 rows in 2 seconds in Management Studio but takes upwards of 30 seconds in db painted within Powerbuilder.  Are there any settings for ODBC I can try?  

 

 

  1. PowerBuilder
  2. Query
  3. SQL Server
Powerbuilder LDAP login
Migrate to 2022 R2
Accepted Answer
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 1 September 2023 17:16 PM UTC
  2. PowerBuilder
  3. # Permalink

how do you set your transaction object on the datawindow?

settrans or settransobject?

 

Comment
Tim Bruce
  1. Tim Bruce
  2. Friday, 1 September 2023 17:23 PM UTC
Thanks for the reply Mike. at this point I'm just running the query in the db painter. Once I get that up to standard I'll try the datawindow again.
  1. Helpful
mike S
  1. mike S
  2. Friday, 1 September 2023 17:56 PM UTC
do you have any user functions as either computed fields or as expressions in that datawindow?
  1. Helpful
There are no comments made yet.
Responses (5)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 1 September 2023 20:07 PM UTC
  2. PowerBuilder
  3. # 1

Hi,

Here's anothe one:

When you say it takes "only" 2 / 3 seconds in Management Studio to retrieve 190 rows, is that SSMS running on the server or on your local PC?

regards.

Comment
Tim Bruce
  1. Tim Bruce
  2. Tuesday, 5 September 2023 10:25 AM UTC
Hey Miguel,



It's on my local machine
  1. Helpful 1
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 1 September 2023 20:02 PM UTC
  2. PowerBuilder
  3. # 2

Hi, Tim - 

In PB, are you running the SELECT statement in a DataWindow or are you running it in the ISQL pane in the Database Painter? If you're comparing results between SQL Server Management Studio (SSMS) and PB, the only meaningful comparison (in my opinion) would be running the SELECT in the ISQL pane/session.

Also, does the PB database connection use the same credentials (database login ID/pswd or Windows Authentication) as SMSS?

What compatibility level is the SQL Server database configured for? 150 (SQL Server 2019)?

As a test, if you encapsulate the SELECT statement in a stored procedure and execute the stored procedure in both SSMS and PB's Database Painter (ISQL), how different are the timings?

Can you post the SELECT statement being tested?

Best regards, John

Comment
Tim Bruce
  1. Tim Bruce
  2. Tuesday, 5 September 2023 10:25 AM UTC
Hey John,



- yes I'm running the select in the database painter

- same credentials

- Compatibility level 150

- I'll try the Store Procedure encapsulation

  1. Helpful 1
Tim Bruce
  1. Tim Bruce
  2. Tuesday, 5 September 2023 10:39 AM UTC
So looks like the stored proc returns the data in 2/3 seconds. At least that gives me an option. Thanks for you and Chris for the suggestion
  1. Helpful
There are no comments made yet.
Tim Bruce
Tim Bruce Accepted Answer Pending Moderation
  1. Friday, 1 September 2023 15:39 PM UTC
  2. PowerBuilder
  3. # 3

Thanks Chris.  We tried both SNC and MSOLEDBSQL it didn't make a difference 

Comment
Load more comments
Tim Bruce
  1. Tim Bruce
  2. Friday, 1 September 2023 16:38 PM UTC
I just tried another db tool connection through the dsn connection set up on my machine (same one I'm using in PB) and the query ran in about 2-3 seconds
  1. Helpful
Tim Bruce
  1. Tim Bruce
  2. Friday, 1 September 2023 16:43 PM UTC
I also turned on tracing and this is the last entry for timing



(03408C10): FETCH NEXT: (0.016 MS / 41179.358 MS)

(03408C10): *** DBI_FETCHEND *** (rc 100)

(03408C10): CANCEL: (0.001 MS / 41179.359 MS)

(03408C10): DISCONNECT: (0.476 MS / 41179.835 MS)

(03408C10): SHUTDOWN DATABASE INTERFACE: (0.000 MS / 41179.835 MS)
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 1 September 2023 17:18 PM UTC
Thanks for that information! Please try the "DROP Statistics" next.

If that does not help, try refactoring the SQL via a Stored Procedure and have the DWO use a SP instead as the SP is a "Compiled Plan" vs SQL from a DWO is a "Dynamic Plan" execution. HTH
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 1 September 2023 14:23 PM UTC
  2. PowerBuilder
  3. # 4

Hi Tim;

  SQL Management Studio uses a native SS DB driver whereas ODBC is "Middle-ware" and might be the cause of the problem.

Have you tried connecting the PB IDE to SS using a native DB Client driver like SNC or better yet, the new MSOLEDBSQL driver?

Then try that SQL again n the DB Painter again.  HTH

Regards ... Chris

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.