Performance optimisation on lowlevel (EF, devart)
Posted: Thu 14 Dec 2017 14:34
Hi
problem description high level: the generated sql code I see in Oracle AWR report is having 2 statements instead of using 1 statement and therefore unnecessarily slow.
setup: C# -> entity framework 5.0.505.0 -> DevArt 9.1.131.0 -> Oracle 11.2.03
scenario: I am running high load on a server and it could easily be 30% faster by optimising the generated code. I created an Oracle AWR report within a timeframe of 10 minutes. more than 5 minutes (CPU elapsed time) are consumed by insert and update statements as described below.
Current description:
Generated statement that reaches Oracle is:
My goal:
Code in C# part:
- Call of CommitChanges()
- Using concurrency of ConcurrencyStrategy.StoreWins
But note: In my opinion this should not be about a concurrency setting because this DOB_ROWSETVERSION is a DB column with a trigger that is incremented before rows are inserted or changed.
My question: is it possible to achieve mentioned goal?
Many thanks for support.
problem description high level: the generated sql code I see in Oracle AWR report is having 2 statements instead of using 1 statement and therefore unnecessarily slow.
setup: C# -> entity framework 5.0.505.0 -> DevArt 9.1.131.0 -> Oracle 11.2.03
scenario: I am running high load on a server and it could easily be 30% faster by optimising the generated code. I created an Oracle AWR report within a timeframe of 10 minutes. more than 5 minutes (CPU elapsed time) are consumed by insert and update statements as described below.
Current description:
Generated statement that reaches Oracle is:
Code: Select all
DECLARE
updatedRowid ROWID;
BEGIN
UPDATE DOB
SET DOB_LASTMODIFICATIONACTORID = :p0
, DOB_LASTMODIFICATIONDATETIME = :p1
WHERE DOB_ROWSETVERSION = :p2
AND DBKEY = :p3
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR
SELECT DOB_ROWSETVERSION
FROM DOB
WHERE ROWID = updatedRowid;
END;
Code: Select all
BEGIN
UPDATE DOB
SET DOB_LASTMODIFICATIONACTORID = :p0
, DOB_LASTMODIFICATIONDATETIME = :p1
WHERE DOB_ROWSETVERSION = :p2
AND DBKEY = :p3
RETURNING DOB_ROWSETVERSION INTO :outParameter;
END;
- Call of CommitChanges()
- Using concurrency of ConcurrencyStrategy.StoreWins
But note: In my opinion this should not be about a concurrency setting because this DOB_ROWSETVERSION is a DB column with a trigger that is incremented before rows are inserted or changed.
My question: is it possible to achieve mentioned goal?
Many thanks for support.