Batch updates and deletes in OpenAccess?

10 posts, 0 answers
  1. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 16 Jul 2011 Link to this post

    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?
  2. Ady
    Admin
    Ady avatar
    589 posts

    Posted 19 Jul 2011 Link to this post

    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!

  3. DevCraft banner
  4. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 19 Jul 2011 Link to this post

    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.
  5. Ady
    Admin
    Ady avatar
    589 posts

    Posted 22 Jul 2011 Link to this post

    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!

  6. Patrice Boissonneault
    Patrice Boissonneault avatar
    18 posts
    Member since:
    Mar 2010

    Posted 26 Aug 2011 Link to this post

    +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.
  7. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 26 Aug 2011 Link to this post

    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 >>

  8. XXXX
    XXXX avatar
    57 posts
    Member since:
    Jun 2008

    Posted 02 Sep 2011 Link to this post

    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
  9. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 05 Sep 2011 Link to this post

    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 >>

  10. Patrice Boissonneault
    Patrice Boissonneault avatar
    28 posts
    Member since:
    Nov 2009

    Posted 28 Sep 2011 Link to this post

    Hi Jan,

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

    Thanks.
  11. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 30 Sep 2011 Link to this post

    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 >>

Back to Top
DevCraft banner