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

High performance delete?

4 Answers 42 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 30 Jun 2011, 02:16 AM
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?

4 Answers, 1 is accepted

Sort by
0
Jan Blessenohl
Telerik team
answered on 01 Jul 2011, 05:07 PM
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.
0
Kendall Bennett
Top achievements
Rank 2
answered on 01 Jul 2011, 07:13 PM
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.
0
Kendall Bennett
Top achievements
Rank 2
answered on 03 Jul 2011, 07:32 PM
Have you considered adding some kind of way to do this natively in the API, rather than having to drop the SQL for this?
0
Jan Blessenohl
Telerik team
answered on 06 Jul 2011, 08:24 AM
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!

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