This is a migrated thread and some comments may be shown as answers.

Batch updates and deletes in OpenAccess?

9 Answers 273 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Kendall Bennett
Top achievements
Rank 2
Kendall Bennett asked on 17 Jul 2011, 03:57 AM
Is there any way to support batched updates in OpenAcces, when submitting changes for lots of entities? For instance if I create an invoice, and an number of products attached to it at the model level, and then submit that to the database. It is going to generate a bunch of inserts, but because our primary keys are generated by the database (AUTOINC), it has to read them back from the database which is slow when you are inserting a lot of data.

Your competitors product, Mindscape Lightspeed (which I am evaluating) supports the ability to do batch updates by allowing you to change the identity key generation for new entities to something that can be computed at the ORM level, and does not need a round trip to the database to get the inserted identify keys back:

http://www.mindscapehq.com/documentation/lightspeed/Controlling-the-Database-Mapping/Identity-Generation

Is there any support for something similar to this in OpenAccess, so that we would be able to support batched updates and deletes in OpenAccess? Or should I just drop to a stored procedure or native SQL through the new ADO.NET API to do this?

9 Answers, 1 is accepted

Sort by
0
Ady
Telerik team
answered on 19 Jul 2011, 02:21 PM
Hi Kendall Bennett,

 OpenAccess performs bulk deletes whenever possible via a 'DELETE FROM tablename WHERE pkcol IN (...)'  query. Regarding the bulk inserts - this is something we are looking into so that we can insert a bunch of new rows in a single strip to the database and not 1 insert statement for each new object,

If your database has AUOTINC columns there is really no way to bulk insert rows since the newly generated id needs to be obtained for each row added.
You could resort to a stored procedure to perform the bulk insert and then use a new context instance so that all these newly added objects are wired up appropriately.

Btw, I did have a look at the Mindscape documentation and they too suggest to avoid using 'autoinc' columns if you want to perform bulk inserts.


Best wishes,
Ady
the Telerik team

Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

0
Kendall Bennett
Top achievements
Rank 2
answered on 19 Jul 2011, 03:56 PM
The core problem with the delete functionality as it exists with OpenAccess today, is that if you need to bulk delete a bunch of items from the database, you need to first load those entities from the database so the primary keys can be loaded. Once OpenAccess knows all the primary keys to be deleted, it could batch those up, but that won't help with the issue of all the selects that will occur in the process of loading the entities from the database so they can be deleted.

The solution to the problem is to have a Remove<TEntity> function that will accept a where clause for the entity in question, and then using that construct a SQL statement along the lines of:

delete from table where some_value = 'a' && some_other_value = 'b'

Then all you need to do is make sure ClearChanges() is called, so that the identity map is cleared for existing entities that might be loaded, but the delete would be a lot faster if you are deleting lots of entities.

It would also be nice to be able to do bulk updates using a where clause as well, without having to resort to SQL (once again a ClearChanges() would be needed).

As for bulk inserts, you cannot perform bulk inserts into the database if you care about the resulting primary keys for the entity and the primary key is an AUTOINC primary key, unless you implement something like a KeyTable (great idea though). However if you don't care about the resulting entities being tracked after the insert, then it is possible to batch them all up. We do that right now using custom code on top of the ADO.NET layer to build up bulk insert SQL, and I will probably just port that over to run on top of the new low level ADO.NET API. But it would be nice if this was handled in the API directly.
0
Ady
Telerik team
answered on 22 Jul 2011, 03:10 PM
Hello Kendall Bennett,

 We will deliberate over your suggestions about the bulk delete and updates.
Regarding the KeyTable option for inserts, there is already a key generator mechanism - HIGHLOW (the default key generator mechanism) , that use a 'voa_keygen' table to generate identity values in a bunch and then uses these values for inserts. You can specify the key generator by selecting a domain class in the designer and pressing F4 to open the Propeties window. The 'Identity Mechanism' property lists various key generators available.

Do get back in case you need further assistance.

Greetings,
Ady
the Telerik team

Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

0
Patrice Boissonneault
Top achievements
Rank 2
answered on 26 Aug 2011, 04:06 PM
+1 for this feature, this is a much needed feature to delete or update multiple records in big tables.

Please consider adding this (unless this was added since the last post to this forum?)

Regarding "OpenAccess performs bulk deletes whenever possible via a 'DELETE FROM tablename WHERE pkcol IN (...)'  query.", can you give an example when this would be used?

Thanks.
0
Jan Blessenohl
Telerik team
answered on 26 Aug 2011, 04:22 PM
Hello Patrice Boissonneault,
Batch operation and optimizing the insert and update statements are two different things. We will work on reducing the amount of C/S calls for similar inserts and update, we already do it for deletes.
To have a query language that includes data modification statements is a complete different story. We have no idea what is changed in the database and we have no chance to clean our caches.

Kind regards,
Jan Blessenohl
the Telerik team

Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

0
XXXX
Top achievements
Rank 1
answered on 02 Sep 2011, 05:59 PM
It's good that the code behind the scene is optimized but what I would like to know is how to clear a table in the simplest way.
Is there anything like the GetAll<T>  in the OpenAccessContext  that will clear a table like RemoveAll<T>.

Yours
   Björn
0
Jan Blessenohl
Telerik team
answered on 05 Sep 2011, 03:07 PM
Hi Bjössi,
You have to use  SQL to achieve this. Please add a partial class to your generated context and add a method like:
public partial class EntitiesModel
{
    public void BatchDeleteAndCommit<T>()
    {
        string tablename = Metadata.PersistentTypes.Where(x=>x.FullName == typeof(T).FullName).Single().Table.Name;
        this.ExecuteNonQuery("delete from " + tablename);
        this.SaveChanges();
        this.GetScope().Database.Cache.EvictAll(typeof(T), true); // 2nd level cache
    }
}
This will delete your data and clean the cache.

All the best,
Jan Blessenohl
the Telerik team

Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

0
Patrice Boissonneault
Top achievements
Rank 1
answered on 29 Sep 2011, 02:38 AM
Hi Jan,

Will the Evict also clean query cache that may have this data too?

Thanks.
0
Jan Blessenohl
Telerik team
answered on 30 Sep 2011, 09:20 AM
Hello Patrice Boissonneault,
Yes it will, whenever we evict an object of a type that is part of a query definition, the results are evicted.

All the best,
Jan Blessenohl
the Telerik team

Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

Tags
General Discussions
Asked by
Kendall Bennett
Top achievements
Rank 2
Answers by
Ady
Telerik team
Kendall Bennett
Top achievements
Rank 2
Patrice Boissonneault
Top achievements
Rank 2
Jan Blessenohl
Telerik team
XXXX
Top achievements
Rank 1
Patrice Boissonneault
Top achievements
Rank 1
Share this question
or