Re: Reports 6.0 Performance problem:
Date: Tue, 12 Oct 1999 16:00:49 GMT
Message-ID: <7tvlv7$unt$1_at_nnrp1.deja.com>
Hi Sean,
I notice that in your while loop, you are re-creating the
parameter list for each record, then launching
Reports for each individual invoice.
It appears that if you had 100 invoices,
you would recreate the parm list 100 times, each with
one invoice, then call the RUN_REPORT 100 times, and each
report would execute and print one invoice...
Here are a couple of suggestions, in general.
Method 1) Still using parm list.
a. Change your while loop to generate parameters
of invoice numbers, but do not RUN_REPORT in the
parameter loop..
Thus:
GO_Block('Invoices');
first_record;
IF NOT Id_Null(Get_Parameter_List('inv_print')) THEN
Destroy_Parameter_List('inv_print');
END IF;
p := Create_Parameter_List('inv_print');
counter := 0;
while :invoices.invoice# is not null LOOP
counter := counter + 1
Add_Parameter(p,'pinv'||to_char(counter),TEXT_PARAMETER,
to_char(:invoices.invoice#));
next_record;
end loop;
Run_Product(...etc...)
b) In a before or after Parameter Trigger, in the report Convert the parameter list into a string to be used as an 'IN' clause in the query.
loop through parameters....
wrkstr := wrkstr + ','+ :parm1
(This is messy, and would require a many parameters declared
and hardcoded).
Method 2) Create In clause before RUN_PRODUCT, and pass
one parameter.
a. Either with a string or still using a parameter list,
loop through the records gathering invoice numbers. Then
format into the In Clause text, as above...however you do it,
the end result should be all the invoice numbers...
wrkstr is ==> 1234,234,567,789,....
b. Build a parameter list with one param.
Add_Parameter(p,'pInClause',TEXT_PARAMETER,wrkstr);
Run_Product(......)
c. The report query uses substitution:
Select a,b,c,d from TheInvoiceTable where InvoiceNumber IN (&pInCluase)
Method 3) Build entire query and pass text file.
When I did this with Laboratory numbers, in forms 5, I hit
a 2K limit on passing parameters. I then chose a different
method.
a. I create the base query in a text file xxx.sql, and set
the report to use query from a file.
b. I still created the In Clause as above, but also created
the entire query and wrote the whole text query out with
Text_IO. Now the text file can be very long and there are
no parameters to pass. I only must guarantee that the Select
statement is complete and correct.
Thus:
wrkSelect := 'Select a,b,c,d '||chr(10)||
'from Invtbl where Invoice in (';
wrkIn := .... the inclause already built ...
wrkend := ') and x=y and field=field etc..... order by xxxxx';
wrkStr := wrkSelect + wrkIn + wrkend;
....
text_io.put_file(.xxxxx.);
....
Run_Product(xxx);
If the select is long, you may need multiple put_lines,
since there are limits on the length of a buffer...
In any event, this is just an example.
Performance should be much better, since there is only one RUN_REPORT, one launching, one set of resources used by reports, and ONE query. The query will be passed to the kernel and return all the rows, so that should also be much faster....
Good Luck,
write to me if you have questions....
Robert Proffitt
Beckman Coulter
Brea, California
RTProffitt 'at' beckman 'dot' com (change words to symbols)
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Oct 12 1999 - 18:00:49 CEST