Batched queries

Batched queries

All queries can be batched. However, the batch execution only returns the sum of all number-of-rows affected per batch (as INT), which makes batched queries mostly suited for CREATE/UPDATE/DELETE/MERGE query types. If no rows are affected per-batch, the per-batch result is -1 (which will be summed with the other per-batch results over all batches).

QueryBatch is a simple ordered list or queries. Batched queries are intelligently split over one or more database calls. If the last batch is short (less than 1/3 of batch size), it will be merged into the previous batch rather than trigger an additional database call. Default batch size is 50 queries (can be user-provided, of course).

Single batch

var batch = DbContext.CreateQueryBatch();
for (int i = 0; i < 50; ++i)
	batch.AddQuery("select [Answer] = 2;");

int result = await db.CommitQueryBatchAsync(batch);
Console.WriteLine(result);
// -1
// No rows were changed per batch; 1 batch only (default batch size is 50).

var batch1 = DbContext.CreateQueryBatch();
for (int i = 0; i < 65; ++i)
	batch1.AddQuery("select [Answer] = 2;");

int result = await db.CommitQueryBatchAsync(batch1);
Console.WriteLine(result);
// -1
// No rows were changed per batch; 1 batch only (short 2nd batch of 15 queries merged into the 1st batch)
// short batch is 50/3 = 16 queries or less

Multiple batches

var batch2 = DbContext.CreateQueryBatch();
for (int i = 0; i < 70; ++i)
	batch2.AddQuery("select [Answer] = 2;");

int result = await db.CommitQueryBatchAsync(batch2);
Console.WriteLine(result);
// -2
// No rows were changed per batch; 2 batches:
// 1st batch of 50 queries and 2nd batch of 20 queries
// last batch is larger than short batch - triggers an additional db call

Note:

If you use SQL Server Profiler to inspect database traffic, you will see separate queries instead of batches, which might make you doubt that batching is actually working. This is merely a SQL Server Profiler representation. You can inspect the network traffic with a tool like Wireshark to confirm that batching is working. Another way to confirm is to note that a typical QueryBatch with multiple batches will commit much faster than sequentially invoking the same queries.

Combining query batches

Multiple QueryBatch objects can be combined:

var batch1 = DbContext.CreateQueryBatch();
var batch2 = DbContext.CreateQueryBatch();
for (int i = 0; i < 40; ++i)
{
	batch1.AddQuery("select [Answer] = 1;");
	batch2.AddQuery("select [Answer] = 2;");
}
batch2.Append(batch1); // adding batch1 queries to batch2
int result = await db.CommitQueryBatchAsync(DbContext.CreateQueryBatch(new[] { batch1, batch2 }));
Console.WriteLine(result);
// -3
// No rows were changed per batch; 3 batches:
// 1st batch of 50 queries; 2nd batch of 50 queries; 3rd batch of 20 queries
// last batch is larger than short batch - triggers an additional db call

Custom batch sizes

There are several opportunities to set a custom batch size:

var batch = DbContext.CreateQueryBatch(batchSize: 100); // setting custom batch size on creation
for (int i = 0; i < 200; ++i)
	batch.AddQuery("select [Answer] = 1;");

batch.BatchSize = 123; // changing batch size later
var newBatch = DbContext.CreateQueryBatch(queryBatchList: new[] { batch, batch }, batchSize: 1); // on combining
int result = await db.CommitQueryBatchAsync(newBatch);
Console.WriteLine(result);
// -400
// 400 separate batches with 1 query -- for API demonstration only, don't do this in production