Re: Reports 6.0 Performance problem:

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