1. Pierre Couvreur
  2. PowerBuilder
  3. Friday, 11 October 2019

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

 

 

Accepted Answer
Michael Kramer Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Chris Pollach
  2. Monday, 14 October 2019
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. Michael Kramer
  2. Monday, 14 October 2019
There are no comments made yet.
  1. Sunday, 13 October 2019
  2. PowerBuilder
  3. # Permalink
Pierre Couvreur Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Pierre Couvreur
  2. Monday, 14 October 2019




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. Michael Kramer
  2. Monday, 14 October 2019
No prob Pierre, I make bigger mistakes than that all the time :)
  1. Miguel Leeuwe
  2. Monday, 14 October 2019
There are no comments made yet.
  1. Monday, 14 October 2019
  2. PowerBuilder
  3. # 1
Miguel Leeuwe Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Sunday, 13 October 2019
  2. PowerBuilder
  3. # 2
Miguel Leeuwe Accepted Answer Pending Moderation
0
Votes
Undo

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.
  1. Sunday, 13 October 2019
  2. PowerBuilder
  3. # 3
Pierre Couvreur Accepted Answer Pending Moderation
0
Votes
Undo

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
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. Michael Kramer
  2. Friday, 11 October 2019
There are no comments made yet.
  1. Friday, 11 October 2019
  2. PowerBuilder
  3. # 4
Matthew Balent Accepted Answer Pending Moderation
1
Votes
Undo

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
Oh correct myself after reading the other answers: no function calls.
  1. Miguel Leeuwe
  2. Sunday, 13 October 2019
FYI ... global functions are global functions and their execution is the same in an App as it is in the DW Painter.
  1. Chris Pollach
  2. Sunday, 13 October 2019
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. Miguel Leeuwe
  2. Monday, 14 October 2019
There are no comments made yet.
  1. Friday, 11 October 2019
  2. PowerBuilder
  3. # 5
Roland Smith Accepted Answer Pending Moderation
2
Votes
Undo

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
To add to Roland's great suggestion ... AND no ancestor code in that event either!
  1. Chris Pollach
  2. Friday, 11 October 2019
There are no comments made yet.
  1. Friday, 11 October 2019
  2. PowerBuilder
  3. # 6
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.