Optimizing Deleting

Thread is closed for posting
2 posts, 0 answers
  1. Fakhru
    Fakhru avatar
    37 posts
    Member since:
    Oct 2007

    Posted 29 Aug 2009 Link to this post

    public class Categories
       IList<Products> products;
       public IList Products
          get { return products; }

    If the products has 1000 items, then when I want to remove all Category.products, then OpenAccess will send 1000+ sql query to delete all product.

    Maybe you should consider simple sql like DELETE FROM Product p WHERE p.category_id=? when user call category.Products.Remove(), because we want to delete all collections.
  2. PetarP
    PetarP avatar
    754 posts

    Posted 31 Aug 2009 Link to this post

    Hi Fakhru,

    If you pass a list of persistent objects to the remove method of the IObjectScope, a temporary stored procedure will be created and will be called for the members of the list. This will greatly improve your performance. If you, however, would like to use the approach you mentioned you can quite easily implement it by yourself. What you would need to do is create a simple stored procedure that takes only 1 parameter (the id on which your remove logic will be based) and reverse map it to a static method. Implement a method Clear() in your class that will clear your list and execute the stored procedure for deleting the elements. For example:
    public void Clear() 
    In the stored procedure DeleteOrderDetails you can have any logic you find suitable for your scenario. This way you can guarantee that minimal calls are made to your SQL server.

    Best wishes,
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Back to Top