2

I'm working on gamification of a web application and I'm going to use points juste like reputation on StackOverflow. The environment is LAMP and I have to :

  • Increment user points for every upvote.
  • Decrement user points for every downvote.
  • Keep track of user points by day.
  • Keep track of user points by the category where he posted and by day.

So I need to make inserts and updates in different MySQL tables for every upvote and downvote.

What's the most efficient way to do it ? using a set of PHP functions (in a transaction), a single MySQL stored procedure or by a different mean ?

Monsieur Light
  • 46
  • 5
  • @MateiMihai "too many joins" !!! Care to elaborate, or maybe amend that remark! – Strawberry Apr 26 '14 at 13:16
  • Ooh, interesting question. Some may regard this as too opinion-based, but I think views can be given technical backing, so I'll not vote to close. +1 – halfer Apr 26 '14 at 13:22
  • Ah, but [it is a duplicate](http://stackoverflow.com/questions/3419604/mysql-stored-procedures-or-php-code) though! – halfer Apr 26 '14 at 13:23
  • isn't it a more specific case ? I need the click on upvote or downvote to be smooth so the code has to be efficient. – Monsieur Light Apr 26 '14 at 13:31

3 Answers3

2

I recommend you to read this Who Needs Stored Procedures ..and i quote this

Having used both stored procedures and dynamic SQL extensively I definitely prefer the latter: easier to manage, better encapsulation, no BL in the data access layer, greater flexibility and much more. Virtually every major open-source PHP project uses dynamic SQL over stored procs (see: Drupal, Wordpress, Magento and many more).

from @leepowers (MySQL stored procedures or php code?)

Community
  • 1
  • 1
stackrocha
  • 405
  • 2
  • 8
  • 1
    Thanks for pointing out the duplicate. In general if you spot a duplicate question, just add a link under the question, and a higher-rep user will consider a dup-close vote. – halfer Apr 26 '14 at 13:24
  • @halfer, i answered because i wanted to give my opinion also, but thanks for the advice, next time i'll do that! – stackrocha Apr 26 '14 at 13:27
2

Advantages of Using Stored Procedures

Stored procedures are so popular and have become so widely used and therefore expected of Relational Database Management Systems (RDBMS) that even MySQL finally caved to developer peer pressure and added the ability to utilize stored procedures to their very popular open source database. The list below details why stored procedures have gained such a stalwart following among application developers (and even Database Administrators for that matter):

  • Maintainability Because scripts are in one location, updates and tracking of dependencies based on schema changes becomes easier
  • Testing Can be tested independent of the application
  • Isolation of Business Rules Having Stored Procedures in one location means that there's no confusion of having business rules spread over potentially disparate code files in the application
  • Speed / Optimization Stored procedures are cached on the server Execution plans for the process are easily reviewable without having to run the application
  • Utilization of Set-based Processing The power of SQL is its ability to quickly and efficiently perform set-based processing on large amounts of data; the coding equivalent is usually iterative looping, which is generally much slower
  • Security Limit direct access to tables via defined roles in the database Provide an "interface" to the underlying data structure so that all implementation and even the data itself is shielded. Securing just the data and the code that accesses it is easier than applying that security within the application code itself

Drawbacks to Using Stored Procedures

There are certainly drawbacks to Stored Procedures that preclude them from being the one-stop shop solution to application database access. The list below contains some reasons why Stored Procedures might not be right for your application solution. Interestingly, you'll probably recognize some headings that also appear in the "Advantages" section above; this is because what one developer views as affirmative evidence for their use might cause another to see the same evidence to disprove their viability as a solution.

  • Limited Coding Functionality Stored procedure code is not as robust as app code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)
  • Portability Complex Stored Procedures that utilize complex, core functionality of the RDBMS used for their creation will not always port to upgraded versions of the same database. This is especially true if moving from one database type (Oracle) to another (MS SQL Server).
  • Testing Any data errors in handling Stored Procedures are not generated until runtime
  • Location of Business Rules Since SP's are not as easily grouped/encapsulated together in single files, this also means that business rules are spread throughout different Stored Procedures. App code architecture helps to ensure that business rules are encapsulated in single objects. There is a general opinion that business rules / logic should not be housed in the data tier
  • Utilization of Set-based Processing Too much overhead is incurred from maintaining Stored Procedures that are not complex enough. As a result, the general consensus is that simple SELECT statements should not be bound to Stored Procedures and instead implemented as inline SQL.
  • Cost Depending on your corporate structure and separation of concern for development, there is the potential that Stored Procedure development could potentially require a dedicated database developer. Some businesses will not allow developers access to the database at all, requiring instead a separate DBA. This will automatically incur added cost. Some companies believe (and sometimes it's true, but not always) that a DBA is more of a SQL expert than an application developer, and therefore will write better Stored Procedures. In that case, an extra developer in the form of a DBA is required.

Alternatives to Stored Procedures

Because Stored Procedures are not always the perfect solution nor do they satisfy all the needs of all developers, other solutions exist that attempt to provide most of what a developer wants to do when accessing a database backend. These include:

  • In-line or Parameterized Queries These are written within the application code itself
  • Object Relational Mapping (ORM) Provides an abstraction to the database without having to manually write data access classes. At this point, most all major platforms offer some form of ORM software, as illustrated at this site.

When Should You Use Stored Procedures?

Stored Procedures may not always be the right answer for processing data, but there's also not enough compelling evidence to not use them either. Whether or not to use them determines on your particular situation and ability to develop the Stored Procedure(s) to match. Just like with writing a good, quality application, if you or your developers can write good, quality Stored Procedures, then by all means implement them. If they can't, then another solution might be best for you.

by PJ Hambrick Advantages and Drawbacks of Using Stored Procedures for Processing Data

user3565425
  • 41
  • 4
0

I would recommend you to use php functions so you can leave the load of preprocessing on the web server and not overload the DB server.

perodriguezl
  • 430
  • 3
  • 13