The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 01:51:47 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Stored Procedures, Best Practices...?  (Read 3985 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: May 28, 2008, 02:10:35 PM »

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.

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #1 on: May 29, 2008, 12:49:42 AM »

On the down side, I burden the purchaser with the processing time of it… but this is measured in milliseconds.

I have to ask..... why?

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #2 on: May 29, 2008, 05:58:28 AM »

On the down side, I burden the purchaser with the processing time of it… but this is measured in milliseconds.

I have to ask..... why?

Cheers,
td

I'd have to ask why not? Processor time gets exponentially more expensive as your site gets popular Cheesy
Logged

hai
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: May 29, 2008, 08:30:52 AM »

I have to ask..... why?

Well, it's got to be done at some point... so when do I do it?

The question is always payment... who/what will pay for for the processing time (I'm not talking money of course) - it makes sense to take all possible workload off the transaction process to make the point of sale as quick as possible... but for the additional cycles needed to make it so that numbers that only change when this event occurs get changed, then that thinking may be all wrong.

Tis the whole point of this thread Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #4 on: May 29, 2008, 02:30:56 PM »

Why not do it after they're gone?
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: May 29, 2008, 02:38:02 PM »

Well first off I'm not sure how to do a "delayed trigger" that would only fire after an event and after a specific amount of time... so I'd be back to a cron job. And since the monitoring systems run once a minute, I'd be right where I am, reprocessing numbers that haven't changed once a minute.

I could do a "dirty flag" somewhere that the cron job looks to - if ! dirty then close down without doing anything, but then I've got a complexity problem - what if something occurs and the dirty flag is not thrown? Then I'm out of sync... no, the most reliable ways are either at the moment of sale or once a (period) regardless of changes.

It just seems so terribly inefficient to do it once a minute, regardless if there is a sale - I'm very drawn to event driven models, so triggers appeal to me *I THINK* provided the downside doesn't out weigh the upside... so I'm looking for both ... erm ... sides  ROFLMAO
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #6 on: May 29, 2008, 03:28:19 PM »


On the down side, I burden the purchaser with the processing time of it… but this is measured in milliseconds.

"But what are they waiting for and why MUST they wait?" is what I'm trying to communicate (and failing miserably :-( )

Cheers,
td
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #7 on: May 29, 2008, 03:42:08 PM »

Well that's sort of the question TD... *someone* will pay for the time to process ... the question here really isn't about paying for the time, because in this case the stored proc will run in scant milliseconds, obviously all serverside - so the 'payment' is not that great and load against the server is significantly decreased as described in my first post.

(in case we're crossing here, what needs to be processed is stuff like the updating of the SQ in the database as well as telemetry numbers and such... things that only change when a sale or inventory modification occurs)

But this adds a level of complexity to the app as well as a less serial and more event driven timing that <may be> more difficult to debug. My feeling is that the effort is completely worth it and is a worthy learning curve for a new methodology and toolset for building larger, more complicated apps... but I'm still pretty new to this way of thinking.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!