Re: Multiple Updates statements Vs. Single large update statement

  • From: Nik Tek <niktek2005@xxxxxxxxx>
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>
  • Date: Thu, 14 May 2015 18:42:43 -0700

Hi Mark,

Yes, perf team is saying sql executions are taking long (slower).

Yes, $3, $5, $13 all remained the same in before and after case.

Yes, every column is updated everytime (NOT under any condition check).

Yes, few columns have indexes, but why is the single large statement is
slow (but not in the smaller update statements case).

Commit happens at the end, and there is a single commit in before change.

Nothing has changed on overall update statements. All I did was consolidate
the update statements (did not change the commit behavior).

Thank you
Nik



On Thu, May 14, 2015 at 6:14 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:

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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *raza siddiqui
*Sent:* Thursday, May 14, 2015 8:29 PM
*To:* niktek2005@xxxxxxxxx; 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



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

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

GIF image

GIF image

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2015