High performance delete?

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

    Posted 29 Jun 2011 Link to this post

    I am in the process of trying to see if I can port some Entity Framework code to OpenAccess, so I can see how the performance stacks up. However one thing I cannot yet work out how to do, is do efficient deletes in OpenAccess. The database we are using is MySQL with MYISAM tables, so they don't support server side cascading deletes, and I we don't have stored procedures for this. Rather in the code we delete related entities as necessary using native SQL, by making calls to the Entity Framework functions to issue a native SQL call to the database.

    Is there a way to do something similar with OpenAccess? For instance in one of my classes I have the following:

                uow.ExecuteSqlCommand("delete from " + Tables.Address_Book + " where customers_id = {0}", customerID);

    ExecuteSqlCommand in my UnitOfWork class ends up calling ExecuteStoreCommand() on the ObjectContext from Entity Framework.

    I see there is support for OQL in OpenAccess, but I am not sure if DELETE is an operation supported in OQL? If not, I assume I need to drop to native SQL, so what is the way to write that in OpenAccess?

    Also, once I have issued my native SQL commands, what do I then need to do in order to make sure any entities that might have been loaded in the context are flushed, since they are now deleted from the database? Is there a way to flush anything cached in the OpenAccess context so it will pick up the new data from the database for the next query?
  2. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 01 Jul 2011 Link to this post

    Hi Kendall Bennett,
    We are at the moment working on a new direct SQL API based on the context. It will be available in the Q2 release. Maybe you want to wait for that.

    To cleanup the first level cache, the easiest way is to just call SaveChanges() we take care of reloading the data. To clean the second level cache you can add a method to your context that calls:

    base.GetScope().Database.Cache.EvictAll(typeof(Person),true)

    Kind regards,
    Jan Blessenohl
    the Telerik team
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. DevCraft banner
  4. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 01 Jul 2011 Link to this post

    For the most part I don't need to materialize entities at all, I just need to be able to issue delete operations (although I am thinking of writing MySQL triggers to do this instead to simulate cascading deletes). Is there no support at all for issuing a direct SQL command right now in OpenAccess?

    It sounds like the Q2 release is going to have a lot of things I need, so I might just wait for that.
  5. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 03 Jul 2011 Link to this post

    Have you considered adding some kind of way to do this natively in the API, rather than having to drop the SQL for this?
  6. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 06 Jul 2011 Link to this post

    Hello Kendall Bennett,
    The deletes are already very optimized, we combine them to an 'in' statement to reduce the server round trips. Nevertheless, because our in memory state management is important, you need to load all data before you can delete it. Imagine our IInstanceCallbacks interface where you can fire related deletes based on the objects state or the tracking API where you can hook into the delete process to write log or audit trail information. All this would not work anymore.

    Best wishes,
    Jan Blessenohl
    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!

Back to Top
DevCraft banner