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

Resolved Slow DW retrieve compared to actual query

  1. Issue
0
Votes
Undo
  1. Bjarne Anker
    Bjarne Anker
  2. PowerBuilder
  3. Thursday, 21 December 2017 09:21 AM UTC

Hi.

 

I have a very basic query which selects about 25 columns from 2 tables using inner join and where-statement on one of the indexes (SQL Anywhere 16).

This query returns 19.000 rows and takes 0.04 seconds in Interactive SQL, but almost 5 seconds in the pfc_retrieve() event on the DW.

The profiling tool doesn't have any more detail than this.

No code on the retrieverow() event or other, and no special code in the query itself.

I've tried with setredraw() before and after retrieve, with no significant improvement in performance.

Has anyone an explanation on why the retrieve is so slow compared to the select-statement in Interactive SQL?

Or perhaps there are tips or tricks I can try to speed up the retrieve?

 

Best regards,

 

Bjarne Anker

Maritech Systems

Norway

  1. Build
Powerbuilder 2017 lost link to the existing offlin...
How to get PBNetRunTime.msi in PowerBuilder 17R3 ?
Responses (10)
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 21 December 2017 13:56 PM UTC
  2. PowerBuilder
  3. # 1

Create a small one window non-pfc app that connects to the database and retrieves the DataWindow. See how long it takes.

Does the DataWindow have any drop-down DataWindows on it? Are there any functions called from a computed field or expression that has embedded SQL in it?

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 21 December 2017 14:21 PM UTC
  2. PowerBuilder
  3. # 2

Hi Bjarne;

  Besides Roland's advise, please make sure that your DW Control does not have any code (even a comment) in the RetrieveRow Event ... that would include any ancestors as well. This can slow down any DWO's retrieval performance exponentially.

Regards ... Chris

 

Comment
Bjarne Anker
  1. Bjarne Anker
  2. Friday, 22 December 2017 09:06 AM UTC
Hi.



 



I've triplechecked, and there is absolutely no code on any event (besides pfc_addrow, pfc_insertrow and pfd_initrow).



There is an expression on the backgroundcolor (active/inactive), but is has no impact.



I've also created a brand new DW with the exact same select, and it runs just as slow.



I also created an n_ds (datastore), assigned the same DW-object and the retrive takes 3.9 secs vs 4.1 secs.



So there is definitely something weird going on here.



Most of our other DW's works just fine, so I'm a bit curios in why this one acts up like this. :)



 



Regards,



 



Bjarne

  1. Helpful
There are no comments made yet.
Ronnie Po
Ronnie Po Accepted Answer Pending Moderation
  1. Friday, 22 December 2017 05:26 AM UTC
  2. PowerBuilder
  3. # 3

How about in the PowerBuilder IDE? If you preview the retrieve in the DataWindow painter, is the performance slow? Is there possibly a DropDownDataWindow that's retrieving automatically at runtime?

Comment
Bjarne Anker
  1. Bjarne Anker
  2. Friday, 22 December 2017 09:00 AM UTC
I takes no time at all in the IDE (0.3 secs perhaps).



 



Bjarne

  1. Helpful
There are no comments made yet.
Eduardo G.
Eduardo G. Accepted Answer Pending Moderation
  1. Friday, 22 December 2017 07:52 AM UTC
  2. PowerBuilder
  3. # 4

Make the retrieve with a datastore then a saveas of the ds and finally an importfile in the datawindow, to have if this way you have greater speed.

Comment
There are no comments made yet.
Clarence Chamorro
Clarence Chamorro Accepted Answer Pending Moderation
  1. Thursday, 28 December 2017 21:09 PM UTC
  2. PowerBuilder
  3. # 5

Have you try Retrieve Options -> Rows As Needed. It may help for your application.

Clarence.

Comment
There are no comments made yet.
Vladimir Prochazka
Vladimir Prochazka Accepted Answer Pending Moderation
  1. Monday, 28 January 2019 13:31 PM UTC
  2. PowerBuilder
  3. # 6

If possible, I recommend moving the query into stored procedure. That should solve your performance issue.

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 28 January 2019 15:51 PM UTC
  2. PowerBuilder
  3. # 7

Hi Bjarne;

   FWIW: Computed Columns could be one aspect here that would degrade retrieval performance due to their high over-head.

Regards... Chris

Comment
There are no comments made yet.
Ashutosh Varshney
Ashutosh Varshney Accepted Answer Pending Moderation
  1. Tuesday, 29 January 2019 17:11 PM UTC
  2. PowerBuilder
  3. # 8

Bjarne,

Try setting the disablebind parameter to 1 in your db connectstring -

SQLCA.DBParm="ConnectString ='DSN=Sales;UID=dba;PWD=sql;',DisableBind=1"

Hope this helps

 

 

Comment
There are no comments made yet.
Bjarne Anker
Bjarne Anker Accepted Answer Pending Moderation
  1. Tuesday, 29 January 2019 17:38 PM UTC
  2. PowerBuilder
  3. # 9

Hi.

 

I will look into that setting. 

But what does it do?

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 29 January 2019 20:20 PM UTC
Hi Bjarne;

FWIW: While this can speed up your SQL a little - it can also change its behaviour here & there.

Regards ... Chris
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Wednesday, 30 January 2019 01:27 AM UTC
Binding is a feature where an application sends the SQL statement with argument place holders and argument values separately. The database server compiles the SQL statement once so the next time it is executed, it just needs to plop in the arguments. With DisableBind=1, PowerBuilder will send the SQL with the argument values embedded. This means that the database server needs to compile it every time. It would seem that this would actually make it slightly slower. The exact impact would depend on how your server handles this option and whether it is processing the same queries many times a day.
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Wednesday, 30 January 2019 01:55 AM UTC
  2. PowerBuilder
  3. # 10

Have you tried creating a new DataWindow object? I have experienced behavior similar to this once or twice many years ago.

I started trouble-shooting by copying the SQL statement source to the Windows clipboard, then created a new DataWindow, pasting the SQL into it and defining any needed retrieval arguments. Do this and make no changes to the visual layout. If it is feasible, substitute the new DW at this point and run the app, open the appropriate window and retrieve the 19,000 rows.

I'll go out on a limb and predict the retrieval will be quick. If it is, the cause of the issue is something in the original DW which makes the PB DW engine perform poorly. In my case, I slowly and methodically added the formatting, edit styles, visual arrangement, tab order, computed fields, data grouping, etc. to the replacement DW, testing the retrieval performance after every step. It took a while, but eventually I found the item(s) that caused the slow retrieval (it's been far too many years since I did this, so I do not remember the details of what was eventually found to be causing the lethargic retrieval.)

HTH,

John

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.