EJB programming & troubleshooting: Stored Procedures vs. Entitiy Beans

  1. Stored Procedures vs. Entitiy Beans (3 messages)

    I would like to get the input from the members of this forum on the advatages/disadvantages to using stored procedures over Entity Beans (especially 1.1, but also in general).
    My experience may not be as deep as some of yours, which is why I am asking here, but it seems to me that stored procedures, while not a "Java" solution, do seem to be a wise choice in many instances. My reasons for saying so are as follows:
    1. In the quest to truly seperate the Data layer from the logic layer, I have to ask if stored procedures are not the best realization of this goal. You leave all of the database processing to the database server, and make calls to it from your application.
    2. It seems that most people agree that using stored procedures results in better performance.
    3. Granted, using stored procedures does defenitely tie you to the database, but in theory, you could still access data from multiple systems by using a simplified version of the DAO pattern. (Hope that makes sense...)Also, when you are optimizing performance, you usually take advantage of vendor specific optimization techniques anyways.

    I am looking forward to your input!

    Ray Grieselhuber
  2. It depends on your application. I have used stored procedures and called them from stateless session beans under the following scenario

    - When there are too many roundtrips to the database for some data processing logic

    - To improve performance after all other means of improving performance have been explored

  3. Thank you for your response.
    Let me ask this -- why would this only be considered after all other options for optimization have been explored? To be realistic, most custom applications/implementations are being built for a specific environment, so it's not always a bad thing to depend on an underlying database system if you can boost performance a great deal. Furthermore, if you structure the application correctly, it isn't so difficult to migrate the application to other platforms any way. So why would it not be among the top choices in a design decision?
  4. I have experience of this issue on two projects now...
    You are right stored procedures called directly from session beans allow a very efficient mechanism for data access and manipulation... however:

    1: Once you take this path to the 'Dark Side' (unless you are very careful) you give up the chance to use Entity beans at all. If you try and mix the two methods of data access you find the the data integrity is compromised.. as the EJB spec allows the container to decide when it should call EJBStore method (during or on the boundary of a transaction) you never know the state of the database at any particular time. Running a stored procedure while a transaction is in progress can therefore give unpredictable results. If all data access is through Entity Beans within transactions this is not the case. It is therefore not possible to mix EJB and stored procedures access within a transaction

    2: We also found problems when stored procedures use the current user for a database connection to limit data access:
    Trying to mix J2EE security data base level ‘user joins’ is very sticky.

    Once one path is taken then that must be followed exclusively. So in my experience stored procedures may be more efficient and easier (for DBAs) to program and understand.. but trying to fit them into a J2EE app can result in nasty little problems.