RE: Multiple Updates statements Vs. Single large update statement

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 14 May 2015 21:14:54 -0400
Message-ID: <079401d08eac$8de8fcc0$a9baf640$_at_rsiz.com>

It is the opposite of expected that the change described would make things slower. (I’m presuming by “the team is reporting regressions for all the operations happening on the table” you mean they are slower.)  

Since there are differences in the symbolic values, it leads to a suspicion that the code might not be iso-functional. Are $3, $5, and $13 from the “before” all the same and the same as $21 in the “after?”  

Are all the columns updated every time, or only under certain conditions? If one of the column updates is relatively expensive but is only done some of the time, for example, in the “before” version, then that would be an explanation.  

Are any of the columns being updated parts of indexes or constraints?  

Does the commit take place immediately? Is there a single commit for the “before version?”  

Sql trace and/or session statistics regarding waits would be useful ways to investigate actual performance, and of course if your code is instrumented finer granularity is available. (see MethodR)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of raza siddiqui Sent: Thursday, May 14, 2015 8:29 PM
To: niktek2005_at_gmail.com; ORACLE-L
Subject: Re: Multiple Updates statements Vs. Single large update statement  

Very important to understand concept of a TRANSACTION - the unit of work bound by COMMIT.

If all the work is part of the same transaction, then you CAN have a single or multiple DML statements terminated by the COMMIT. If the "work" being done are for different transaction, then you MUST COMMIT inbetween.

QUESTION:

• Of the 73 columns, how many are being updated as part of this transaction ?
• You haven't mentioned how many rows are in the table and whether INDEXES are being used.
• Hoping the NCLOB is NOT being updated.

Corrections welcome.
Raza

On 5/14/2015 4:48 PM, Nik Tek wrote:

Hi Gurus,  

I have a table with 73 columns in it, with data_type count as below

 data_type | count

------------------+-------

 Date             |     3

 NCLOB            |    12

 INTEGER          |    25

 NVARCHAR2(255) | 20  NUMBER | 13   In the application, code had multiple update statements (something like 14 update

statement) with few columns  

I recommended to consolidate all the multiple update statements into a single

large update statement, and commit once  

Now, with this change, performance team is reporting regressions for all the

operations that are happening on the table.  

Question:

1. Is this bad solution (more curious to know why sql execution is taking longer)?
2. Is there a way to measure why sql execution is taking longer?

e.g:

• BEFORE ==

UPDATE T1 set c2 = $1, c3=$2 WHERE c1=$3;

UPDATE T1 set c5 = $1, c7=$2, c8=$3, c11=$4 WHERE c1=$5;

UPDATE T1 set c22 = $1, c32=$2 WHERE c1=$3;

UPDATE T1 SET C10 = $1 , C12 = $2 , C21 = $3 , C31 = $4 , C41 = $5 , C43 = $6 , C44 = $7 , C45 = $8 , C46 = $9 , C47 = $10 , C48 = $11 , C49 = $12 WHERE C1 = $13    

• AFTER ==

UPDATE T1 set c2 = $1, c3=$2, c5 = $3, c7=$4, c8=$5, c11=$6, c22 = $7, c32=$8,

C10 = $9 , C12 = $10 , C21 = $11 , C31 = $12 , C41 = $13 , C43 = $14 ,

C44 = $15 , C45 = $16 , C46 = $17 , C47 = $18 , C48 = $19 , C49 = $20

WHERE C1 = $21;   Could you please provide some light, so I can investigate and explain it to my

perf team for why this is happening?  

-- 

Thank you 

NikTeki

 

-- 
 <http://www.oracle.com> Oracle
Raza Siddiqui | Senior Principal Curriculum Product Manager (DB Server)

Oracle Oracle University - Products & Services
500 Oracle Parkway, MS 4IP3 | Redwood Shores, CA 94065 
 <http://www.oracle.com/commitment> GreenOracleOracle is committed to developing practices and products that help protect the environment 







--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 15 2015 - 03:14:54 CEST