Prepared statemnt vs Stored procedure

[andy kumar]

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]

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)

[Ranveer K Kumar]

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]

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]

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

[andy kumar]

Jan Cumps,

Thanks for the response. I am clear in my understanding now.
Thanks to everyone who replied.