• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Improve the Update Query Performance

Hi,
I have written two set of code which will update my staging table after validation. The table can contains more 30000 records. Please let us know which method is good to use and why? Because i am very much bothering about the performance.

Method 1:
declare
    TYPE sample_tab IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
    
    type_sample_tab  sample_tab
    
begin
        SELECT  DISTINCT t1.ITEM_NAME
        BULK COLLECT INTO type_sample_tab
        FROM sample_staging_table t1
        WHERE
            t1.process_flag = 1
            AND t1.request_id = 70440789
            AND NOT EXISTS (SELECT
                                'X'
                            FROM
                                qp_list_headers_tl
                            WHERE
                                LANGUAGE = 'US'
                                AND NAME = t1.price_list_name
                            );

        FORALL i IN 1 .. type_sample_tab.COUNT
        UPDATE sample_staging_table
        SET error_message = 'RECORD NOT EXIST', process_flag = 3
        WHERE item_name = type_sample_tab(i)
        AND process_flag = 1
        AND request_id = 70440789;
end;

Open in new window


Method 2:
    UPDATE
        XXQP_EPM_PRICE_STG pbs
    SET
        pbs.process_flag = 3,
        pbs.error_message = 'RECORD NOT EXIST'
    WHERE
        pbs.process_flag = 1
        AND pbs.request_id = 70440789
        AND NOT EXISTS (SELECT
                            'X'
                        FROM
                            qp_list_headers_tl
                        WHERE
                            LANGUAGE = 'US'
                            AND NAME = pbs.price_list_name
                        );
end;

Open in new window

0
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would say the second method is faster, because it does all in 1 single step, and hence will generate less transactions.

just ensure that on table (view?)  qp_list_headers_tl you have a index on NAME + LANGUAGE

on table  XXQP_EPM_PRICE_STG, you surely have a index on request_id ...
0
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi thanks for your quick reply. But i have seen Oracle BULK COLLECT and FOR ALL will be good to increase the performance and it will not occupy more PGA memory. The transaction will be faster than the second method may be.

Method 2 is kind of FOR loop right. So it will check each and every record in the table. But FORALL will update it in onehshot. Please correct me if i am wrong.
0
 
slightwv (䄆 Netminder) Commented:
No need to guess.  If you want definitive metrics, trace them and run tkprof on the results:  21.4 Using the SQL Trace Facility and TKPROF


http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/sqltrace.htm#PFGRF01020


My guess agrees with angelIII:  Method 2.

>>increase the performance and it will not occupy more PGA memory

Where do you think Oracle will store type_sample_tab?  In the PGA so it will likely use more PGA not less.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
GOOD
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now