Home » SQL & PL/SQL » SQL & PL/SQL » how to get the report with huge data in the tables (oracle 10g)
how to get the report with huge data in the tables [message #441275] Sat, 30 January 2010 02:03 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
We have one report.this report will have 72 columns.Internally report will call the procedure.If i take the report for 3 months(approx 68000 records) its coming in 8 mins .if i take the report for 1 year its not coming because of the huge data present in the tables.as of now total 6 lakh records were there.so i am not able to generate the report for 1 year because of the huge data.

we designed the report in such a way that we created one global temporary table with on commit delete rows option.

In this procedure Initially we will join the main tables and insert the main columns data in the global temporary table then after wards update the remaining columns in that global temporary table.this kind of update statments were there upto 20.

For your information we have tuned the queries and most of the indexes were created.if i want to generate the report for 1 year with 6 lakh records what i need to do?as i said we have tuned all most all update statements and main insert statment .i think because of the huge data report is not coming.

can i use any bulk updates in the INSERT and UPDATE statements?if yes how to implement in this scenario?


INSERT INTO GT_CHARGE_REGISTER 
  (DOCUMENT_ID, 
   COMPANY, 
   BRANCH, 
   DEPARTMENT, 
   CHARGE_CODE, 
   DESCRIPTION, 
   INVOICE_DATE, 
   INVOICE_ID, 
   FOREIGN_CURRENCY, 
   ACTUALAMOUNT, 
   EXCHANGE_RATE, 
   CURRENCY, 
   STATUS, 
   STATUS_POSTED, 
   INTERNAL_REMARKS, 
   HOUSENO, 
   type_code) 
  (SELECT CHARGES.DOCUMENT_ID, 
          CHARGES.COMPANY, 
          CHARGES.BRANCH, 
          CHARGES.DEPARTMENT, 
          CHARGES.CHARGE_CODE, 
          CHARGES.DESCRIPTION, 
          T.INVOICE_DATE, 
          T.INVOICE_ID, 
          CHARGES.FOREIGN_CURRENCY, 
          CHARGES.ACTUALAMOUNT, 
          CHARGES.EXCHANGE_RATE, 
          CHARGES.CURRENCY, 
          CHARGES.STATUS, 
          T.STATUS_POSTED, 
          T.INTERNAL_REMARKS, 
          T.HOUSENO, 
          type_code 
     FROM TRANSACTION T, CHARGES CHARGES, JOB JOB, 
    WHERE CHARGES.STATUS = 'INVOICED' 
      AND CHARGES.INVOICE_ID = T.INVOICE_ID 
      AND Job.COMPANY_ID = T.COMPANY_ID 
      AND T.STATUS_POSTED = 'POSTED' 
      AND CHARGES.AMOUNT <> 0 
      AND CHARGES.DOCUMENT_ID = JOB.DOCUMENT_ID 
      AND trunc(t.invoice_date) BETWEEN '01-Jan-2009' AND '31-Dec-2009' 
      AND T.COMPANY_ID = 'XXX'); 
 
UPDATE GT_CHARGE_REGISTER GT 
   SET (GT.CHARGEACCTCODE, spt.CHARGNAME) = (SELECT ACM.ACCTID, ACM.ACCTNAME 
                                                FROM MASTERCHARGES CMAP, 
                                                     ACCOUNTMSTR   ACM 
                                               WHERE CMAP.CHARGEID = 
                                                     GT.CHARGE_CODE 
                                                 AND ACM.ACCTCODE = 
                                                     CMAP.ACCR_ACCTCODE); 
 
UPDATE GT_CHARGE_REGISTER GT 
   SET (GT.CHARGECODE, spt.ChargeDESC) = (SELECT ACM.ACCTID, ACM.ACCTNAME 
                                             FROM MASTERCHARGES CMAP, 
                                                  ACCOUNTMASTER ACM 
                                            WHERE CMAP.CHARGEID = 
                                                  GT.CHARGE_CODE 
                                              AND ACM.ACCTCODE = 
                                                  CMAP.ACT_ACCTCODE); 
 
UPDATE GT_CHARGE_REGISTER GT 
   SET ( GT.DOCID
       , spt.DOCNO
       , spt.docdate
       , GT.jobno
       , GT.SENDER
       , GT.RECEIVER
       , GT.CUSTOMER
       , GT.SALESPERSON
       , GT.CHWEIGHT
       , GT.GROSSWEIGHT
       , GT.VOLUME
       , GT.TYPE, GT.MODE
       ) = 
       ( SELECT hdr.DOCID
              , hdr.DOCNO
              , hdr.docdate
              , hdr.jobno
              , hdr.SENDER
              , hdr.RECEIVER
              , hdr.customerid
              , hdr.salesperson
              , hdr.CHWEIGHT
              , hdr.grossweight
              , hdr.volume
              , hdr.TYPE
              , hdr.MODE
           FROM JHOUSE H 
          WHERE H.shipmentno = GT.HOUSENO
      ) 
 WHERE spt.type_code in ('AWB', 'BL');

[HR: Frank trimmed long lines]

[Updated on: Sat, 30 January 2010 04:02] by Moderator

Report message to a moderator

Re: how to get the report with huge data in the tables [message #441283 is a reply to message #441275] Sat, 30 January 2010 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I am not able read large line

Regards
Michel

[Mod-edit: Frank removed unnecessary blanks.]

[Updated on: Sat, 30 January 2010 04:01] by Moderator

Report message to a moderator

Re: how to get the report with huge data in the tables [message #441510 is a reply to message #441275] Mon, 01 February 2010 12:50 Go to previous messageGo to next message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
chaituu wrote on Sat, 30 January 2010 03:03

      AND trunc(t.invoice_date) BETWEEN '01-Jan-2009' AND '31-Dec-2009' 



Please use proper code. Comparing a DATE column to 2 character strings can only cause trouble. Don't try to cut corners in code or give a response like "it works for me."
Re: how to get the report with huge data in the tables [message #441511 is a reply to message #441283] Mon, 01 February 2010 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 30 January 2010 10:33
I am not able read large line

Regards
Michel

[Mod-edit: Frank removed unnecessary blanks.]

Blanks were necessary to show what happens with long lines.

Re: how to get the report with huge data in the tables [message #441519 is a reply to message #441275] Mon, 01 February 2010 15:08 Go to previous messageGo to next message
John Watson
Messages: 6396
Registered: January 2010
Location: Global Village
Senior Member
Hi - this line
AND trunc(t.invoice_date) BETWEEN '01-Jan-2009' AND '31-Dec-2009' 

could explain why performance degrades as you have more rows, because your trunc function means that the optimizer can't use an index on invoice_date. So, to include joy_division's eminently sensible comment regarding type casting, you could try this:
and t.invoice_date BETWEEN 
TO_DATE('01-JAN-2009','DD-MON-YYYY') AND TO_DATE('31-Dec-2009' ,'DD-MON-YYYY')


and by the way, what is "lakh"?
Re: how to get the report with huge data in the tables [message #441536 is a reply to message #441275] Mon, 01 February 2010 16:30 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
1. Look at the example:
SET AUTOTRACE ON
SELECT something FROM table WHERE DateColumn BETWEEN '1900-01-01T00:00:00' AND '1900-01-01T00:00:01'
This could show that there are 2 predicates: ACCESS PREDICATE which extracts everything since 1900 till now. Then there is a filter predicate which would filter-out some data from it (probably 99.999999999999% of the data). It might be an issue for you. Make sure that you are using correct index access.

2. As you might see I have used ISO-8601 international date format which should be understood by most of the people. You are using some abstract date which wouldn't work on my database. What is "Jan"? Is it name "Joanne" or rather "Julian Andrew Natalie"? Yes - I don't need to know English month names. You should always use
TO_DATE('99-JAN-01','YY-MON-DD')
at least.

3. Since your query needs to access a lot of data - there is not much you can do about it except creating correct index (make sure that it is prefixed and local... I hope that your table is partitioned by range(date)). If possible - you should try to store some meta data so that you don't need to access all the raw data.

4. Horizontal scaling might be a solution.

5. If you are using UPDATE on the data then it would impact performance. Make sure that your index is defragmented and used. Minimize amount of updates. Rebuild fragmented indexes - it might help. Enable row movement - if possible. When inserting data - make sure that you are inserting in a bulk. You might enable compression - it might increase performance (but there is a CPU cost).

6. Check how your indexes are being used. Refer to the optimization guidelines from the OraFAQ. Especially check how the index is being accessed.

7. I am working with a large database that is running some queries periodically to create some reports. Queries are divided into several groups (ex. daily, weekly, monthly, yearly). Application is accessing between 10^3 and 10^9 rows per query (seems more than 68k) and until now I have not seen any performance issues that couldn't be solved by fixing indexes/queries/... using autotrace.
You might find here that some people have much more experience than I do - and they are probably working with much more complex apps.

Good luck
Re: how to get the report with huge data in the tables [message #441555 is a reply to message #441519] Mon, 01 February 2010 21:24 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
and by the way, what is "lakh"?


Quote:
A lakh or lac is a unit in the Indian numbering system equal to one hundred thousand (100,000; 10^5).
Smile

And @OP what is this "spt" alias in your post? may be i should re read you post....let me

sriram Smile

[Updated on: Mon, 01 February 2010 22:11]

Report message to a moderator

Previous Topic: converting a string to date
Next Topic: Procedure back
Goto Forum:
  


Current Time: Sun Sep 25 00:55:07 CDT 2016

Total time taken to generate the page: 0.05496 seconds