Insert Query Batching in v5

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 20-Feb-2019 19:53:15   

For the large database build UOW2 I mentioned in http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=26613 I just tried it in v5.5 with batching.

Unfortunately, it make is 5 times slower no matter what BatchSize I set confused

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Feb-2019 20:48:01   

5 times slower compared to what?

The solution in the mentioned thread was to be implemented in v.5.6

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 20-Feb-2019 22:00:20   

Compared to not adding BatchSize=x

I used the other ticket solely as a reference to the UOW2 with 138k entities being inserted.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 10:29:03   

It's likely slower because it has to sort the entities on type too (for batching and batch them together based on type). Previously it didn't have to do that so it simply sorted them only for dependency order. But that's guesswork of course, I have no data to back that up, but I also don't see where it would be slower otherwise.

I've profiled the code earlier this week to see if the newly implemented sorting was slower, and there's little else I can do about it. I used a graph of 148 entities and 5 types, executed the sorting 1000 times and it took together 440ms, or 0.4ms per sort action of the entire graph. Your graph is exceptionally big and likely makes things choke based on the large amount of types in the graph.

I don't see any improvement points in the code at this moment, other than not using a large graph like that. To give you an idea, it has to request for all entities in that graph all depending and dependent entities, and traverse them, to build the adjacency lists (which is the bulk of the time)

5 times is excessive btw. Could you attach a profiler and see where the times are taken? We tested the batcher with e.g. 1000 inserts which take less than 200ms so it's odd your code takes 5 times longer...

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Feb-2019 11:48:09   

For reference, my code took 29s to import the 138K entities to a local SQL Server Express on an SSD. With batching, it took 2 mins 27s.

I accept my use-case is not typical but I suppose my point was that batching is not necessarily the _free _optimization I had hoped.

To give you an idea, it has to request for all entities in that graph all depending and dependent entities, and traverse them, to build the adjacency lists (which is the bulk of the time) Isn't that the same both with and without batching? ie in the UOW2?

I will try a profiler though to see if it gives any clues.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 13:04:28   

simmotech wrote:

For reference, my code took 29s to import the 138K entities to a local SQL Server Express on an SSD. With batching, it took 2 mins 27s.

29s is also a tremendous amount of time. Is the file presized in sql server? If not, if it runs out of space it is increased by sql server, which can be slow.

I accept my use-case is not typical but I suppose my point was that batching is not necessarily the _free _optimization I had hoped.

There's one thing where it might go wrong and I think it's noticeable when you have a LOT of entities in the queue: it will re-order that queue potentially after every entity based on types. This is not needed if you perform the work from a unit of work: it can do it once: after the complete set of entities to process has been calculated.

I'll see if I can mimic that with a huge set of entities, all inserts.

To give you an idea, it has to request for all entities in that graph all depending and dependent entities, and traverse them, to build the adjacency lists (which is the bulk of the time) Isn't that the same both with and without batching? ie in the UOW2?

I will try a profiler though to see if it gives any clues.

The issue should be in ObjectGraphUtils.DetermineActionQueues, the loop at the bottom.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 14:06:59   

I think it's a regression, and it's visible only if there are a lot of entities in the uow. When I have e.g. 10000 entities in the uow, it's fast, but when I have 130K+ entities in the uow (10000 graphs of 7 nodes), all marked insertable/new, it starts to show:

Done. Total amount of entities: 138367
Sorting them in queues for work using UoW
Done. Total time taken by sorter: 103749,8583ms
Insert queue contains 138367 elements
Update queue contains 0 elements

This is on 5.6 but it should be the same on 5.5.1. a 1000 graphs:

Done. Total amount of entities: 12151
Sorting them in queues for work using UoW
Done. Total time taken by sorter: 458,4592ms
Insert queue contains 12151 elements
Update queue contains 0 elements

So this clearly takes a nosedive when there are many more entities, and looking at the code that's fairly obvious: the design is flawed as it assumes it's run once, but in the case of a unit of work, it's not, it's ran as many as there are entities in the uow.

This is done by calling uow.ConstructSaveProcessQueues(), which doesn't take into account the batching parameter, as the performance is lost in the sorting based on types. I think if you use a profiler on your code you'll see the same.

Looking into fixing this a.s.a.p. This will require some time however as it requires refactoring of some code. So not sure if I have an updated runtime today.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Feb-2019 14:38:54   

So a scenario that wasn't considered when benchmarking eh? sunglasses

Only kidding smile - if you can fix it then great but otherwise a simple warning about many entities in the UOW2 might actually reduce performance would be sufficient.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 14:57:30   

The one case we didn't consider indeed wink

It turns out to be easy to fix without a breaking API change, I think (as the methods on ObjectGraphUtils are public) The main issue is that it sorts the insertqueue in progress every time a new subgraph is created. All over. So the more entities in that insert queue, the slower it gets. It of course only has to sort that queue once, not every time. The key is to keep the adjacency lists for the type dependencies, and only sort the queue at the end, using all built adjacency lists of the types (so we know which type depends on what in the queue at hand).

Hopefully a fix later today.

(edit)

Done. Total amount of entities: 138367
Sorting them in queues for work using UoW
Done. Total time taken by sorter: 1528,9423ms
Insert queue contains 138367 elements
Update queue contains 0 elements

That's more like it (v5.5.2, in VM. Previously it was a LOT more). Have to run all tests tho, but looking good.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Feb-2019 15:44:17   

Please use the updated hotfix build for 5.5.2 to fix this (which also contains the template fix). The hotfix is also on nuget (you have to enable hotfix/prerelease builds in the nuget UIs for vs/rider) and should be available shortly.

Frans Bouma | Lead developer LLBLGen Pro