Switch to RETURNING/OUTPUT as the default way to retrieve generated values for INSERT #27503
Description
#27372 is about optimizing the SQL generated for fetching generated values on SQL Server; as part of that, we will be switching to INSERT INTO foo (bar) OUTPUT VALUES ... by default, which works considerably faster than the current method.
SQL Server, PostgreSQL, MySQL and SQLite all support such a method of retrieving generated values (the standard clause is RETURNING except for in SQL Server). Aside from two SQL statements being generally more efficient (and safer) than one, two SQL statements also need to be wrapped in a transaction, to prevent a race condition where a separate update could cause different values to be returned (see #27446). With #27439 removing transactions for single updates, switching to RETURNING would unlock that perf benefit as well.
For example, SQLite shows the following improvement when switch from our current method to RETURNING:
| Method | Mean | Error | StdDev | Ratio |
|---|---|---|---|---|
| Insert_and_Select_with_transaction | 10.555 us | 0.1769 us | 0.1568 us | 1.00 |
| Insert_and_Select_without_transaction | 5.428 us | 0.0558 us | 0.0495 us | 0.51 |
| Insert_Returning | 5.914 us | 0.0596 us | 0.0497 us | 0.56 |
Benchmark code
BenchmarkRunner.Run<Benchmark>();
public class Benchmark
{
private SqliteConnection _connection;
private SqliteCommand _command;
private async Task Setup()
{
_connection = new SqliteConnection("Filename=:memory:");
_connection.Open();
using var cmd = new SqliteCommand("CREATE TABLE data (id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER)", _connection);
await cmd.ExecuteNonQueryAsync();
}
[GlobalSetup(Targets = new[] { nameof(Insert_and_Select_with_transaction), nameof(Insert_and_Select_without_transaction) })]
public async Task Setup_Insert_and_Select_with_transaction()
{
await Setup();
var sql = @"
INSERT INTO data (num) VALUES (@p0);
SELECT id FROM data WHERE changes() = 1 AND rowid = last_insert_rowid();";
_command = new SqliteCommand(sql, _connection);
_command.Parameters.AddWithValue("p0", 8);
}
[GlobalSetup(Target = nameof(Insert_Returning))]
public async Task Setup_Insert_Returning()
{
await Setup();
var sql = "INSERT INTO data (num) VALUES (@p0) RETURNING id";
_command = new SqliteCommand(sql, _connection);
_command.Parameters.AddWithValue("p0", 8);
}
[Benchmark(Baseline = true)]
public void Insert_and_Select_with_transaction()
{
var tx = _connection.BeginTransaction();
_command.Transaction = tx;
using var reader = _command.ExecuteReader();
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
_ = reader.GetInt32(i);
tx.Commit();
}
[Benchmark]
public void Insert_and_Select_without_transaction()
{
using var reader = _command.ExecuteReader();
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
_ = reader.GetInt32(i);
}
[Benchmark]
public void Insert_Returning()
{
using var reader = _command.ExecuteReader();
reader.Read();
for (var i = 0; i < reader.FieldCount; i++)
_ = reader.GetInt32(i);
}
}/cc @lauxjpn