performance of update

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

performance of update

Post by wgkwvl » Sun 07 Aug 2011 18:21

Hi,

I have noticed a problem with an update.
The update changes only one field. The record itself has a duplication trigger to fill a 'synchronization' table.

If i execute the same statements in a sql session the execution is fast (0.x seconds ), but when execute from Entity Framework / devart this single update takes over 6 seconds.

These statements are executed from my client :
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
update AFDA.AFDA_PATIENTEN
set WIJZIGING_DT = :p0, WIJZIGING_USER = :p1, OFFICIELE_TAAL = :p2 where ((PATIENT_NR = :p3) and (WIJZIGING_DT = :p4))

update AFDA.WGK_ENTITY_VERSIONS
set MAAK_DIRTY = :p0, WIJZIGING_DT = :p1
where ((VERSION_ID = :p2) and (WIJZIGING_DT = :p3))

this statement is executed in the trigger :
UPDATE EVD_PATIENT SET NAAM = :B21 , VOORNAAM = :B20 , GESLACHT = :B19 , GEBOORTE_DT = :B18 , NATIONALITEIT = :B17 , TAAL = :B16 , RIJKSREGISTER_NR =
:B15 , GSM_NR = :B14 , E_MAIL = :B13 , LANDSBOND = :B12 , MUTUALITEIT = :B11 , CODE_GERECHTIGDE = :B10 , LAATSTE_CHECK_MUT = :B9 , PALLIATIEF_IND =
CASE :B8 WHEN 'J' THEN 1 ELSE 0 END, PALLIATIEF_VANAF = :B7 ,
EIDGELEZEN_IND = CASE :B6 WHEN 'J' THEN 1 ELSE 0 END,
AKKOORD_GEGEVENSUITW_IND = CASE :B5 WHEN 'J' THEN 1 ELSE 0 END,
INFOBROCHURE_IND = CASE :B4 WHEN NULL THEN 0 ELSE 1 END, DOKTER_ID = :B3 ,
GEEN_AFSPRAKEN_IND = CASE :B2 WHEN 'J' THEN 1 ELSE 0 END, DIABETES_DT = :B1
WHERE
PATIENT_NR = :B22

Using oracle trace I find :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.01 0.01 0 7 6 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.01 0 7 6 2

Misses in library cache during parse: 3
Misses in library cache during execute: 3

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 9 0.00 0.00
SQL*Net message from client 9 6.39 6.51
log file sync 1 0.00 0.00


So it seems the statement in itself cause no problem but there is something happening in between them.

Anyone know why i loose 6 seconds here ? And how to solve this using Entity framework / Devart ?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 10 Aug 2011 07:34


wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Post by wgkwvl » Wed 10 Aug 2011 13:01

So what you are saying is :

my statement isnt slow because of the
SQL*Net message from client that get sent,

but that the 'SQL*Net message from client ' get sent because my EF is slow ?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 11 Aug 2011 15:15

We mean that Entity Framework implies additional operations (http://msdn.microsoft.com/en-us/library/cc853327.aspx) which provide abstraction and scalability but decrease performance.
Could you please explain your question?

Post Reply