Re: sqr using 90% cpu

[Ray Ontko <rayo@ontko.com>]

> We are having a major problem with some of our sqr reports taking up to 90%
> of the cpu.  We have run the reports through SQL-LAB and explain.sqr and
> they seem to be o.k, No full table scans, using the indexes.  We tried
> moving the tables around in the where clause and this seemed to bring one
> report in particular to 40% but this is still to high. We are using SQR
> V3.0.12.1  on a HP-UX 9000.
>
> Is the problem in the order of the tables in the FROM clause?
> Does the  WHERE clause have an effect on machine performance?
>
> Any information will be appreciated.

Bob,

Are you using Oracle?  Getting the most out of the query
optimizer can sometimes be tricky.  If you used explain plan to
verify that the query is operating the way you want, but the program
still takes too long, you may want to:

1) take a look at the -B option (for the command line or the begin-select).
   This will allow you to fetch more than 10 rows at a time.  This
   may help speed up your program.

2) consider using load-lookup to eliminate extra joins in your query;
   it may be faster to do this in your program than to have database
   do all the work.

By the way, is your program taking too long to run, or does it
just seem to be taking all the available CPU time and the rest
of the system seems slow?  I have seen many situations where
a system is improperly tuned, and the SQR program is able
to run fine and appears to take all the CPU time because
it's the only program asking for it, while the interactive
programs were behaving badly waiting for some other resource
(some limited system resource).

If you looked closely at your program and it appears to be working
the most efficient way possible, you might need to look elsewhere.

Ray
----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Time for a new signature line."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/