RE: [sqr-users] SQR report performance

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

I agree with Richard and Dwaine, but there is another point to remember -
the time you take to load the arrays.  Certainly, reading the same thing
over and over from an array is faster than reading it over and over again
from a networked disk drive.  But are you reading the same thing over and
over?  Or are you reading each thing once, but inefficiently?  The slowest
part of most SQRs, and the area with the greatest potential for improvement,
is the SQL statements.  You should strive to to execute the fewest, simplest
SQL statements possible.  The advantage of an array, or a load-lookup
statement, is that you can replace 100,000 single row selections with one
selection of 100,000 rows.  We could probably give you all sorts of specific
advice if you tell us more about the program.

-----Original Message-----
From: Bartlett, Dwain [mailto:dbartlett@state.mt.us]
Sent: Thursday, August 05, 2004 9:30 AM
To: 'This list is for discussion about the SQR database reporting
languagef rom Hyperion Solutions.'
Subject: RE: [sqr-users] SQR report performance


I two have had success with arrays.  I have been programming more year then
most of you are old.  I have done PeopleSoft and Sqr 7+ years.  I had one
sqr that took over two hours to run if I looped thru the tables.  I was able
to create arrays and only go thru the tables once and cut the time to less
than 2 min.

Dwain Bartlett
SABHRS Services Bureau, ITSD
HR Systems Analyst
(406) 444-0418
dbartlett@state.mt.us


-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Thursday, August 05, 2004 10:23 AM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] SQR report performance



I haven't timed the differences so my claim is based on theory and the
fact that I have been able to get very good response from arrays.  An
array is a data structure in memory; a table, temporary or otherwise, is
a file structure.  Databases (and table accesses) are pretty fast these
days but there is always some overhead related to the db structure and
file i/o. Often, though, the real table access burden is due to location
of the table on a remote machine and the network overhead involved in
transferring the data.  In truth, some disk i/o  (swapping) may be
involved with the use of arrays if they are large or not accessed all at
once.  This last point is something I always pay attention to.  If I can
load and/or access the array all in one fell swoop, I do.  Not always
possible.  If your program is being executed on a server, even an array
will experience network data transfer costs.  Kind of tweaky but fun,
no?  Another reason programmers need to know how it all works.

Best,

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu


-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Bryson, Steven
Sent: Thursday, August 05, 2004 10:48 AM
To: 'This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] SQR report performance

Richard, you mention that use of arrays would help performance.  How is
their performance in comparison to temp tables or is there none?

-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Wednesday, August 04, 2004 4:39 PM
To: This list is for discussion about the SQR database reportinglanguage
from Hyperion Solutions.
Subject: RE: [sqr-users] SQR report performance



Report tuning is, in my experience, not that simple.  The SQL queries
themselves need to be well tuned.  The code needs to be efficient,
streamlined, well organized.  Don't do anything in a loop 100,000 times
that only needs to be done once.  That sort of thing.  There is no
substitute for review of the code by an experienced person.  Boilerplate
code by novice programmers can get the job done but can almost always be
improved. The judicious use of arrays can help big time.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
 

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Chu, Nicholas
Sent: Wednesday, August 04, 2004 3:10 PM
To: 'sqr-users@sqrug.org'
Subject: [sqr-users] SQR report performance

I was wondering if anybody can let me know what parameters in SQR can be
expanded to let it run faster?

We have SQR reports that is taking almost 20mins to run depending on the
size on the shipment.
We are accessing Oracle 9i using 

SQR V6.1.4 Copyright (C) Brio Technology 1994-2000.  All Worldwide
Rights
Reserved.

These are the only 3 that we've set:
SUBVARS=250
EXPRESSIONSPACE=2048
SQLSIZE=14096

I wonder what parameters and how much would take care of my problem.
Thanks!!

Nicholas

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users


"The sender believes that this E-mail and any attachments were free of
any
harmful and malicious code or defects when sent.  This message and its
attachments could have been infected during transmission.  By reading
the
message and opening any attachments, the recipient accepts full
responsibility for taking protective and remedial action regarding the
code
or such defects.  The sender is not liable for any loss or damage
arising in
any way from this message or its attachments."

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users