Query optimisation

building reports desktopintelligence
1

Hi,

A friend asked me to pass on these questions for some assistance !

(They are against a Progress database but I don’t think that is the problem)

Thanks in advance,

Paul

Listserv Archives (BOB member since 2002-06-25)

2

Well, I don’t know PROGRESS, but I have a guess:

Listserv Archives (BOB member since 2002-06-25)

3

Erich,

A1: This sounds like a rule-based versus cost-based optimizer problem.
I
suspect that your “PROGRESS SQL Editor” uses cost-based optimization,
and
that you do not have the “Perform the cost estimate” option turned in
your
BO4.1 Universe’s connection properties, so BO is using rule-based optimization.

Interesting point this. We are using Oracle (which I believe is your strength) and what you suggest surprises me (I don’t dispute it though!).

Anyway, provided the database has the optimiser set to ‘Cost’ or at minimum ‘Choose’ as default then surely BO isn’t telling it to use Rule based optimisation. The SQL being sent to the database either by SQL*Plus or BO should come under the same ‘analysis’ by the db and use the most appropriate optimiser for the query. Provided the tables and indexes have been ‘Analyzed’ then surely the Cost based optimiser will be used the majority of the time.

We have the ‘Perform the cost estimate’ set on but I haven’t looked to see whether it makes any difference. Does the setting add a database ‘Hint’ to the Select clause?

Any other Oracle tuning things you have found to be useful as far as BusObj is concerned (other than the normal sensible indexing etc)?

On another ‘tuning’ matter, what have you found to be the best all-round ‘Fetch Size’ to have on a universe for use in either c/s or web versions of BusObj?

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)

4

Jonathan,

I didn’t mean to imply that BO would override the optimization method set in an Oracle database, (it would have to issue an “Alter Session Set Optimizer_Goal” command, which I don’t think is an option) but (1) the original question was regarding a Progress database, and I have NO idea how IT handles optimization (perhaps Progress requires you to set your optimization goal for each session), and (2) the original post mentioned a “Progress SQL Editor”, and who knows what it does? I was just speculating that the problem sounded like a rule- versus cost-based issue.

But I’m sorry if I worried anyone needlessly.

As for your other questions, I’m afraid I have found it hard to optimize performance of ANY ad-hoc query tool like BO. The best you can do is to optimize what you think are the most common queries your users are going to run. I don’t have any tricks, either. Just good index design. I have been told that a fetch size of 40-50 is best; we use 50, and haven’t any reason to complain yet.

Erich Hurst
Compaq Computer Corporation

Listserv Archives (BOB member since 2002-06-25)

5

Erich,

But I’m sorry if I worried anyone needlessly.

Don’t worry - you didn’t worry me - after all, I’m not the one with the problem!!

We also use a fetch size of 50 and apart from when I forget to put indexes on multi thousand row tables which end up having full table scans it works fine :wink:

I hope you original response provided the poster with something to consider in Progress (which, I too, know nothing about).

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)