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 db = DbContext.CreateDbContext(connString);

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

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

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

int result2 = await db.CommitQueryBatchAsync(batch2);
Console.WriteLine(result2);
// -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 batch3 = QueryBatch.CreateQueryBatch();
for (int i = 0; i < 70; ++i)
	batch3.AddQuery("select [Answer] = 2;");

int result3 = await db.CommitQueryBatchAsync(batch3);
Console.WriteLine(result3);
// -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 = QueryBatch.CreateQueryBatch();
var batch2 = QueryBatch.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(QueryBatch.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

Specifying custom batch size explicitly (per commit):

var batch = QueryBatch.CreateQueryBatch();
for (int i = 0; i < 43; ++i)
	batch.AddQuery("select [Answer] = 1;");

int result = await db.CommitQueryBatchAsync(queryBatch: batch, batchSize: 10);
Console.WriteLine(result);
// -4
// 3 batches with 10 queries and last batch with 13 queries

Changing the default batch size on db context:

var batch = QueryBatch.CreateQueryBatch();
for (int i = 0; i < 43; ++i)
	batch.AddQuery("select [Answer] = 1;");

db.BatchSize = 10;
int result = await db.CommitQueryBatchAsync(batch);
Console.WriteLine(result);
// -4
// 3 batches with 10 queries and last batch with 13 queries