Hello,

We have just started using the LLBLGen Pro demo, and are very impressed ... we'll almost surely buy the full version. However, we currently have 1 small problem: performance of UPDATEs.

Our test app UPDATEs 2000 rows (out of 2000). We can select these rows into an EntityCollection in <0.2 seconds, but when we loop through the EntityCollection and run a SaveEntity on each Entity, the 2000 updates take ~20 seconds (40 seconds when using transactions and only 1 commit at the end). We're only updating 2 columns - here's the code that's causing the problem:

//ecIL is an EntityCollection of InvoiceLineEntities foreach (InvoiceLineEntity delIL in ecIL) { delIL.Lineamt = 293.25M; //M casts to decimal delIL.Rate = 22.0M; adapter.SaveEntity(delIL); }

Is there a better way to perform updates on many rows? In a real world app, each row would have different values so we couldn't use straight SQL or stored procs.

Thanks, Josh Lindenmuth

Also,

We tried running the same 2000 updates via non-parameterized queries via sqlcmd. 2000 distinct updates (different values in the 2 columns) were placed in a flat file and fed to sqlcmd. I'd think this would be one of the slowest ways to update the database, but the 2000 updates only took ~2 seconds (vs. 20-40 using the method I describe in my first message).

Thanks, Josh Lindenmuth

Try saving just the entity collection. It should be much faster. So pass your entity collection into the save function, not each individual entity. The save function will take care of looping through each one

That should update them all in one query, as opposed to making 2000 calls to the DB.

Answer wrote:

That should update them all in one query, as opposed to making 2000 calls to the DB.

It will still make 2000 calls to the DB, but it will keep the same open connection for the duration of the transaction which will be faster.

I did ask Frans in the past whether these sort of operations could be batched to the DB as a single call... maybe in V 2.0 (hint hint)

Your code becomes:

//ecIL is an EntityCollection of InvoiceLineEntities
foreach (InvoiceLineEntity delIL in ecIL)
{
delIL.Lineamt = 293.25M; //M casts to decimal
delIL.Rate = 22.0M;
}
adapter.SaveEntityCollection(ecIL);

Thanks for the compliments, Josh And thanks for chiming in, guys

Batching the statements is currently not done as not every database supports it and there's no code to first generate the queries and then push them down to the db at once. Changed entities are examined as well as their related entities, unless you specify you don't want to recurse through the graph, i.e. you specify false for recurse in the SaveEntity overload. As you save the entity each time recursive, it might be that it traverses the complete graph each time.

As said above, simply pass ecIL to SaveEntityCollection() and it should be more efficient. Also, you can try to start a transaction on the adapter before the loop, then in the loop, use adapter.SaveEntity(delIL, false, null, false); instead.

Or, use 1 save after the loop: adapter.SaveEntityCollection(ecIL, false, false);

LLBLGen Pro doesn't cache generated SQL. This is because caching a generated query is pretty hard, i.e.: it is hard to find back a query based on changed entities. Therefore the query is generated each time an entity is about to be saved.

There is also adapter.UpdateEntitiesDirectly to update all entities at once (of course this work only if new values are the same for all entities)

It will still make 2000 calls to the DB, but it will keep the same open connection for the duration of the transaction which will be faster.

I would have sworn i asked frans this before i purchased and it was done in one batch...Good thing i didnt bet money on it

I just thought the driver for that db was smart enough to batch them. Oh well.

Answer wrote:

It will still make 2000 calls to the DB, but it will keep the same open connection for the duration of the transaction which will be faster.

I would have sworn i asked frans this before i purchased and it was done in one batch...Good thing i didnt bet money on it

I just thought the driver for that db was smart enough to batch them. Oh well.

Heh No nothing is batched, except identity fetch queries WHEN it is possible. Batching is not always possible, for example ODP.NET 10g, firebird, ms access, don't support batching.

ok actually i am here to ask about batching cuz i have so many updates in different tables in one method different times

so is there a way to get the updates of every object as sepparate scripts concatinate'em and send'em just once to the database only once ?

Sorry but batching is not supported.

Otis wrote:

As said above, simply pass ecIL to SaveEntityCollection() and it should be more efficient. Also, you can try to start a transaction on the adapter before the loop, then in the loop, use adapter.SaveEntity(delIL, false, null, false); instead.

Frans,

Have you thought about caching the persistenceInfo object in the SaveEntityCollection method? As normally this method is used to save a lot of instances of the same entity, then passing the entity's persistenceinfo object to an overloaded version of the method that you are using to save the entity. As the collection can contains different kind of entities, you can use a hashtable to keep track of the persisteninfo by entities.

Rogelio wrote:

Otis wrote:

As said above, simply pass ecIL to SaveEntityCollection() and it should be more efficient. Also, you can try to start a transaction on the adapter before the loop, then in the loop, use adapter.SaveEntity(delIL, false, null, false); instead.

Frans,

Have you thought about caching the persistenceInfo object in the SaveEntityCollection method? As normally this method is used to save a lot of instances of the same entity, then passing the entity's persistenceinfo object to an overloaded version of the method that you are using to save the entity. As the collection can contains different kind of entities, you can use a hashtable to keep track of the persisteninfo by entities.

Although the idea seems nice, adding a lookup structure to find back cached objects is precisely what the provider does .

Nevertheless, in v2, the persistence infos are stored in a new structure which uses hashtables (or dictionaries in .NET 2.0) to quickly retrieve the objects for a given entity or field. These are all cached, nothing is re-created at runtime. To cache them again in a savecollection routine would not help as it too would use the same internal structures as the persistenceinfo provider uses

Frans,

Thank you, I am waiting for V2.0.

Rogelio wrote:

Frans,

Thank you, I am waiting for V2.0.

Well, you're not alone, and I am so looking forward to the day when I can say "DONE!", but still some work has to be done before beta can be started, though the list is getting shorter and shorter