Discussions

General J2EE: JDBC Batch Updates vs Stored Procedures

Bookmark and Share Get thread feed

  1. JDBC Batch Updates vs Stored Procedures (7 messages)

    • Posted by: siva raman
    • Posted on: December 27 2005 08:12 EST
    1. In a given transaction, if I need to insert/update few records (say around 5 records) in different tables (say around 5 tables), can someone tell me which is the best approach to follow: JDBC batch updates or stored procedures?

    2. We are going to use Hibernate3.0 in our project. In that case which is the prefered solution: Using JDBC batch updates through Hibernate or using stored procedures through Hibernate?

    Thanks
    Sivaraman
    Reply to this

    Threaded Messages (7)

  2. JDBC Batch Updates vs Stored Procedures[ Go to top ]

    Stored Procs.......!!!!!!
    Reply to this Reply to original

  3. JDBC Batch Updates vs Stored Procedures[ Go to top ]

    ok. Thanks for your suggestion. Can you please tell what is the advantage in using stored procedures? Won't JDBC Batch updates give the same/equivalent performance as Stored procedures?
    Reply to this Reply to original

  4. JDBC Batch Updates vs Stored Procedures[ Go to top ]

    I'll prefer JDBC updates 'cos stored procedures have different syntaxes for different databases. Also, some databases do not support SPs at all.
    Reply to this Reply to original

  5. JDBC Batch Updates vs Stored Procedures[ Go to top ]

    In my current project i use hibernate 3.0. But I use stored procedures in ORacle for certain data intense tasks. For example a certain use case requires making copies of existing data and this would involve potentially thousands of records. Perfect for stored procs and a definite no-no for any type of ORM tool.

    In your case u have only 5 records..so its not a data intensive task. Secondly I am guessing you have relations between these tables in which case hibernate may be the better choice. Then again if these 5 inserts/updates happen very very often then there can be a case for jdbc or stored procs.

    not sure i answered anything... see these questions are very dependent on the project + use case. Only u can make the call.
    Reply to this Reply to original

  6. Complex SPs and loose-coupling to DB[ Go to top ]

    We too have Hibernate 3.0 an Oracle 9i Database and many (fairly complex) Stored Procedures. We need the stored procedures for performance reasons but we also want to reduce our dependecies on the database (or at least confine required changes from a database change to a well-defined and limited number of places). Has anyone any suggestions for this? I don't think that JDBC batch updates will be useful because they are just for simple updates and can't provide additional logic (but I may well be wrong).

    TIA - Brian
    In my current project i use hibernate 3.0. But I use stored procedures in ORacle for certain data intense tasks. For example a certain use case requires making copies of existing data and this would involve potentially thousands of records. Perfect for stored procs and a definite no-no for any type of ORM tool.In your case u have only 5 records..so its not a data intensive task. Secondly I am guessing you have relations between these tables in which case hibernate may be the better choice. Then again if these 5 inserts/updates happen very very often then there can be a case for jdbc or stored procs.not sure i answered anything... see these questions are very dependent on the project + use case. Only u can make the call.
    Reply to this Reply to original

  7. JDBC Batch Updates vs Stored Procedures[ Go to top ]

    1. I think that stored procedures would be faster since stored procedures are precompiled at the database end whereas queries need to be compiled if they are not being cached. Also stored procedures are helpful when your INSERT queries depends upon other tables.

    2. Again Hibernate too uses JDBC to query the database.
    So stored procedures are a better approach.

    3. The only advantage of Batch updates is that multiple JDBC statements are executed in a single JDBC call.

    Please tell me if I am wrong.

    Regards,
    Abhishek Bhargava
    Reply to this Reply to original

  8. JDBC Batch Updates vs Stored Procedures[ Go to top ]

    1. In a given transaction, if I need to insert/update few records (say around 5 records) in different tables (say around 5 tables), can someone tell me which is the best approach to follow: JDBC batch updates or stored procedures?2. We are going to use Hibernate3.0 in our project. In that case which is the prefered solution: Using JDBC batch updates through Hibernate or using stored procedures through Hibernate?ThanksSivaraman

    Probably too late for your project but why not prototype both and see which one works better for your data/environment. Many people jump toward using stored procedures with the assumption that they'll perform better (and they may). The question is how do they perform under load.

    If high load isn't a problem then who cares which one performs sightly better? I'd go for the JDBC for easier maintenance (assuming your team is stronger in java than plsql).
    Reply to this Reply to original