Remove a lot of rows in a database

Discussions

EJB design: Remove a lot of rows in a database

  1. Remove a lot of rows in a database (9 messages)

    Hi,

    I'm developing an application that, in the future, will have a table in the database with a lot of rows (maybe more than 1.000 and more) and I need to make an method that deletes all the rows. I have used the method remove of the Entity, but I must retrieve each row to remove it and if there are a lot of rows it will take so much time ... Which is the best solution for it?


    Regards,

    Poli

    Threaded Messages (9)

  2. Remove a lot of rows in a database[ Go to top ]

    Here u need to create the instance of Key class for each
    unique record in table. U need to iterate through each record.

    I will suggest this code to remove record via iteration.

    here uniqueId is primary key filed of row to be deleted.

    ketClass keyId = new keyClass(uniqueId);

    then use...
    ejbRemote remote= ejbHome.findByPrimaryKey(keyId);
    remote.remove();

    But for iteration u need result set or vector that holds all record from table.


    i think this will help u.



  3. Remove a lot of rows in a database[ Go to top ]

    Hi, Santosh,

    perhaps I didn't explain me very well. My problem is that I have two Entity Beans corresponding to two tables of a database, Shops and Products, and I want to make a method that deletes a shop and all the products of the shop. The primary key of the shops is the shopId and the primary key of the products is the shopId and the productId, if I use the remove method of the Entity Products, I must retrieve each element with its primary key. I wonder if it will be better that I use a Session Bean that implements a query SQL:
    delete from Products where ShopId = xxx;

    that to use the method remove of the Entity Product.


    Regards,

    Poli


    Regards,

    Poli
  4. Hi Poli,
           You need to do "ON DELETE CASCADE" operation. This can be set either as constarint/trigger/some kind of setting in the DBMS. See whether creating constraints help you. If it is then while creating the tables for the two entity beans - shop and product, set this constraint.
           If it is a BMP, then even if your database doesn't support that, you can achieve this by first using an iterative remove on the 'products' of the desired shopID and then a remove on 'shop' of the shopID. Keep the two actions under a high-level transaction.

    Please reply your opinion and the feasibility of my approach.

    Regards,
    Vamsi Kalyan Poondla
  5. Iterating through lot's of entity bean and calling remove() for each and every one of them will have an impact on performance, especially if it's 1000 rows of information.

    I would use a Session bean and create a method to delete a Shop and all its corresponding Products associated with it. I forsee a problem with this though: what will happen to all the entity beans in the app server cache when they're not supposed to exist any more.

    Benedict Chng
  6. Hi, Vamsi,

    that's what I want to do, Delete Cascade, but I don't know how to implement it; furthermore, I have more tables and I have to do some variants of the Delete Cascade. I've implemented a Session Bean which is the client of the Entity Beans. In the Session I've implemented a method that deletes all the products of the shop first, and then deletes the shop, but my problem is that it can take too much time.

    Regards,

    Poli
  7. Remove a lot of rows in a database[ Go to top ]

    Maybe you can also write to Procedure at the Backend where you can pass the ShopId and the procedure will delete all the enteries in the database corr. to the Product table.
    You can use callable statements for that.
    Also do set the DELETE CASCADE constaint ON
  8. Remove a lot of rows in a database[ Go to top ]

    Maybe you can also write to Procedure at the Backend where you can pass the ShopId and the procedure will delete all the enteries in the database corr. to the Product table.
    You can use callable statements for that.
    Also do set the DELETE CASCADE constaint ON.

    One more thing if you are deleting in two SQL's the record make sure to delete first the Product records and then the Shop Table records.
    And Insert in the reverse way like Insert Shop first and then Product.
  9. Remove a lot of rows in a database[ Go to top ]

    I've seen alot of people recommend "delete on cascade" and stored procedures to handle this.

    I'd recommend against that. It works well enough, but it becomes hidden logic that other developers can't see.

    For clarity (and performance), business logic should be kept out of the database, but using cascading deletes and stored procedures is business logic in the database. Remember, the database is the least-scalable resource you have. Middle tier apps are much better at scaling.

    Three possible approaches I would suggest you consider are:

    1) Use a session bean to delete (as you suggested). Probably more efficient, but adds another bean that manages a table, and is a bit messy. What happens if someone is modifying a product (via an entity bean) when the session bean does a delete? Sort of yanks the carpet out from underneath the entity bean.

    2) Bite the bullet and use entity beans. Create an ejbFindByShopID(), which will return all the products associated with that shop, and call remove() on them one by one.

    How often do you plan to remove a shop and all products? If not very often, avoid ugliness and accept a minor performance hit once in a while.

    3) Products and the shop are tightly coupled, as can be shown by your choice of a composite primary key for the products table (shopID and productID). Why not treat all products related to a shop as a single row (even though there are multiple in the database). Use the shop id as the defacto primary key.

    Your bean, rather than handling a single row, would handle all products related to a shop. It would handle a container object (like a list). The downside (and it's a big one) is that you get/set all products at once. Maybe you only want to get/set one. But sometimes it is an all or nothing proposition. Our application, financial in nature, sends a loan application to a remote bank. We sometimes get errors back. We want to save all errors at once, show all errors at once, and remove all errors at once. This pattern works for us in this situation.

    Anyway, just my thoughts.

    David.
  10. If you are using BMP, you can issue a SQL statement that delete all products inside the remove method of the shop object.

    You should not worry about having entity beans that will still refer to deleted products since ejbload or findByPrimaryKey will retreive no record thus no object to work with.