Re: [Q:] How to speed up rpt program ?

In article <2umdbq$i9c_at_ccu2.auckland.ac.nz>

           amccullo_at_ccu1.auckland.ac.nz "Alan MCCulloch" writes:

> eleeb0p_at_menudo.uh.edu (Jiazhi) writes:
>
>
> >I am working in a small information company.
> >We have a perfoance problem.
> >We use the rpt to prepare detail reports. It
> >involvs retrieve data from 10-30 tables and
> >arrange the format and print out. But it is
> >unacceptly slow.

Personally, I really like rpt - I know I'm in a minority here, but what the hell.

It has been suggested that you might use PL/SQL - but as Oracle have (almost wilfully) neglected to build in any user I/O, you're likely to need explicit temporary tables. Can you guarantee that you will have the space? Will the program be portable to sites with different tablespace names? How long will it take you to de-bug the damn thing? Or for someone else to find a subsequent bug once it's in production?

How about Pro*C? Well, do you have the on-site skills to build it? If you had to get some one in to do it, who would support it? Have you got the time and budget to start again from scratch?

So let's look at your rpt. Have you used the array switch on the command line to optimize database fetches? Might help. More probably, have you built the code in an rpt-friendly way? For instance, I'm a fan of huge bloody statements that do everything. Often, in a test environment, these things run like the wind, but once they are ported to a much bigger live environment, the increase in required runtime increases exponentially. Often this is because the retreived data becomes too big to be held in ad-lib memory, and Oracle starts building huge internal tables. A bad thing. So, don't be a fan of huge bloody statements.

Though it's a lot less fun to write, small statements with bodies that fire other statements that fire bodies that...etc... are generally a lot more efficient. Also, if you are selecting (as often one is in rpt) rows that need to be joined back to the same table for an update (or a more complex sql), grab the rowid rather than (or as well as) the primary key, and join back on that. So you end up with ...

select a.rowid,a.fld1,a.fld2
into var_rowid, var_fld2, var_fld2....

and, later on

update a
set ....
where a.rowid=&var_rowid...

One of the reasons I like rpt is that I have yet to be given a reasonable request that I couldn't get rpt to fulfil - this includes performance issues. As (I hope) an encouragement, I once wrote a perfectly functional rpt that took twenty-eight (count 'em) hours to run. We played around with it, got a bit lateral (but didn't add any indexes or change anything outside of the program itself) and got it down to an hour and a half. The changes weren't particularly dramatic - it was just a question of figuring out how it was going about it, and telling it to try another way.

Incidentally, rpf is and always will be fiddly and frustrating. As an aid to yourself, and as a favour to those who come after, lay out your table definitions like this:-

.rem    emp      empname        address1      address2
#dt 1   1 5      7     25       27     35     37     45

.rem    zip
        47 53 #

You may think this trivial, or even anal, but you'll thank me one day.

Best of luck, mate,

M

-- 
-----------------------------------------------------------------------------
                                                    MarkB_at_aboy.demon.co.uk
Lyric Quiz of the Week: "Even Presidents have newspaper lovers.
                         Ministers go crawling under covers."