Menu
If you are using php5 and mysql5, is there a substantial advantage to using stored procs over prepared statements? ( i read somewhere you may not get substantial performance gains from mysql5 stored proc)
687
The difference is you cant store prepared statements. You must "prepare" them every time you need to execute one. Stored procedures, on the other hand, can be stored, associated to a schema, but you need to know PL/SQL to write them. You must check if your DBMS supports them.
Calling stored procedures in JDBC applications The Informix JDBC driver provides the Statement , PreparedStatement , and CallableStatement methods, which can be used to execute stored procedures.
You should always prefer working with prepared statements for the security benefits. They all but eliminate vulnerability to SQL injection, without you having to worry about SQL-escaping values. If you have a query that doesn't run often, though (less than once per request), a prepared statement can take longer to run.
The Key Difference between Statement and PreparedStatement is that Statement is used for executing simple SQL Statements whereas PreparedStatement is used for executing dynamic and pre-compiled SQL Statements.
A stored procedure (SP) is a server-side procedure written in SQL, which is stored in the database and is directly accessible by the database engine. It is also exposed to client applications via programming language connections. Server-side stored procedures offer many advantages over typical client side logic processing.
Statement : It is used for accessing your database. Statement interface cannot accept parameters and useful when you are using static SQL statements at runtime. If you want to run SQL query only once then this interface is preferred over PreparedStatement. 2. PreparedStatement : It is used when you want to use SQL statements many times.
Prepared statements are queries written with placeholders instead of actual values. You write the query and it is compiled just once by the DBMS, and then you just pass values to place into the placeholders. The advantage of using prepared statements is that you enhance the performance considerably, and protect your applications from SQL Injection.
The stored procedure can never do anything that you didn't tell it to do, at an earlier time. The worst thing to happen is that someone trying to exploit the server manages to insert some garbage as his username or such. Who cares. So, you get much better security, and the server needs to do less work (i.e. queries run faster), too.
They are not really the same thing - with stored procedures, your database logic resides inside the database. Prepared statements basically avoid re-parsing queries if they are called multiple times - the performance benefit can vary greatly.
The choice to use one or the other is really dependent on your specific situation. I don't really use stored procs anymore as I like having all of my logic in one place.
81
Stored procedures make sense for professional-grade (IE enterprise-grade) applications where you:
There are other reasons.
Prepared statements are better for work done within a session. But if you are taking the time to create a prepared statement, you have essentially done everything necessary to create a stored procedure. The difference is that the stored procedure is available across multiple sessions (subject to GRANTS in the database).
What I can not figure out is that if you have the option for stored proc vs. prepared statement, why you would bother with prepared statements. Most of the SP vs PS discussions seem to focus on what the differences are between them, not on why to use one vs the other. That always appears to boil down to "depends on what you are trying to do." But I haven't seen a well organized description of: use a proc if you need VS use a statement if you need....
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
