?
Solved

Improve the Update Query Performance

Posted on 2011-10-19
4
Medium Priority
?
323 Views
Last Modified: 2012-06-21
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
Comment
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36992065
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
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 36992136
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36992322
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
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 37872649
GOOD
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question