On 5 November 2010 05:21, Tamara Temple <tamouse.lists@xxxxxxxxx> wrote: > > On Nov 4, 2010, at 6:36 AM, Jay Blanchard wrote: > >> [snip] >> If you have a query in your PHP code, which you are going to be >> executing a lot, even if you are using prepared statements, you can go >> one further by creating a stored procedure. Now the SQL server will >> only ever need to compile the statement once. No matter how many times >> it is used. You only need to supply the data which will be type >> appropriate. >> [/snip] >> >> I second this, using stored procedures has a lot of advantages. If you >> need to change your SQL you can do it in one spot. It reinforces MVS or >> modular coding behavior, the SP becomes very re-usable. Security is >> improved. Performance can be improved. You can put the bulk of the data >> handling on the database where it really belongs because SP's can >> perform complex operations complete with control structures. Lastly it >> simplifies your PHP code. >> > > (dangit, i sent this from the wrong address initially) > > I do know about stored procedures and have used them where appropriate > (retrieving the entire contents of a table, one record from a table, etc.). > It was the prepared statements that I haven't had experience with. I wasn't > away that these were precompiled. That does make them more attractive for > heavily executed pulls. On the other hand, the seem to require more intense > maintenance than just changing some lines of code in a file if need be. (I > assume prepared statements don't share the same efficiency of maintenance > that stored procedures do across applications.) > > > Ad hoc queries. These will need to be compiled every single time it is used. If it is in a loop, then every single time the server has to go through the compile process. Prepared statements. These will be compiled every time the prepare statement is called. You can execute the query multiple times, but only the prepare will actually be compiled. Stored procedures. In many regards, these are like prepared statements. Depending upon your DB, you may be able to call a stored procedure directly without the need of building an SQL statement to call the SP. If you have to build a query, then you should build a prepared statement, even if you are only calling it once. The advantage is that passing parameters to stored procedures and prepared statements significantly reduce the potential for SQL injection. But, as mentioned, one of queries don't need to be SP. The main advantage I've found is I can get significant optimization on my SQL server if the server knows about it's workload before it is used. I can optimize my indexes for my actual usage. For more complex select statements, then server side views are another optimization you can do. So, not a server side stored procedure, not a client side prepared statement, but a server side prepared statement. Again, client side you can use a prepared statement to get multiple hits on the view (if that's how you are going to run). Basically, ad hoc queries result in more complex client side code. You have to do all the SQL injection protection yourself (or use a Poka-Yoke - STRONGLY recommend). If you can do all the DB related work on the DB, your client code is simpler. The DB work exists in 1 place. Everyone will know where it is. Using the right tool for the job, AND getting the balance right is what being an experienced developer is all about. Start with what you can understand. Ask questions. Learn. Refactor. Improve. Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
