Batch inserts to same table into a single INSERT statements

[maoueh]

Right now, we batch SQL operations together and send them as a single transaction. It appears that a further tweak could be implemented, coalescing INSERT to the same tables together. Right now, each insert is packed as a single query unit:

insert into Info(id, Cost,city) values(1, 100,"Pune");
insert into Info(id, Cost,city) values(2, 50, "Satara");
insert into Info(id, Cost,city) values(3, 65,"Pune");
insert into Info(id, Cost,city) values(4, 97,"Mumbai");
insert into Info(id, Cost,city) values(5, 12,"USA");

But we could actually package those above as a single INSERT query:

insert into Info(id, Cost,city) values
    (1, 100,"Pune"), (2, 50, "Satara"), (3, 65,"Pune"), (4, 97,"Mumbai"), (5, 12,"USA");

Leading to speed up when a lot of inserts is performed.

Ordering

Right now, we fully respects ordering as received from the DatabaseChanges. Full coalescing grouping by table would lead to a different insertion order at the expenses of a faster injection, this exhibits mostly when using some auto-increment column in rows.

We could still coalesce and keeping ordering, in the optimal case everything is still packed in a single query, worst case everything is made as a single query.

Partials Data on same table

The implementation will also need to deal with insert to same table but with a different set of columns. The safest will be to group query by table and set of inserted columns. In practice, we expect most insert to remain constants on terms of initial column inserted.