The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Scalability of DB-oriented apps

The other day I spoke with someone who said that his company has an 80GB SQL Server database with 1000+ concurrent user, and told me that the only way to make such an application work was by pushing the business logic into stored procedures and other logic on the database server.  He said that distributed middleware layers (web services, messaging, etc.) could not scale up to those levels.

Now, I guess I know little about enterprise apps of this scale, but I was under the impression it was better to keep business logic out of the database for various reasons.

What are your thoughts on this claim?  Do enterprise apps reach a point where they basically have to be programmed in SQL stored procedures in order to handle the load?

Bit of a bummer for those of us who enjoy writing business logic code in a general-purpose programming language...
Jesse Smith Send private email
Thursday, April 06, 2006
 
 
From what I've read, there seems to be a good deal of optimization that happens for SP's that can't really happen in external code.  Unforuntately, this locks you into that db vendor/software (sometimes even version!) but sometimes you don't have much of a choice/need to consider other things.
KC Send private email
Thursday, April 06, 2006
 
 
He's right that performance requirements can be so high that the only optimization remaining is to move the code and data closer together. He's wrong to assume that moving the code into a database is the only way to achieve such speed ups.
Matt.
Thursday, April 06, 2006
 
 
The scalability really comes from doing as little work as possible. This generally means leveraging design and coding practices that are optimal for that particular DB platform.

Once the viability of database independent code has been ruled out it's a short step to putting the code into the database to tie it in as closely as possible to the data. Partly this is to reduce network traffic but it's also puts the coding in the hands of database specialists who understand concurrency, platform specific features, and tuning.
David Aldridge Send private email
Thursday, April 06, 2006
 
 
If we're really talking about an enterprise application in a real, functioning business, then this isn't as big an issue as it's made out to be.  Most businesses have standardised on one or two acceptable database platforms, so committing business logic to a layer that 'locks' them in isn't a big deal.  If they're buying a commercial product and it runs on a non-standard server, they either find something that does run on their 'standard' platform or they bring it in anyway and add the 'non-standard' thing to the approved list.

No 'enterprise' I know of has ever done an over-the-weekend type of wholesale db platform changeout.  The ones that I have seen have done it as a phased, usually multiyear, strategic change and have grandfathered in critical systems that can't or won't move to the new platform.

Instantaneous platform portability is a neat theoretical problem but rarely faced in the real world.  Having come from a company supporting a product on both Oracle and SQL Server with >1000 SPs on both platforms, I can honestly say supporting that sucks. 

My current firm's product runs on SQL Server.  If a client won't run SQL Server, they won't run our product, period.  So far it hasn't been an issue.
a former big-fiver Send private email
Friday, April 07, 2006
 
 
Business logic is not the same as data integrity.
D in PHX Send private email
Friday, April 07, 2006
 
 
Yeah, this was a real Fortune 500 company.  The reason I raised the question was that (having most of my experience in smaller companies or departments) I wasn't aware that software development of large-scale enterprise apps reduces down to being an expert SQL programmer.  Even though a job might be advertised as a C# developer position, apparently the critical skill is SQL programming.
Jesse Smith Send private email
Friday, April 07, 2006
 
 
>> Bit of a bummer for those of us who enjoy writing business logic code in a general-purpose programming language... <<

Yep.  I have to do a mental shift when I get into T/SQL.  Although your comment
>>  Even though a job might be advertised as a C# developer position <<
indicates you're using MS tools.  You may be able to use C# in your db if it's SQL Server.  Haven't tried that myself yet; there ought to be some interesting conversations with with our DBA when we wanna take that route!
a former big-fiver Send private email
Friday, April 07, 2006
 
 
Actually, for modern databases, the advantages of stored procedures are greatly overrated.  For instance, SQL Server caches query plans for dynamic (parameterized) SQL and stored procs in the _same_ cache.  Oracle has worked the same way for many years.

Sure, if you have an operation that hits a _huge_ number of records, then it may be faster to do it entirely on the database server than to pass the records over to an application server, mess with them, and then pass them back.

BUT, for everything else (i.e. things that only hit small to medium numbers of records per transaction) stored procs have no meaningful performance advantage.  See http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

I do not for a moment believe that enterprise apps have to be written in stored procs.  Why, for instance, does Amazon consider Java and C++ to be so important when hiring programmers?  If Amazon can use real languages (instead of stored procs) then I'm sure the rest of us can too!
John Rusk Send private email
Saturday, April 08, 2006
 
 
Perhaps they made a mistake of not using stored procedures from the begining, and now they have to support their mistake. :)
Seun Osewa Send private email
Sunday, April 09, 2006
 
 
>> See http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Entirely about T-SQL, a language that by all accounts sucks. No personal experience, mind, just what I've heard.

If you take PL/SQL though, it's very tightly coupled to the database and works with it very efficiently.

Ultimately though, the major benefit I see in using stored procedures is that you are likely to get higher quality of code just because the people doing the writing are more likely to be aware of how to work with the database properly, and account for it's strengths and weaknesses. They're not java developers who aren't interested in dedicating some part of their professional lives to the study of relational databases and SQL, and they're not going to treat the DB as a bit-bucket.

I ought to add that it's not just large systems that benefit from this kind of dedication, it's all of them. However the cost of bad practices on smalll systems is more easily masked by throwing hardware at the problem.
David Aldridge Send private email
Monday, April 10, 2006
 
 
>> I wasn't aware that software development of large-scale enterprise apps reduces down to being an expert SQL programmer. - Jesse S

It doesn't, necessarily.  In my experience, though, it's much harder to find folks who are competent in optimizing database queries than it it to find whiz-bang coders.  For instance, I once worked on a fixed-price contract for a government agency where a $100/hr contractor wrote a dynamic query that ignored column #1 of a 3-column index _because he wasn't presenting that column to the user_. The missing column wasn't missed too terribly in the toy data used for testing, but on Sunday night after the real data was converted & we were running our tests, it was very apparent.  When we added the column to the query, this frequently-used opreration dropped from taking >2 minutes to <.1 second.  That minor issue caused a one-week delay in implementation (migration from the older system needed to happen over a weekend) and invoked 5-figure performance penalties.

>> I do not for a moment believe that enterprise apps have to be written in stored procs. -John R

Neither do I, but see my above comments and the one below.

>> ...you are likely to get higher quality of code just because the people doing the writing are more likely to be aware of how to work with the database properly, and account for it's strengths and weaknesses. -David A

Absolutely.  DBAs tend to not allow sloppy stuff to be complied into the database.  We've all seen "junk that works" get compiled into application code, but pretty much every DBA I've known will fight to the death against it getting into the database.
a former big-fiver Send private email
Monday, April 10, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz