SQL Server: Optimize SQL Server OUTPUT clause usage when retrieving database-generated values

[roji]

When inserting an entity with database-generated columns, we currently generate the following (as long as there's no IDENTITY column):

DECLARE @inserted0 TABLE ([Id] int);
INSERT INTO [Blogs] ([Name]) OUTPUT INSERTED.[Id] INTO @inserted0 VALUES (@p0);
SELECT [i].[Id] FROM @inserted0 i;

This could be simplified into this:

INSERT INTO [Blogs] ([Name]) OUTPUT INSERTED.[Id] VALUES (@p0);

The roundabout through the inserted0 TVP is probably because the OUTPUT clause won't work if there's a trigger defined, unless it's an OUTPUT INTO (??) (@AndriySvyryd it this right, any more context?).

Unfortunately, using OUTPUT INTO instead of OUTPUT adds a lot of overhead:

// * Summary *

BenchmarkDotNet=v0.13.0, OS=ubuntu 21.10
Intel Xeon W-2133 CPU 3.60GHz, 1 CPU, 12 logical and 6 physical cores
.NET SDK=6.0.101
  [Host]     : .NET 6.0.1 (6.0.121.56705), X64 RyuJIT
  DefaultJob : .NET 6.0.1 (6.0.121.56705), X64 RyuJIT


|     Method |     Mean |     Error |    StdDev | Ratio | RatioSD |
|----------- |---------:|----------:|----------:|------:|--------:|
|   NoOutput | 2.119 ms | 0.0423 ms | 0.0396 ms |  0.39 |    0.01 |
|     Output | 1.926 ms | 0.0382 ms | 0.0497 ms |  0.36 |    0.02 |
| OutputInto | 5.365 ms | 0.1068 ms | 0.2083 ms |  1.00 |    0.00 |

That's over 3ms just for passing through a TVP! Also, mysteriously the version with no OUTPUT clause at all performs worse...

Remarks:

• We could default to using OUTPUT, and switch back to OUTPUT INTO if the user tells us the table has triggers (via metadata).
• Note that we have Do not track after SaveChanges() #9118 for not retrieving anything; this would make this optimization a bit less valuable.
• If the table has any IDENTITY column, OUTPUT isn't used at all.

Benchmark code

BenchmarkRunner.Run<SequenceBenchmark>();

public class SequenceBenchmark
{
    const string ConnectionString = "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false";
    private SqlConnection _connection;

    [GlobalSetup]
    public async Task Setup()
    {
        _connection = new SqlConnection(ConnectionString);
        await _connection.OpenAsync();

        await using var cmd = new SqlCommand(@"
DROP TABLE IF EXISTS [Foo];
DROP SEQUENCE IF EXISTS [FooSeq];

CREATE SEQUENCE [FooSeq] AS int START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;

CREATE TABLE [Foo] (
    [Id] int PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR FooSeq),
    [BAR] int
);", _connection);
        await cmd.ExecuteNonQueryAsync();
    }

    [Benchmark]
    public async Task NoOutput()
    {
        await using var cmd = new SqlCommand("INSERT INTO [Foo] ([Bar]) VALUES (8)", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [Benchmark]
    public async Task Output()
    {
        await using var cmd = new SqlCommand("INSERT INTO [Foo] ([Bar]) OUTPUT INSERTED.[Id] VALUES (8)", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [Benchmark(Baseline = true)]
    public async Task OutputInto()
    {
        await using var cmd = new SqlCommand(@"DECLARE @inserted TABLE ([Id] int);
INSERT INTO [Foo] ([Bar]) OUTPUT INSERTED.[Id] INTO @inserted VALUES (8);
SELECT [i].[Id] FROM @inserted i;
", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [GlobalCleanup]
    public ValueTask Cleanup()
        => _connection.DisposeAsync();
}

[ajcvickers]

See also #1441, #25345, and #7188.

[roji]

Thanks @ajcvickers, I was looking into IDENTITY as well. Reopened #7188 to revisit that pattern as well.

[ajcvickers]

See also:

• PR: #6675: Add support for triggers that modify values in the inserted/updated row to SQL Server
  • Issue: #6044: Issue with Computed Fields using User Defined Functions
  • Issue:: #6474: SQL Server update does not get values updated by trigger
• PR: #4889: Update pipeline hot path optimizations
• PR: #4707: Use MERGE for batch insert for Sql Server
  • Issue: #4478: SQL Server batching with Identity columns fails for image, text, and ntext columns
• PR #4171: Add a strong order to the generated values returned from batches of inserts for Sql Server
  • Issue: #4080: Inserted entities has different ID (DB vs instance) after SaveChanges
• PR #3947: Use SCOPE_IDENTITY() for SqlServer inserts if only one row is being inserted
  • Issue: #3893: Can we avoid some of the overhead from OUTPUT INTO changes
• PR: #3869: Use OUTPUT INTO for generated values in SqlServer to support tables with triggers
  • Issue: #1441: Support for inserting/updating tables with triggers (not use OUTPUT when batching off)

[roji]

@AndriySvyryd's suggestion of trying a memory-optimized TVP does reduce the time to something much closer to regular OUTPUT. However:

• Unfortunately creating a memory-optimized TVP requires explicit, separate creation of the table type with the appropriate shape (docs); the type persists until explicitly dropped. Creating and dropping the type every time yields horrible perf (over 600ms), so it's not clear how we would manage the lifecycle of these temporary table types.
• Using memory-optimized tables (including temporary variables) requires special database setup.
• There's still an ~8% advantage to OUTPUT over OUTPUT INTO with memory-optimized TVP.

Method	Mean	Error	StdDev	Ratio	RatioSD
NoOutput	1.905 ms	0.0376 ms	0.0649 ms	0.38	0.02
Output	1.785 ms	0.0354 ms	0.0407 ms	0.36	0.01
OutputInto	4.985 ms	0.0983 ms	0.2136 ms	1.00	0.00
OutputInto_MemoryOptimized_persistent_type	1.935 ms	0.0378 ms	0.0518 ms	0.39	0.02
OutputInto_MemoryOptimized_transient_type	610.737 ms	11.9433 ms	19.2862 ms	122.22	6.18

Note: I also benchmarked using ExecuteReaderAsync instead of ExecuteScalarAsync, and there was no difference.

Benchmark code

BenchmarkRunner.Run<SequenceBenchmark>();

public class SequenceBenchmark
{
    const string ConnectionString = "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false";
    private SqlConnection _connection;

    [GlobalSetup]
    public async Task Setup()
    {
        _connection = new SqlConnection(ConnectionString);
        await _connection.OpenAsync();

        await using var cmd = new SqlCommand(@"
DROP TYPE IF EXISTS [tempTableType];
DROP TABLE IF EXISTS [Foo];
DROP SEQUENCE IF EXISTS [FooSeq];

CREATE SEQUENCE [FooSeq] AS int START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;

CREATE TABLE [Foo] (
    [Id] int PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR FooSeq),
    [BAR] int
);

CREATE TYPE [tempTableType] AS TABLE ([Id] int PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON)", _connection);
        await cmd.ExecuteNonQueryAsync();
    }

    [Benchmark]
    public async Task NoOutput()
    {
        await using var cmd = new SqlCommand("INSERT INTO [Foo] ([Bar]) VALUES (8)", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [Benchmark]
    public async Task Output()
    {
        await using var cmd = new SqlCommand("INSERT INTO [Foo] ([Bar]) OUTPUT INSERTED.[Id] VALUES (8)", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [Benchmark(Baseline = true)]
    public async Task OutputInto()
    {
        await using var cmd = new SqlCommand(@"DECLARE @inserted TABLE ([Id] int);
INSERT INTO [Foo] ([Bar]) OUTPUT INSERTED.[Id] INTO @inserted VALUES (8);
SELECT [i].[Id] FROM @inserted i;
", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [Benchmark]
    public async Task OutputInto_MemoryOptimized_persistent_type()
    {
        await using var cmd = new SqlCommand(@"DECLARE @inserted [tempTableType];
INSERT INTO [Foo] ([Bar]) OUTPUT INSERTED.[Id] INTO @inserted VALUES (8);
SELECT [i].[Id] FROM @inserted i;
", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [Benchmark]
    public async Task OutputInto_MemoryOptimized_transient_type()
    {
        await using var cmd = new SqlCommand(@"CREATE TYPE [transientTempTableType] AS TABLE ([Id] int PRIMARY KEY NONCLUSTERED) WITH (MEMORY_OPTIMIZED = ON)
DECLARE @inserted [tempTableType];
INSERT INTO [Foo] ([Bar]) OUTPUT INSERTED.[Id] INTO @inserted VALUES (8);
SELECT [i].[Id] FROM @inserted i;
DROP TYPE [transientTempTableType];
", _connection);
        _ = await cmd.ExecuteScalarAsync();
    }

    [GlobalCleanup]
    public ValueTask Cleanup()
        => _connection.DisposeAsync();
}

[ErikEJ]

Why are you testning with SEQUENCE ? No one uses that, not even migrations conventions.

[roji]

@ErikEJ it doesn't really matter, it can be any sort of HasDefaultValue/HasDefaultValueSql. It's just that for IDENTITY specifically EF Core uses a different mechanism to bring back the value, so I preferred not to benchmark that.

Basically anyone not using IDENTITY, but having a database-generated column is affected by this. Ths above is also specific to inserting few rows - when batching many rows we use MERGE. A similar thing may exist there, but more research is needed.

[ErikEJ]

Seems like quite a narrow audience. For me it is usually either IDENTITY or a client side generated guid.

[roji]

Maybe you're right, though if you have a guid key plus some other generated column, you're affected by this too.

I'm looking at the batching version too (with MERGE), but we may end up deciding it's not worth addressing this.

[ErikEJ]

Maybe you're right, though if you have a guid key plus some other generated column, your affected by this too.

Yeah, but again a very uncommon edge case scenario, in my experience.

[roji]

I've taken a look at our MERGE usage, which also uses OUTPUT INTO. Here are some ideas:

• Verified that our threshold for switching from INSERT to MERGE (4) is good: INSERTs are faster up to 3.
  • However, for 2-3 statements, we could still batch our INSERTs (i.e. the batching threshold and the MERGE threshold aren't necessarily the same). That provides 0.5-1.2ms speed-up against localhost - with a remote SQL Server that would be more impactful.
• In addition, switching to regular OUTPUT instead of OUTPUT INTO has the same perf benefits as for INSERT (5.5ms -> 2.1ms). Caveats:
  • We'd have the same problem with triggers.
  • MERGE doesn't guarantee ordering for OUTPUT-returned rows (Add a strong order to the generated values returned from batches of inserts for Sql Server #4171). If we're getting the key(s) column, we could use that to look up the entity instance at the client (i.e. not care about the ordering). If the key isn't server-generated, we could still request it in the OUTPUT clause for the lookup. I suspect that would mean a non-trivial change.

The above are just ideas at this point... I do believe that 3ms are a pretty huge difference though.

Benchmark results

Method	Rows	Mean	Error	StdDev	Median	Ratio	RatioSD
Merge_with_OutputInto	1	5.263 ms	0.1043 ms	0.1984 ms	5.260 ms	1.00	0.00
Merge_with_Output	1	1.891 ms	0.0376 ms	0.0916 ms	1.898 ms	0.36	0.02
BatchedInserts	1	1.836 ms	0.0364 ms	0.0907 ms	1.856 ms	0.35	0.02
Inserts	1	1.835 ms	0.0442 ms	0.1296 ms	1.872 ms	0.34	0.03
Merge_with_OutputInto	2	5.370 ms	0.1063 ms	0.2048 ms	5.403 ms	1.00	0.00
Merge_with_Output	2	2.009 ms	0.0376 ms	0.0351 ms	2.007 ms	0.37	0.01
BatchedInserts	2	3.260 ms	0.0474 ms	0.0443 ms	3.257 ms	0.60	0.02
Inserts	2	3.871 ms	0.0741 ms	0.1015 ms	3.891 ms	0.72	0.03
Merge_with_OutputInto	3	5.555 ms	0.1094 ms	0.1534 ms	5.547 ms	1.00	0.00
Merge_with_Output	3	2.041 ms	0.0352 ms	0.0329 ms	2.036 ms	0.37	0.01
BatchedInserts	3	4.580 ms	0.0868 ms	0.0928 ms	4.584 ms	0.83	0.03
Inserts	3	5.741 ms	0.1147 ms	0.2856 ms	5.817 ms	1.02	0.08
Merge_with_OutputInto	4	5.451 ms	0.1085 ms	0.1900 ms	5.472 ms	1.00	0.00
Merge_with_Output	4	2.050 ms	0.0406 ms	0.0839 ms	2.068 ms	0.38	0.02
BatchedInserts	4	5.772 ms	0.1146 ms	0.2038 ms	5.803 ms	1.06	0.06
Inserts	4	7.755 ms	0.1079 ms	0.1009 ms	7.762 ms	1.41	0.06
Merge_with_OutputInto	5	5.518 ms	0.1086 ms	0.2066 ms	5.554 ms	1.00	0.00
Merge_with_Output	5	2.090 ms	0.0346 ms	0.0324 ms	2.090 ms	0.38	0.02
BatchedInserts	5	7.037 ms	0.1386 ms	0.2832 ms	7.094 ms	1.28	0.07
Inserts	5	9.639 ms	0.1890 ms	0.3641 ms	9.702 ms	1.75	0.10
Merge_with_OutputInto	6	5.606 ms	0.1120 ms	0.2184 ms	5.600 ms	1.00	0.00
Merge_with_Output	6	2.140 ms	0.0295 ms	0.0276 ms	2.134 ms	0.38	0.02
BatchedInserts	6	8.320 ms	0.1641 ms	0.3314 ms	8.399 ms	1.49	0.08
Inserts	6	11.734 ms	0.1766 ms	0.1652 ms	11.735 ms	2.09	0.11
Merge_with_OutputInto	7	5.676 ms	0.1134 ms	0.2490 ms	5.747 ms	1.00	0.00
Merge_with_Output	7	2.175 ms	0.0423 ms	0.0671 ms	2.192 ms	0.38	0.02
BatchedInserts	7	9.724 ms	0.1851 ms	0.2131 ms	9.749 ms	1.71	0.09
Inserts	7	13.390 ms	0.2642 ms	0.5574 ms	13.497 ms	2.37	0.14
Merge_with_OutputInto	8	5.707 ms	0.1131 ms	0.2152 ms	5.735 ms	1.00	0.00
Merge_with_Output	8	2.218 ms	0.0442 ms	0.0739 ms	2.233 ms	0.39	0.02
BatchedInserts	8	10.807 ms	0.2128 ms	0.2277 ms	10.773 ms	1.92	0.07
Inserts	8	15.381 ms	0.2590 ms	0.2423 ms	15.332 ms	2.75	0.13
Merge_with_OutputInto	9	5.773 ms	0.1149 ms	0.2398 ms	5.833 ms	1.00	0.00
Merge_with_Output	9	2.245 ms	0.0444 ms	0.0494 ms	2.258 ms	0.39	0.02
BatchedInserts	9	12.031 ms	0.2375 ms	0.5010 ms	12.149 ms	2.09	0.12
Inserts	9	17.188 ms	0.3232 ms	0.3023 ms	17.189 ms	3.01	0.13
Merge_with_OutputInto	10	5.860 ms	0.1160 ms	0.2235 ms	5.958 ms	1.00	0.00
Merge_with_Output	10	2.300 ms	0.0413 ms	0.0366 ms	2.295 ms	0.40	0.02
BatchedInserts	10	13.592 ms	0.2710 ms	0.4219 ms	13.617 ms	2.34	0.12
Inserts	10	19.513 ms	0.3207 ms	0.3000 ms	19.528 ms	3.39	0.20

Benchmark code

BenchmarkRunner.Run<SequenceBenchmark>();

public class SequenceBenchmark
{
    const string ConnectionString = "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false";
    private SqlCommand _command;
    private SqlConnection _connection;

    [Params(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)]
    public int Rows { get; set; }

    private async Task Setup()
    {
        _connection = new SqlConnection(ConnectionString);
        await _connection.OpenAsync();

        await using var cmd = new SqlCommand(@"
DROP TABLE IF EXISTS [Foo];
DROP SEQUENCE IF EXISTS [FooSeq];

CREATE SEQUENCE [FooSeq] AS int START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;

CREATE TABLE [Foo] (
    [Id] int PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR FooSeq),
    [BAR] int
);", _connection);
        await cmd.ExecuteNonQueryAsync();
    }

    [GlobalSetup(Target = nameof(Merge_with_OutputInto))]
    public async Task Setup_Merge_with_OutputInto()
    {
        await Setup();

        _command = new SqlCommand { Connection = _connection };

        var builder = new StringBuilder();

        builder.AppendLine(@"DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [Blogs] USING (
VALUES");
        
        for (var i = 0; i < Rows; i++)
        {
            builder.Append($"(@p{i}, {i})");
            if (i < Rows - 1)
                builder.AppendLine(",");
            _command.Parameters.Add(new("p" + i, i));
        }

        builder.Append(@") AS i ([Name], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name])
VALUES (i.[Name])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [i].[Id] FROM @inserted0 i
ORDER BY [i].[_Position];");

        _command.CommandText = builder.ToString();
    }

    [GlobalSetup(Target = nameof(Merge_with_Output))]
    public async Task Setup_Merge_with_Output()
    {
        await Setup();

        _command = new SqlCommand { Connection = _connection };

        var builder = new StringBuilder();

        builder.AppendLine(@"MERGE [Blogs] USING (
VALUES");
        
        for (var i = 0; i < Rows; i++)
        {
            builder.Append($"(@p{i})");
            if (i < Rows - 1)
                builder.AppendLine(",");
            _command.Parameters.Add(new("p" + i, i));
        }

        builder.Append(@") AS i ([Name]) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name])
VALUES (i.[Name])
OUTPUT INSERTED.[Id];");

        _command.CommandText = builder.ToString();
    }

    [GlobalSetup(Target = nameof(BatchedInserts))]
    public async Task Setup_BatchInserts()
    {
        await Setup();
       
        _command = new SqlCommand { Connection = _connection };
        
        var builder = new StringBuilder();
        
        for (var i = 0; i < Rows; i++)
        {
            if (i > 0)
                builder.Append("; ");
            
            builder.Append($"INSERT INTO [Blogs] ([Name]) VALUES (@p{i})");

            _command.Parameters.Add(new("p" + i, i));
        }

        _command.CommandText = builder.ToString();
    }

    [GlobalSetup(Target = nameof(Inserts))]
    public async Task Setup_Inserts()
    {
        await Setup();

        _command = new SqlCommand("INSERT INTO [Blogs] ([Name]) VALUES (@p0)", _connection);
        _command.Parameters.Add(new SqlParameter { ParameterName = "p0" });
    }

    [Benchmark(Baseline = true)]
    public async Task Merge_with_OutputInto()
        => _ = await _command.ExecuteScalarAsync();

    [Benchmark]
    public async Task Merge_with_Output()
        => _ = await _command.ExecuteScalarAsync();

    [Benchmark]
    public async Task BatchedInserts()
        => _ = await _command.ExecuteScalarAsync();

    [Benchmark]
    public async Task Inserts()
    {
        for (var i = 0; i < Rows; i++)
        {
            _command.Parameters[0].Value = i;
            
            _ = await _command.ExecuteScalarAsync();
        }
    }

    [GlobalCleanup]
    public ValueTask Cleanup()
        => _connection.DisposeAsync();
}