Batched queries

Batched queries

All queries can be batched. However, the batch execution only returns the sum of all row counts 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.Create(connString);

var batch1 = QueryBatch.Create();
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.Create();
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.Create();
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.Create();
var batch2 = QueryBatch.Create();
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.Create(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.Create();
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.Create();
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