File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Prepared statemnt vs Stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared statemnt vs Stored procedure" Watch "Prepared statemnt vs Stored procedure" New topic
Author

Prepared statemnt vs Stored procedure

andy kumar
Ranch Hand

Joined: Jun 08, 2009
Posts: 57
Hi,

I have some confusion regarding stored procedure and prepared statements.

I know SP are useful in cases where you have to make changes to a number of tables. In such cases you can group the logic in a SP which results in the business logic being present in the DB. I usually like the BL to be present in my java code hence I am not using SP for this purpose.

I am using the generic DAO pattern for my basic CRUD operations using prepared statements. What I wanted to know is would it make my app faster if i used SP instead of prepared statements for my basic CRUD operations. Because as SP are pre compiled in the DB server they would be much faster then prepared statements AM i right? I am using mysql db.


Thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Probably, since you would be able to take advantage of any database optimisations and shortcuts. Its a common pattern in Windows applications, where the database is always assumes to be SQL Server for example.

However, it has a number of disadvantages:
  • You need to write your business logic in more than one language
  • You have to debug and test your business logic in two environments rather than one
  • You push the load onto the database server and give yourself two points you need to look at if adding any hardware to scale the applciation. In the case of MySQL you may need to deploy MySQL Cluster over and above MySQL
  • The database is often a licenced component where the code frequently runs in an environment that is open source. If you have to scale your database your too you can incurr extra cost (OK it isn't in your case, but would be if you ever migrated)



  • JavaRanch FAQ HowToAskQuestionsOnJavaRanch
    Ranveer K Kumar
    Ranch Hand

    Joined: Sep 13, 2009
    Posts: 64
    andy kumar wrote:Hi,
    if i used SP instead of prepared statements for my basic CRUD operations. Because as SP are pre compiled in the DB server they would be much faster then prepared statements AM i right? I am using mysql db.


    Thanks


    Yes SP will faster specially when the your fetching and comparing data from different tables.
    andy kumar
    Ranch Hand

    Joined: Jun 08, 2009
    Posts: 57
    Thanks for the prompt reply.

    I agree with all the disadvantages that you have mentioned except the first one "You need to write your business logic in more than one language". I am writing the SP for basic CRUD operations(select by PK....etc) which means that I am not putting any business logic there , moreover it is fairly simple to write basic CRUD operations in SP so it is not much of an effort.

    My confusion lies in the fact that I have read some forums and there they say that it does not help to write SP for basic operations like select/delete by PK. They say that prepared statement will work as well as SP. But I fail to understand how?
    For e.g:-
    suppose I have a prepared statement like "select * from table1 where is=?" in one of my DAO impl classes. Now I have 100 clients where each client does some select operations , so in this case there will be 100 prepared statements compiled and executed to give the result. Of course if the client does a select 2 times then statement will just be executed as it has already been compiled the first time.

    Now suppose I have a SP for this simple select query. Then the query will be compiled only once and executed 100 times so that should mean a great improvement in performance.

    Am I correct in my understanding of how the prepared statement and SP are executed by a DB?

    Thanks
    Jan Cumps
    Bartender

    Joined: Dec 20, 2006
    Posts: 2516
        
      10

    so that should mean a great improvement in performance.
    Do you know how much time precompiling takes, compared to the other logic of your program? I think that most programs don't speed up by deciding to switch from DAO to stored procedures for CRUD operations.


    Regards, Jan


    OCUP UML fundamental and ITIL foundation
    youtube channel
    andy kumar
    Ranch Hand

    Joined: Jun 08, 2009
    Posts: 57
    Jan Cumps,

    Thanks for the response. I am clear in my understanding now.
    Thanks to everyone who replied.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Prepared statemnt vs Stored procedure