Performance issues in Entity Framework while updating records in a oracle table

Question

Some basic information about my architecture:

C# Web API in .Net 4.8
Entity Framework 6.2
Oracle database

Test case: update about 17 records in a table which consists of 1,126,126 records. Table is having primary key on Id column.

Option 1: (Execution time ~43 seconds)

var items = _context.TRADELINEs.AsNoTracking()
                    .Where(x => tradelines.Keys.ToList().Contains(x.ID))
                    .ToList();            

foreach(var i in tradelines) // tradelines is -> Dictionary<string,string> tradelines
{                
    string consolidateFlag = string.IsNullOrEmpty(i.Value) ? "0" : i.Value;
    var newTradeline = new TRADELINE
    {
        ID = i.Key,
        PREVCONSOLFLG = items.Where(x => x.ID == i.Key).FirstOrDefault().CONSOLFLG,
        MODTS = DateTime.Now,
        CONSOLFLG = consolidateFlag
    };
    _context.TRADELINEs.Attach(newTradeline);

    _context.Entry(newTradeline).Property(x => x.PREVCONSOLFLG).IsModified = true;
    _context.Entry(newTradeline).Property(x => x.CONSOLFLG).IsModified = true;
    _context.Entry(newTradeline).Property(x => x.MODTS).IsModified = true;
}
_context.SaveChanges();

Option 2: (Execution time ~36 seconds)

_context.Configuration.AutoDetectChangesEnabled = false;
var items = _context.TRADELINEs.AsNoTracking()
                    .Where(x => tradelines.Keys.ToList().Contains(x.ID))
                    .ToList();            

foreach(var i in tradelines) // tradelines is -> Dictionary<string,string> tradelines
{                
    string consolidateFlag = string.IsNullOrEmpty(i.Value) ? "0" : i.Value;
    var newTradeline = new TRADELINE
        {
            ID = i.Key,
            PREVCONSOLFLG = items.Where(x => x.ID == i.Key).FirstOrDefault().CONSOLFLG,
            MODTS = DateTime.Now,
            CONSOLFLG = consolidateFlag
        };

    _context.TRADELINEs.Attach(newTradeline);
    _context.Entry(newTradeline).Property(x => x.PREVCONSOLFLG).IsModified = true;
    _context.Entry(newTradeline).Property(x => x.CONSOLFLG).IsModified = true;
    _context.Entry(newTradeline).Property(x => x.MODTS).IsModified = true;
}

_context.SaveChanges();
_context.Configuration.AutoDetectChangesEnabled = true;

I added the

_context.Database.Log = s => System.Diagnostics.Debug.WriteLine("SQL : " + s);

line to verify the generated SQL. In log I can see that every update statement is taking around 2-3 seconds but the same SQL statement is getting executed in few milliseconds in SQL Developer tool.

Question: Is there any way to make execution faster while using Entity Framework only? I would like to keep ExecuteSqlCommand as the last option.

Here is the generated SQL by EF:

SQL : update "TRADELINE"
set "CONSOLFLG" = :p0, "MODTS" = :p1, "PREVCONSOLFLG" = :p2
where ("ID" = :p3)

SQL : -- :p0: '0' (Type = String, Size = 1)

SQL : -- :p1: '12/13/2019 12:09:36 PM' (Type = Date)

SQL : -- :p2: '0' (Type = String, Size = 1)

SQL : -- :p3: 'b0b2a534-db52-4dcc-b369-a5f222157d7c' (Type = String, Size = 36)

SQL : -- Executing at 12/13/2019 12:10:01 PM -06:00

SQL : -- Completed in 2538 ms with result: 1

This seems like parameter sniffing. What is the actual SQL that is being sent to the server? — Chris Dunaway, Dec 13 '19 at 20:40
@ChrisDunaway I've added the generated sql by EF. This is going to oracle database. I cant intercept queries on that sever. — Shaggy, Dec 13 '19 at 20:53
What is the dbtype for `"ID"` column. Sometimes tools makes required conversions internally. This way it may not be using the primary key index. Because of type mismatch. — Eldar, Dec 13 '19 at 20:58
@Eldar: Aahh!! Good point! I just check and found out in oracle the type is `CHAR(36 BYTE)` but in model the type is `string`. — Shaggy, Dec 13 '19 at 21:13
We had the same situation in Oracle i remember that :). I don't know how far you went with your project but we had issues to overlap features between Oracle and EntityFramework. — Eldar, Dec 13 '19 at 21:19
This code is already in production environment and now giving performance issue. Do you think Oracle and EF is a bad design idea? — Shaggy, Dec 13 '19 at 22:43

score 1 · Accepted Answer · answered Dec 14 '19 at 00:04

1

It was related to Parameter sniffing. Following two post answers helped me to come up with solution:

https://stackoverflow.com/a/21841170/1169180

https://stackoverflow.com/a/15772873/1169180

I have to change my model to:

public partial class TRADELINE
    {
        [StringLength(36)]
        [Column(TypeName = "char")] // This line got added later
        public string ID { get; set; }
    }

After doing this change, my each update query took around ~50 ms to process.

answered Dec 14 '19 at 00:04

Shaggy

5,422
28
98
163

Cool. Technically this isn't parameter sniffing, but a data type mismatch between the parameter type and the table column type preventing index use. If the client sends the parameter as a unicode type, Oracle has to convert the table column to match. – David Browne - Microsoft Dec 14 '19 at 00:07
Good to know about that. I am just wondering if i was using SQL database with the index column type as `guid`, what would be the model data type in my application? Assuming model was generated by EF. – Shaggy Dec 14 '19 at 00:13
It would have been a System.Guid, which is an exact match for SQL Server’s UNIQUEIDENTIFIER type. – David Browne - Microsoft Dec 14 '19 at 03:44

Performance issues in Entity Framework while updating records in a oracle table

1 Answers1