Re: Multiple Updates statements Vs. Single large update statement

From: Nik Tek <niktek2005@xxxxxxxxx>
To: raza siddiqui <raza.siddiqui@xxxxxxxxxx>
Date: Thu, 14 May 2015 17:45:29 -0700

Hi Raza,

Comments are inline

QUESTION:

- Of the 73 columns, how many are being updated as part of this transaction
?
nik>69 columns are getting updated as part of the transaction (columns
update were same in before and after)

- You haven't mentioned how many rows are in the table and whether INDEXES
are being used.
nik> 1(PK) +11 b-tree indexes

- Hoping the NCLOB is NOT being updated.
nik> NCLOB columns are getting updated as part of the transaction.

Thank you
Nik

On Thu, May 14, 2015 at 5:29 PM, raza siddiqui <raza.siddiqui@xxxxxxxxxx>
wrote:

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

--
[image: Oracle] <http://www.oracle.com>
Raza Siddiqui | Senior Principal Curriculum Product Manager (DB Server)
<+1%20408%20387%204928>
Oracle Oracle University - Products & Services
500 Oracle Parkway, MS 4IP3 | Redwood Shores, CA 94065
[image: Green Oracle] <http://www.oracle.com/commitment> Oracle is
committed to developing practices and products that help protect the
environment

--
Thank you
NikTeki

References:
- Multiple Updates statements Vs. Single large update statement
  - From: Nik Tek
- Re: Multiple Updates statements Vs. Single large update statement
  - From: raza siddiqui

Re: Multiple Updates statements Vs. Single large update statement

Other related posts: