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

Resolved DW object fast in IDE and very slow in DW control

  1. Issue
0
Votes
Undo
  1. Pierre Couvreur
    Pierre Couvreur
  2. PowerBuilder
  3. Friday, 11 October 2019 13:39 PM UTC

Hello,

How could it be that a DW object retrieves the data in 10 seconds in the DW painter, and 10 minutes in a DW control ?

The DW retrieves 241 rows and 1420 nested rows.

The DW control is pure PB, it contains no user event nor code. The window is also pure PB. The only code is in the window's open event and contains dw_1.SetTransObject(SQLCA) and dw_1.retrieve(). 

The DB is Oracle. The connection's parameters are the same in the IDE and the application, and so are the datasource and the data retrieved.

Any idea is welcome !

Thank you,

Pierre

 

 

How to use .srd file generated in C# project?
Monthcalendar-Control has a different look in exe
Accepted Answer
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Sunday, 13 October 2019 01:19 AM UTC
  2. PowerBuilder
  3. # Permalink

A thought => Does your app uses DisableBind=1 but your IDE's DB profile DisableBind=0?

Any difference on DisableBind and/or StaticBind could severely impact performance. You may even have code running that changing some of these values while app is running. I would check the values immediately before you fire off the Retrieve for the master DW containing the nested DW.

StaticBind=0 risks forcing your app to describe and stage most retrievals for most DW objects.

DisableBind=1 risks turning your nested DW into a SQL generator where the SQL differs row-to-row so new query plan needs to be decided for every retrieve. Being very complex SQL I can see performance degrade. Especially if app has to describe + stage every 241 instances. EX: 241 x 2 seconds = 8 minutes.

HTH /Michael

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 14 October 2019 15:04 PM UTC
Wow ... I would not of suspected that kind of degradation by using the bind setting! Normally, that would have introduced a 10-15% degradation - not a 10x performance drop. Very puzzling but, glad you found the cause!
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Monday, 14 October 2019 15:26 PM UTC
I've had insanely slow data dictionary experience on Oracle before. That combined with "complex SQL" is why I went for DisableBind=1 at first attempt.
  1. Helpful
There are no comments made yet.
Responses (7)
Pierre Couvreur
Pierre Couvreur Accepted Answer Pending Moderation
  1. Monday, 14 October 2019 07:31 AM UTC
  2. PowerBuilder
  3. # 1

Hello,

First I want to thank everybody for taking time to help me, I appreciate everyone's valuable effort !

Special thank to Michael Kramer who found the culprit : DisableBind was 0 in the IDE's DB profile while it was 1 in the application's DB profile !

The difference is huge in that particular report, but I guess the application's overal performance will be improved. I still have to make some tests to ensure there won't be side effects with default columns values, ...

The week starts well :-) !

Pierre

 

Comment
Load more comments
Pierre Couvreur
  1. Pierre Couvreur
  2. Monday, 14 October 2019 08:30 AM UTC
Yes Miguel, so sorry, I read the profiles too fast, my big mistake :-( . The parameters should have been the same, and for some reason there were not (or 'not any more') :-( !
  1. Helpful
Michael Kramer
  1. Michael Kramer
  2. Monday, 14 October 2019 09:14 AM UTC




Disable bind has the issue of being open for SQL injection attacks. Like:

DisableBind = 0 ==> SQL = SELECT ... FROM ... WHERE ... AND email = ?

DisableBind = 1 ==> SQL = SELECT ... FROM … WHERE ... AND email = 'attack'or'1'!='@mydomain.com'

Worst :: That is actually valid formatted e-mail, so what do you do? Disallow valid e-mails? DisableBind=0?
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 14 October 2019 09:43 AM UTC
No prob Pierre, I make bigger mistakes than that all the time :)
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Sunday, 13 October 2019 15:48 PM UTC
  2. PowerBuilder
  3. # 2

Another thing to try to chase down the problem, since there were users having problems with Opening of windows being slower on w10 than on w8.3 for example, don't do the retrieve in the open event. Do it with POST or just for trying, see how it behaves when you do a retrieve from a button that you put on the window.

See if it makes a difference.

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Sunday, 13 October 2019 15:35 PM UTC
  2. PowerBuilder
  3. # 3

Hi Pierre,

I would say:

- create a new tiny app with one window that retrieves the data

- then compare retrieval times

 

Does it happen when running the executable only or is it also slow when running the application from the IDE?

(if only with the executable, we might think of a missing runtime library like the one for the "themes").

Comment
There are no comments made yet.
Pierre Couvreur
Pierre Couvreur Accepted Answer Pending Moderation
  1. Friday, 11 October 2019 14:37 PM UTC
  2. PowerBuilder
  3. # 4

There is absolutely no code in any event of the DW control (it's a pure PB DW control taken from the object list, not a user object).

No global function neither in any item of the DW object.

The SQL datasource in the nested DW is quite long, with UNION, MINUS and so on. If I drop the nested DW from the main one, the retrieve is fast. However the DW (with its nested report) works perfectly from within the DW painter...

Comment
Michael Kramer
  1. Michael Kramer
  2. Friday, 11 October 2019 15:49 PM UTC
Based on this info my initial guess is that something makes the 241 nested retrieve calls very, very slow when you run the app.



Do you see same slow behavior when you run the app as deployed EXE (without PB IDE open)?
  1. Helpful
There are no comments made yet.
Matthew Balent
Matthew Balent Accepted Answer Pending Moderation
  1. Friday, 11 October 2019 14:10 PM UTC
  2. PowerBuilder
  3. # 5

Any code/expressions/global function calls in the objects in the datawindow control?

Have to run a trace to verify the sql being sent to the database?

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Sunday, 13 October 2019 15:53 PM UTC
Oh correct myself after reading the other answers: no function calls.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 13 October 2019 20:10 PM UTC
FYI ... global functions are global functions and their execution is the same in an App as it is in the DW Painter.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 14 October 2019 07:00 AM UTC
Hmm, yes you're right Chris, I stand corrected. So ... what was it that didn't work? Ah, now I remember: Any global functions which depend on things like instantiated objects, like gnv_app, etc. and also any nested reports that you have to retrieve manually by first doing a GetChild and which cannot be solved by the attributes / arguments on the dw itself.
  1. Helpful
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 11 October 2019 14:08 PM UTC
  2. PowerBuilder
  3. # 6

The most common cause would be the presence of code or comments in the retrieverow event. Double check to make sure there is nothing in that event. Anything, even comments will slow it down.

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 11 October 2019 16:46 PM UTC
To add to Roland's great suggestion ... AND no ancestor code in that event either!
  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.