I am new to architecting a system with a deep level of stored procedures, and have a lot of things I am considering. I’m hoping with this thread we can start some debate about the best practices for using this tool – considering the more current development notions (Ajax style apps particularly) as opposed to traditional arguments.
Perhaps the most public argument against is here, “Stored Procedures are Evil”
http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html… which I find rather silly and limited in its understanding, but a good read to start you thinking in any case.
Some things I am working through (these are just a cursory set of examples):
Processing of Telemetry Data: In my retail applications, I have people watching screens that calculate the total sales volume, numbers etc for the day. This HTML page is auto-refreshed every 60 seconds and is reasonably heavy on processing resources. My thought is to have a new table, Telemetry or something equally clever, and only recalc the total sales numbers for the day when a sale occurs. Since we experience somewhere around 100 sales per day, this means that I’d be doing the update logic 100 times, as opposed to every minute for (at the very least) 3 monitoring systems. Since I’m processing the telemetry data 1430 times in my current configuration (1440 minutes per day *3) then I experience a whopping 92.9% decrease in processing by doing it only at the moment of a sale. On the down side, I burden the purchaser with the processing time of it… but this is measured in milliseconds.
Inventory Updating: This seems to me to be the most perfect example of (why) stored procedures and triggers. In my inventory system, I have a physical quantity number, (PQ), on order (OO), back order (BO), saleable quantity (SQ) and functional quantity (FQ). The PQ is, of course, the number of pieces I physically have in stock. Although I won’t go into the math, the Saleable Quantity is essentially PQ – BO. OO is, of course, how many pieces I am waiting for. The FQ is a tougher number – it shows me the results of a function that calculate the average daily sales for the last 60 days, versus the SQ remaining, versus the OO and the time to delivery to show me if I am “functionally” out of stock already, or how many days I have left – sort of like saying that blue whales may well be functionally extinct right now, even though there are a bunch left because the mating pair numbers have hit the point of no return and it’s only a matter of time. So I’ll be pushing the calculation of these numbers up into my retail database so that these numbers are real time and don’t need to be recalculated every time we run a report or simply call up the inventory.
Essentially, I’m looking at all logic that is absolutely specific to the database and the interrelation of data – the logic has no face or GUI, is basically dependent on an event (triggers) and the only result is more information in the database. Sort of like a network switch – if the traffic is destined for <this machine only> then why light up all the ports like a hub? If the data logic affects ONLY more data contained in the database, why should I execute that from outside the database?
OK, please rip me apart.
