1

I come from c++/SQL server background and new to web development. I have couple of newbie questions.

a. To me it makes sense to use stored procedures via mysqli rather than calling direct queries in my PHP code. Advantage is I can go lots of internal calculations in stored procedure and return desired results. Also, I can modify stored procedure later without changing PHP code if I want to change it. But it doesn't seem to be used that often in web development world. I don't understand why? Does it affect caching or something. Just puzzled.

Raj
  • 22,346
  • 14
  • 99
  • 142
user1536361
  • 11
  • 3
  • See http://stackoverflow.com/questions/462978/when-should-you-use-stored-procedures - this and the related question it duplicates present the reasons you might be looking for. As for me, I'd consider that, if I were going to make direct calls to the database, stored procs would be essential as they are another barrier between the world, my code, my database and SQL Injection, for example. – dash Jul 18 '12 at 23:33
  • Thanks for your comment. For me my only concern was whether there was any drawback in terms of caching. From my understanding, mysqli caches queries. So, if I use stored proc does it affect caching or slow performance? – user1536361 Jul 19 '12 at 00:00
  • There are also some useful answers here: http://stackoverflow.com/questions/1151255/mysql-store-procedure-performance The point Is that there are plenty of good reasons to use stored procs if you are doing your own DAO - the performance gains with MySql may not be massive, but not embedding lots of SQL in php is almost certainly a good thing, for example. – dash Jul 19 '12 at 00:18
  • thanks. I checked out your links. I only see positive about it as expected. The thing I am puzzled is that how come I don't see use of it much in lots of .php code I have come across. Eg. Consider, on site you can submit vote. So, internally say we maintain two tables with that vote. So, what I see in most common approach is that developer would run two 2 separate queries from .php code rather that setting up stored proc and running that once. So, you make only one trip to database. Is it just poor programming practice? I thought most performace gain is by reducing the number of trips you make – user1536361 Jul 19 '12 at 00:46
  • Sometimes, it's about nothing more than what the developer chooses to do and there isn't a `good` reason for it. However, some developers might put as much of their SQL as possible in the code because they have limited access to the database. – dash Jul 19 '12 at 15:54

0 Answers0