next »

[kurdt]

For the first time in my life even I have done A LOT of different services with MySQL I'm actually going thru a course about SQL. I never realized before how much you can do with SQL alone. For example you can do all sort of stuff like LENGTH, SUBSTR, etc. that I have seen before in other people's scripts but I always did them separately with PHP.

So do you guys use these built-in functions or do you perform the deed with PHP or other language? Now I just need to understand why I want to use Perk's SQL IDE thingie

[lamontagne]

i usually do string manipulation within the language and try to keep the queries generic for an "all purpose use" type of thing and generally throw the queries into stored procedures... (which is what i'm guessing is the ide thingie, i dunno)... but as for why you would want to use it, it helps separate the database from the code and make security issues loads easier in my opinion. Also you can write all in one type deals with a stored procedure. For example, any time I write a stored procedure to add something to the database I make it an update function as well by passing an ID and using an if then... basically...

Code:

@id bigint,
@title text,
@description text,
@link text

if @id>0
  begin
    update links set title=@title,description=@description,link=@link where id=@id;
    select @id;
  end
else
  begin
    insert into links(title,description,link) values (@title,@description,@link);
    select @@identity;
  end

I used mssql syntax here, but same rules apply for mysql... basically this would allow me to insert a new record, update an existing record, and get back the id of the updated/newly inserted record.... for most this is basic stuff, but for someone new to sql functions i figured you might find it interesting and useful... saves loads of time if you build a lot of tables that have edit/delete/add new functions (I think the goal of ruby on rails is to make those sort of tasks trivial one liners, but I dunno much about RoR, just what I read and from a quick newb tutorial I looked at... )

[KaptainKrayola]

I'll generally offload whatever I can to the DB until it starts causing performance issues.  Then again, I like writing SQL queries so that may have something to do with it.

it really depends on what the app is doing though and whether or not the DB can do it better than whatever technology i'm using it with (PHP, ColdFusion, etc)

[nutballs]

Generally speaking, I put the work where there is space. I find though, that often in the stuff I do, the DB already has enough to deal with, and the webserver is doing almost no work, so I put it on the application side.

There is a lot to be said for putting it all in SQL though. Especially using Stored Procs. That way, many apps can use the same procs. Or you can upgrade your app, without dealing with the DB. etc. etc etc.

[arms]

when i used to work for the man, i worked on a large "enterprise" java/jsp web app. there were HUGE performance benefits when we moved the more complicated business logic to oracle stored procedures.

i've never really tried it with mysql though.

[tomblack]

I write in SQL all the time in my day job so it comes natually to me to use the SQL functions rather than PHP which I'm still learning. 

[perkiset]

(Sorry - was going to post this this morning, but a family emergency got in the way)

SQL is enormously powerful K, and a real ally in distributed processing.

My IDE allows you to write stored procedures and functions that live within the database itself. This is way, way faster (well, it can be, you can write crappy slow code in any language if you try) than doing row-by-row work in any outboard language. You can also employ triggers, which are stored procedures that can be called on an event like, onAdd onUpdate onDelete - although usage of triggers must be judicious and well calculated, because they can really mess you up big if you don't know what you're doing. Lastly, and equally cool are views, which the IDE also allows you to work on - you can use them like this: you create a view that might be a huge join, or even has fields that are calls to stored functions that then returns as a simple row-set for you.

So you could create a view like this:
select customer_id as id, getLastTransactionID() as trans_id, getCustomerTotalValue() as totalvalue, getCustomerName() as customer_name from customers

that would then do execute those functions for each potential row, but return it as a simple row set. Execution of this from PHP might take 5 or more SQL statements and some code... doing it in the database will be considerably quicker and there's no code to worry about in PHP.

The way I architect now is do push the 3 tiers of processing into Javascript, PHP and MySQL stored procedures/functions/triggers/views. Since every "location" is doing what it does best in the processing chain, the net delivery time of a page to the surfer is as quick and efficient as possible.

[isthisthingon]

--- Happy Friday Cache ---

Your question touches on one of the fun challenges of software architecture.  Sorry for the length of this post but it occurred to me that having a "quick-guide" would be useful, especially since I can't find the one I created when I taught distributed programming many years ago   In reality there will never be a correct answer that works for all situations and in all areas of a given architecture.  It's always a percentage blend that reflects the needs of each requirement. 

Since every "location" is doing what it does best in the processing chain

Nothing that follows will be news to perk.  He's a freakishly talented architect.  However, simply from exposure to a different set of life experiences I have a perspective that I'll offer.  Actually perk's one sentence is a perfect summary of what my ridiculously verbose treatise attempts retardedly to express, in a special yet handiretardicapable way.  However, I'll take full credit for "handiretardicapable."  Anyway, though important, organizing your processing based on currently available room is only part of the equation and can lead to scaling headaches if the design is "shrink wrapped" to its hardware.

Below is a list of pros and cons for the business logic tier, taken to extremes to highlight the differences.  IMO an architect's job is to take all considerations and balance the known merits of each against the realities of the target domain, including possible futures, growth, etc.  Also, the code pros and cons assume an n-tier architecture.  I would avoid binding data at the presentation layer unless it's a small, light load app that won't need to scale up.  Additionally, although many senior architects approve of and even encourage the use of 2-tier tools for core processing, it's a short-sighted approach at best and coding yourself into a cul-de-sac-failure at worst.  It's a little like saying memory management isn't important since the code is "managed."  The reliance on simple, path of least resistance performance gains will haunt you in the end, either by hitting the scaling wall or wasting money on unnecessarily powerful hardware to compensate for inefficiency.


"Quick-Guide" 

Business Logic is located in the database

Pros:

• Initially the fastest way to go.  Nothing beats the performance of processing data right next to the source
• Simplifies porting your non-database code to other languages
• Simplifies scalability since throwing more RAM, procs, etc. at the server instantly scales everything up
• Reduces network traffic since data isn't marshaled across process/machine boundaries
• Your specific question: Takes advantage of built in RDBMS functions designed to handle these tasks efficiently with minimal development effort
• Best approach for rapid prototyping, especially when requirements are likely to change.

Cons:

• 2-tier architecture (client-server), ultimately resulting in a far less scalable design for VLDB environments and web traffic with unpredictable transaction volume.
• The more utilization of RDBMS specific functions, the less portable the design.  Coding yourself into complete dependence on a specific RDBMS.
• New versions of an RDBMS SHOULD be backwards compatible but your code breaks if, for example, a function's interface changes.  FunctionA(boolean, double) changes to Function(string, boolean, double).
• Database must handle its primary job of managing data PLUS processing business logic.  Makes scaling extremely difficult to manage cost effectively and results in throwing memory/horsepower at the problem.  Hard to avoid overkill/underkill.
• Debugging is a nightmare, even with the best tools.  Stepping through SP code is difficult at best and usually impossible, so you wind up checking temp values in your DB and other hack-like techniques to monitor run-time values/states.

Business logic is located in code

Pros:

• Best scalability, by far.  Business logic can be relocated to available servers.
• Database is only responsible for its primary mission: data.
• Language independent.  Various aspects of business logic can be written in any language you want.
• Best position to be in when technology changes/improves, both from a language and RDBMS standpoint.
• Certify on numerous RDBMSs quickly: Runs on MySQL, Oracle, SQL Perver, etc.
• Scaling is far more predictable, measurable and cost-justifiable.
• Less platform dependent, since it's harder to move a DBMS to a different OS than code.
  

Cons:

• Performance hit on single box.  Unless correctly designed, the penalty for shipping data to a BL component isn't worth the transfer time.
• Performance hit on distributed environment.  Marshaling data across machine boundaries can be a serious performance problem, especially when roundtrip intensive (DCOM, CORBA, .NET Remoting, SOAP, ODBC, ADO, etc.)  Prefered: Asynchronous calls that completely describe the processing request without roundtrips.
• Synchronous calls that block waiting for return values are far more problematic across machine boundaries.
• Complicates network planning, scaling, and creates potential network bottlenecks.  In a way this is similar to the scaling challenge when the DB handles all business logic.  When performance becomes a concern in a 2-tier, database-centric design, it's very difficult to determine the performance impact of business logic versus seeks, inserts, updates, etc.  Similarly, the additional network bandwidth consumed in a distributed architecture is difficult to manage, since the more granular interactions are harder to quantify, plan for scaling, etc.
• Although when done properly scaling is more predictable, it's also considerably more complicated to design, develop, manage, and tune for consistent performance across multiple servers.

True n-tier architectures are occasionally considered "overkill" but usually superior to anything 2-tier.  I've never regretted designing from an n-tier perspective.  However, I can't count all the times I've been stung by, been involved with, or otherwise seen the quick and easy 2-tier approach cause serious problems, rewrites and countless wasted hours trying to identify "where the performance problem is."

Again sorry for the length of this post.  I just wish I had someone explain these things to me 20+ years ago when I was learning by brick wall.

[perkiset]

Thank you ITTO, both for an excellent illumination and your extraordinary compliment.

I apologize again that I'm not really involved today... one of my sons took a hard hit during his football game last night and broke his collar bone. He is OK and the orthosurg says that he will be as good as new in 3 months with no surgery, but his season is over. So I am understandably inarticulate and distracted tonight. (But he did catch and hold the ball for a great 25yd completion, even as he went down and was broken. One TOUGH MoFo).

Probably the biggest difference between ITTO and me is that he's studied, spent time with people of technical philosophy, had to endure people that were above him and had their head up their asses and managed teams of people that, well, often had their heads up their asses as well. And he's had to produce. So his thinking is more classical in that he need to get lots of people to succeed at something together. He will also need to report back to financial masters about why decisions were made. Why technologies were chosen. Why (that) development philosophy was used.

I am a Ronin. When I first went to college I was aghast at the lack of gray matter or vision in my professors (re. computers) so I left and toured professionally as a musician. I went back to college for Marine Bio because it was not something I wanted a career in, I just wanted to study. So my methods and designs come from a self-inflicted philosophy that is at once masochistic, audacious and most probably, irreplicable by anything that would resemble a team. And after 33 years of progamming and probably about 20 of architecture now, I am rather, well, opinionated  .

My personal philosophy is really based on exactly the pros and cons of what ITTO speaks. The difference may be that I don't see it as one or the other: I use a 3(+) tier, 5 language architecture to take advantage of the best parts of each point in the processing chain. In other words, I push business logic (well, at least a small and appropriate component of it) right out to the client, some at the request handler and some in the database. ITTO is right: there are some real constraints to scalability if all the business logic lives in the DB: but if *some* of it lives there, you can really maximize the potential of a system. To wit: I had a web framework that delivered highly complex pages (on average, about 35ish queries per page, sometimes rather large sets) that would take as long as 3-4/10s a second to produce. the load to my system was gigantic. spreading out the processing I now produce *better* pages with even more complexity in about 4/1000s. That said, even with this sort of per-machine capability there will be times that the system is overloaded and I need to distribute processing across several boxes. Therefore, inter-tier communication is kept to an absolute minumum - my framework now executes, on average, 7 queries per page (including DB-based session management) so if I abstract the DB from the renderer, the increase in construction time will not be *that* large.

The downside to my designs (and ITTO was at the pointy end of the stick on one of them, almost 10 years ago) is that the religion of My Favorite Language or worse, the decidedly inertia-less laziness of the standard programmer makes real cooperation and efficient intersystem/inter-tier communication problematic. Also, spreading the knowledge of How It All Works across several people (for the various disciplines) is often *MUCH* more headache than it is worth.

But if you can shrug all that off, and keep several languages alive in your head all at once, and have a stupidly dogmatic desire to make things extremely efficient, robust and capable, as well as maintainable for the programmer and hyper-friendly for the user, well then this might be the path for you.

Don't forget to purchase a lot of Motrin

If I may be so bold as to distill ITTOs and my diatribes to a chewable nugget:

Learn how to put business logic in the database.
But know that it's both your best friend and your worst enemy.
Used appropriately, it'll keep you warm and snuggly all night.
Over-leaned upon it will puke on you in a complex and startling way that you'll never untangle.

[lamontagne]

Perks, your post reminds me of a comment I read in some code once back when I did the 9-5. It was the funniest line in code I ever read, well, besides if($dickinthe==$vagina) ... but that's another story.. anyways, the comment said exactly this:

# I am so tired, my eyes hurt so bad, I am just going to copy the code and make a few small changes so I can quit...

Basically it was a programming function that this guy wrote to do something and it was quite a bit of code, well it didn't satisfy every scenario as he had hoped. So he copied the code from the other function, changed a few lines and renamed the function....

[perkiset]

Oh man I've NEVER done something like that... 

[isthisthingon]

if($dickinthe==$vagina)

while(true == true && false != me)
{
   denial += 1;
}

Hoping for an endless loop    Just checking quickly before getting horizontal and watching reality re-runs with my gf.  Bridezilla is off the hook funny.

Seriously glad to know your son's incident today is repairable and most importantly, please let him know that uncle ITTO was proud of the 25 yards - but expected 26 

ty perks for all the return compliments.  You totally suck but not nearly as much as me 

[isthisthingon]

But if you can shrug all that off, and keep several languages alive in your head all at once, and have a stupidly dogmatic desire to make things extremely efficient, robust and capable, as well as maintainable for the programmer and hyper-friendly for the user, well then this might be the path for you.

Additionally, this path requires the kind of brain that wobbles atop perk's bay-watch body of carnal desire.  It's like Bruce Lee saying: "this is what works for me," but you keep getting your ass kicked.  Then from off camera Jackie Chan leaps in chode-first and finishes up the unfavorably high mortality risk scenes.  But he sucks.

Oh shit I'm sorry, I think my bourbon just kicked in.  I just pooped on the Cache!!!... but caught the last nugget with chopsticks 

[kurdt]

ITTO, great post. It verified how I imagined the pros and cons mostly. I'm gonna copy/paste that to my box so if I ever need to verify my decision, I can check it thru and say that if the teacher with the led lights says so, it must be so

I'm surprised that there isn't any good solution for scalability with MySQL. So rule of thumb could be that if you are planning to do a system that will grow over 2 boxes, stay with code processing. But if you process with code, then you better learn how to do ninja queries to avoid unnecessary memory consumption. At the moment this is my biggest weakness with MySQL work. I did a lot of work that could be done with proper queries in the code which is really unnecessary and stupid. Now I got to learn how much like SQL Hive really is. I know it can do JOIN and GROUP BY but how about more advanced functions. With Hadoop there really isn't problems with scalability and that's kinda the whole point. But yet again it only works with LARGE data sets. Shit, why there just can't be one database that scales and works like your normal average database...

Perk... hope you kid gets well soon. I have witnessed few of those collar bones getting broke when snowboarding and boy it looks paaaiiiinnnfffuuuuullll. If your kid still continued after it got broken, I gotta say that respect And the worst part is that it kinda prevents you from doing nothing for a long time 'cause you are operating with one hand and other one hurts like hell.

[perkiset]

I'm surprised that there isn't any good solution for scalability with MySQL. So rule of thumb could be that if you are planning to do a system that will grow over 2 boxes, stay with code processing.

NO NO NO. I mean, NO NO NO.

I have to run to a clients. Comment more later.

Perk... hope you kid gets well soon. I have witnessed few of those collar bones getting broke when snowboarding and boy it looks paaaiiiinnnfffuuuuullll. If your kid still continued after it got broken, I gotta say that respect And the worst part is that it kinda prevents you from doing nothing for a long time 'cause you are operating with one hand and other one hurts like hell.

Thanks K ... it certainly looks it. He's one toughy, but it's absolutely knocked him down. Again, sorry guys have to run, update more later.