Performance optimisation on lowlevel (EF, devart)

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
KommissarK
Posts: 1
Joined: Thu 14 Dec 2017 14:17

Performance optimisation on lowlevel (EF, devart)

Post by KommissarK » 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:

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;
My goal:

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;
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.

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

Re: Performance optimisation on lowlevel (EF, devart)

Post by Shalex » Thu 21 Dec 2017 11:53

Thank you for your request. We will investigate the possibility to optimize the generated SQL in the way you described.

As a current solution, please create a separate context without using StoreGeneratedPattern=Identity/Computed for entity properties not to return any values on inserts/updates. Use this context for the cases when you need to insert/update a lot of records.

Post Reply