ef core, why does it generate this query instead of a simple insert? (save object graph with byte[] inside) and associated performance issue

c# entity-framework entity-framework-core sql-server

Question

I'm importing data from a legacy DB to a new project, i'm a bit confused about some of the SQL generated by EF Core (i looked at it in the output view as it was performing very poorly vs what i expected).

I'm targeting SQL Server and i'm creating objects that have subproperties of type Image (a poco) here's an example of how it is used on an object with such a property :

MyObject.Thumbnail = new Image()
                {
                    Name = Thumbnail.GetValue("Name"),
                    OriginalData = ImageData,
                    Data = ImageData,
                    Width = bmp.Width,
                    Height = bmp.Height,
                    Format = ImageFormat.Jpg
                };

I'm left confused about the generated SQL (and to be honest i don't understand it, is it declaring a temp table and then merging? Why?) and by its performance (it's taking 17 seconds to insert 10 elements with 2 image properties each, i'm running the SQL Server locally on the same machine as the .net core process running the queries so there is no network lattency and inifnite bandwidth and the byte arrays and fairly small to begin with, mostly in the 300kb range).

Expected performance would be removing one to two zeroes.

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (17,211ms) [Parameters=[@p0='?' (Size = -1) (DbType = Binary), @p1='?' (Size = 4000), @p2='?' (DbType = Int32), @p3='?' (DbType = Int32), @p4='?' (Size = 4000), @p5='?' (Size = -1) (DbType = Binary), @p6='?' (DbType = Int32), @p7='?' (Size = -1) (DbType = Binary), @p8='?' (Size = 4000), @p9='?' (DbType = Int32), @p10='?' (DbType = Int32), @p11='?' (Size = 4000), @p12='?' (Size = -1) (DbType = Binary), @p13='?' (DbType = Int32), @p14='?' (Size = -1) (DbType = Binary), @p15='?' (Size = 4000), @p16='?' (DbType = Int32), @p17='?' (DbType = Int32), @p18='?' (Size = 4000), @p19='?' (Size = -1) (DbType = Binary), @p20='?' (DbType = Int32), @p21='?' (Size = 8000) (DbType = Binary), @p22='?' (Size = 4000), @p23='?' (DbType = Int32), @p24='?' (DbType = Int32), @p25='?' (Size = 4000), @p26='?' (Size = 8000) (DbType = Binary), @p27='?' (DbType = Int32), @p28='?' (Size = -1) (DbType = Binary), @p29='?' (Size = 4000), @p30='?' (DbType = Int32), @p31='?' (DbType = Int32), @p32='?' (Size = 4000), @p33='?' (Size = -1) (DbType = Binary), @p34='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [Images] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, 0),
(@p7, @p8, @p9, @p10, @p11, @p12, @p13, 1),
(@p14, @p15, @p16, @p17, @p18, @p19, @p20, 2),
(@p21, @p22, @p23, @p24, @p25, @p26, @p27, 3),
(@p28, @p29, @p30, @p31, @p32, @p33, @p34, 4)) AS i ([Data], [Description], [Format], [Height], [Name], [OriginalData], [Width], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Data], [Description], [Format], [Height], [Name], [OriginalData], [Width])
VALUES (i.[Data], i.[Description], i.[Format], i.[Height], i.[Name], i.[OriginalData], i.[Width])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;
1
0
3/26/2019 10:00:17 PM

Accepted Answer

For SQL Server SaveChanges batches by default. For this scenario (loading blob data), batching is actually bad, as you don't want a huge parameter set bound on the client and server, and then traversed to load. You want single-insert batches (or if your blobs are really large, SqlClient Streaming, for which you'll need to drop down to ADO.NET).

You configure this on the same line you instruct the DbContext to use SQL Server, eg:

optionsBuilder.UseSqlServer(constr, b => b.MaxBatchSize(1).UseRelationalNulls(true) );

To disable batching, and to opt out of generating queries that emulate C# null comparison semantics.

If you need to conditionally disable batching, you can add a DbContext constructor parameter that you read on OnConfiguring. EG

public class Db : DbContext
{
    bool disableBatching = false;

    public Db() : base()
    {

    }
    public Db(bool disableBatching)
    {
        this.disableBatching = true;
    }

And if you need to convince your DI container to sometimes hand out DbContexts with batching disabled, you can create a new subtype for that, as most DI containers work off type registrations. EG:

public class NoBatchingDb : Db
{
    public NoBatchingDb() : base(disableBatching: true) { }
}
3
3/27/2019 2:52:21 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow