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.

Never use stored procedures?

I was just (casually) reading the copilot spec for the first time (enthralling read, great plot, superb twist) and am interested why "we hope never to use stored procedures"?

My experience of stored procedures is that they are juicy and delicious and, if you get a really good one, will give you sound advice on styling your hair in the morning, so I am a little bamboozled as to why fog creek hope never to use them?
Don Vince Send private email
Monday, January 01, 2007
 
 
Fog Creek prefers portability over speed. They specifically want to write their applications in one language, so to speak, and then allow their compilers to generate the necessary binaries and/or bytecode for whatever platiform they're targeting at the moment. You may find the discussion on Wasabi interesting; search Joel's columns.

Stored Procedures generally invite the developer to take advantage of the database's proprietary extensions, and while a lot of tasks are easier to accomplish this way, it's arguably hard to write... a single psuedo procedure and installer combo that works for Oracle, MS SQL Server, MySQL, Postgre, DB2, Firebird, etc etc.
TheDavid
Monday, January 01, 2007
 
 
Incompatible across DB is a good reason. Although generating the 5 different variants wouldn't kill you, if you needed the speed.  Could be joel has some other misaprehension about stored procs.
Jimmy Banks
Monday, January 01, 2007
 
 
I would think the usual portability argument.  Unfortunately, every relational DB platform has big differences in calling and using SPs (and in some cases, SPs didn't even exist for a long time - like MySQL).

However, it must be said that even SQL as typically written for various platforms in their own native style have a lot of incompatibilities which need to be avoided if you are trying to write portable (ANSI) SQL.  And the level of ANSI compatibility itself varies (a lot worse than the C/C++ world ever was in the late 80s).  So it is potentially possible that you could spend as long trying to make your SQL portable than debugging different SPs in the first place.  Or a possible strategy is actually avoiding most non-trivial SQL completely (leaving mainly select whatever from table where pk = criteria).

This is certainly true for a some code-gen'ed ORM-style work - where a lot of times there are multiple trivial trips to the DB for single rows instead of stored procedures in hand-coded ORM work, where the SP returns multiple rowsets.  Again, there's a tradeoff in code lifecycle management (and even writing code at all) versus performance.
Cade Roux Send private email
Monday, January 01, 2007
 
 
Another point is this: the often-quoted advantages of stored procs are no-longer as great as they used to be in the 90s.  For instance, it is now questionable whether they have any intrinsic performance advantage (simply due to their being "precompiled").  See http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
John Rusk Send private email
Monday, January 01, 2007
 
 
Stored procedures also introduce organization and deployment issues.  They're also hard to put into source control.  The isn't that much difference in performance anyway.
Almost H. Anonymous Send private email
Monday, January 01, 2007
 
 
One reason is to free up your database CPU cycles for handling requests instead of logic. One of the scaling design principles is to offload the database as much as possible. If you are running complex logic on each row then you are sucking a lot of CPU power that could be used elsewhere. Instead, let the database server data and your applications perform logic, perhaps in a cluster.
son of parnas
Monday, January 01, 2007
 
 
Updates are easier. If all logic is contained in the binary then that's all you have to redistribute. With SP's you would also need to distribute a SQL script to update the SP's. You would still need the script if you are updating data storage.
Phil C Send private email
Tuesday, January 02, 2007
 
 
>> http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

It's almost as if Microsoft make the only RDBMS in the world, huh? Please don't assume that every system has the same architecture as SQL Server, nor the same failings.

>> One reason is to free up your database CPU cycles for handling requests instead of logic. One of the scaling design principles is to offload the database as much as possible.

Why is it cheaper or more efficient to buy a different set of CPU's to perform business logic? Why does it make a system more scalable to do so?
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
Wow John Rusk! Thanks for that link:
( http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx )

All others, if you've read down this far, but not read that page, do so now for it is full of information debunking some of the misconceptions I had.

---

Have you clicked it yet? Go - do clicky!
Don Vince Send private email
Tuesday, January 02, 2007
 
 
> Why is it cheaper or more efficient to buy a different set of CPU's to perform business logic? Why does it make a system more scalable to do so?

Because your primary database is the only place that writes can happen transactionally. Reads can be spread to slaves but even then then the replication overhead grows too high as your system scales. By dedicating cycles to logic the database can't be doing the only job that it can do. Logic can be done anywhere.
son of parnas
Tuesday, January 02, 2007
 
 
> http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

He missed control. In one system I worked on every database access has to be through a stored proc because it allowed the DBAs control of the data.
son of parnas
Tuesday, January 02, 2007
 
 
Yeah, the control thing.  Gawd I hate those guys!

Another thing is that the hardware & driver software used to be so slow; it was much more time-efficient to call an SP, hand off parameters and let the DB do the crunching.  Now, even ODBC offers performance equal to or better than the 'native' drivers on reasonable hardware for any given statement.

One place I personally think SPs have a place: complex transactions.  I'm not talking about two or three tables; I'm talking about transactions where 10 or more tables need to update, _correctly_, in a single transaction.  Maybe my opinion is colored by the fact that most of the folks I've worked with over the past 10+ years were bodyshopped but my experience is that very few of the people out there competing for jobs with JOS readers are _interested_ in thinking about data relationships of any complexity, event if they're _capable_ of it.  Not a mindset you want programming the guts of mission-critical apps, IMO, any so-called 'data access layer' notwithstanding.

I;m not saying that's the only way to manage complex transactions, just that it's a proven way that still has a place.
a former big-fiver Send private email
Tuesday, January 02, 2007
 
 
>> Because your primary database is the only place that writes can happen transactionally. Reads can be spread to slaves but even then then the replication overhead grows too high as your system scales. By dedicating cycles to logic the database can't be doing the only job that it can do. Logic can be done anywhere.

I've been working with Oracle for 12+ years and I'm afraid that none of that made sense to me.

"primary" database? What that?

"Reads can be spread to slaves ..." what sort of slaves? Whay are reads not transactional?

"...  even then then the replication overhead grows too high as your system scales" Replication how and from where and to where?

"By dedicating cycles to logic the database can't be doing the only job that it can do. Logic can be done anywhere." ... of course it can be doing logic. You may need more CPU's to do so but you're just going to put those CPU's on a different box otherwise so you're definitely not saving anything simply through spreading the load over multiple boxes. Just because logic can be done elsewhere doesn't make doing so sensible.

I think that you're a long way from making the case that business logic and data access are incompatible in any way.
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
> "primary" database? What that?

In mysql speak it's called the master and the databases syncing to the master are called slaves. A typical architecture is to have write queries and any read query that can't stand inconsistency go to the master. Other queries can be load balanced across the slaves.

> Whay are reads not transactional?

Transactional relative to the slave or the master? Having a distributed transaction is slow and is thankfully not needed for many applications.

An interesting discussion on the subject is: http://www.addsimplicity.com/adding_simplicity_an_engi/2006/12/avoiding_two_ph.html - Avoiding Two Phase Commit, Redux

Another interesting take is how ebay has decided to move almost everything out of the database: http://netmesh.info/jernst/Comments/sdforum-ebay-architecture.html


> I think that you're a long way from making the case
> that business logic and data access are incompatible
> in any way.

I didn't try to make that case. It's your decision based on the tradeoffs. I brought up one side.
son of parnas
Tuesday, January 02, 2007
 
 
>> He missed control. In one system I worked on every database access has to be through a stored proc because it allowed the DBAs control of the data.

DBA's are generally the only ones ever held responsible for the data quality and the database performance. When confronted with yet another new breed of developers fresh from school wielding the latest industry silver bullet and misinformation (constraints are evil, databases are just bit buckets, store everything as XML, use a single universally extensible table, stored procedures are slow, commit after every single DML) they rightly roll their eyes and retreat behind every defensive layer they can.

Full disclosure: I'm not a DBA, I'm an Oracle developer.

This thread must be the first troll of 2007. Happy new year, everyone. :)
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
>>In mysql speak it's called the master and the databases syncing to the master are called slaves

What's the reason for that architecture though, and how is it relevant to other RDBMS's? If a single (or clustered) machine can be used that can run the business logic as well (and in my experience the business logic is not close to the majority of the load anyway, in comparison with DML) then why does it become more efficient to offload that business logic onto another layer?

With regard to eBay, are we now to take it that their solution is the one that is ipso facto the most efficient? Are we now supposed to believe that joins, sorting and referential integity are more efficiently performed in a different layer to the data? Surely not.
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
> What's the reason for that architecture though

It allows cheap horizontal scale out.

> how is it relevant to other RDBMS's?

I don't know, how is it?

> If a single (or clustered) machine can be used that can run the business logic as well

Being an Oracle guy you are used to people throwing tons of money at their database. If I am starting a web company, for example, I don't have a ton of money. I'll buy a good master with RAID 10 for optimizing writes and then cheaper read oriented slaves. In MySql the slaves can be configured differently to be optimized purely for reads. As most of the traffic is read oriented scaling is accomplished by adding cheap read slaves. This gives out at a certain point, but it's useful for a long while.

> are we now to take it that their solution is the one that is ipso facto the most efficient?

There's no way ever knowing that. But it's something to learn from.

> Surely not.

I don't expect you to do anything different than you've ever done. Others might find something useful however.
son of parnas
Tuesday, January 02, 2007
 
 
>> how is it relevant to other RDBMS's?
>I don't know, how is it?

I don't think it is, and I don't think it's relevant to stored procedures either. You're talking about cases where you run out of hardware because business logic tips your CPU usage over the limit, but that's apparantly based on experience with databases that lack a fast, efficient and well-established language like PL/SQL. If you want to say "don't use sp's in mysql or sql server" then go ahead, but don't try to transfer that knowledge over to Oracle.

>Being an Oracle guy you are used to people throwing tons of money at their database.

I'm sorry that your clients are short of cash, but just about all companies are interested in an economical solution -- I don't see the purchase of more hardware for a bigger application layer to be a money saver though

>> are we now to take it that their solution is the one that is ipso facto the most efficient?

>There's no way ever knowing that. But it's something to learn from.

If we can't judge whether it's more efficient then what are we going to learn? Just that someone did something for a reason that they thought was good, nothing more. Ebay's experience is virtually meaningless outside of their business domain.

> I don't expect you to do anything different than you've ever done. Others might find something useful however.

Nice personal jab -- well done on that. But I think that using eBay's experience as a guide is actually very dangerous. That doesn't mean that it won't encourage a new generation to believe that there's no way that Oracle could possibly have worked out efficient ways of joining and sorting in the last 20+ years of research. Hand-rolled solutions will soon proliferate, and when the applications don't perform then it will be the fault of the database, naturally.
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
> Nice personal jab -- well done on that.

I figured you earned it by your continual superficial dismissiveness. Which brings us to your real nit:

>That doesn't mean that it won't encourage a new
> generation to believe that there's no way that Oracle
> could possibly have worked out efficient ways of
> joining and sorting in the last 20+ years of research.

Oracle is great, but expensive, especially for a startup. Using stored procs or not is an architectural decision in the end and you have to keep your entire architecture in mind when making the decision. If you have the bucks for an Oracle RAC solution then that might be a good way to go.
son of parnas
Tuesday, January 02, 2007
 
 
Wikipedia uses MySQL and the previously mentioned master write, slave read paradigm because they have determined that the vast majority of their database requests are relatively simple select statements. Their architecture and choice of databases prioritize speed, horizontal scalability and (ahem) cheapness. They obviously seem to be doing well.

However, Wikipedia is hardly representative of the rest of us. As David Aldridge implies, different people have different needs. Here at my company, our needs are best met by SQL Server (application support), Oracle (records management) and surprisingly so, Ingres (forms and data entry).  Each of these scenarios has different "problems" and the databases chosen provide what we thought were the best "solutions" to each problem.  Please note that we've never believed there was a single one right way of doing things.

With respect to the original topic, maintaining multiple database servers however takes up a considerable chunk of our budget, training time, staff availability, etc etc. We'd have to make a very good business case for buying an application that only runs on DB2 or Firebird; better to buy something that runs on one of our existing databases and save money on support costs.

The fact that Joel has made FogBugz relatively portable by banning stored procedures and doing much of the logic within the application itself, makes it a lot easier to justify buying FogBugz. (It should be said for the record that we haven't looked at it or bought it and I honestly don't know if it will run anything other than SQL Server.) By deciding up front not to use stored procedures, Joel has simply made it orders of magnitude easier to support additional platiforms if he so chooses.
TheDavid
Tuesday, January 02, 2007
 
 
> Wikipedia is hardly representative of the rest of us

Depends on who us is :-) It certainly does matter though. Decision support, OLTP, web, data mining, etc all have different needs.
son of parnas
Tuesday, January 02, 2007
 
 
Correct me if I am wrong, SOP, but I don't think your point was that you should have mySQL master-slave databases.

I think your point was that any logic executed inside an SP beyond just grabbing records does not actually have to run inside the DB, and could therefore be offloaded to another box, thus leaving the box hosting the DB to do nothing but service database requests.

If that is what you meant, this argument makes perfect sense to me. However, I would never say something like "Never used stored procedures" any more than I would tell someone buying tools "never use a jackhammer". Depends on the task at hand.

Bottom line, IMO, SP's are fine if scaling is not a big consideration, and even if it is, they often are anyway.
Greg Send private email
Tuesday, January 02, 2007
 
 
> I would never say something like "Never used stored
> procedures" any more than

Agree perfectly. But the opposite is always true. We usually don't see a continuum of opinion though. Since I took database classes very long ago the canonical view was to use stored procedures to move the logic closest to the data to minimize paging. This is sensible, but there are other issues to consider too.
son of parnas
Tuesday, January 02, 2007
 
 
> But the opposite is always true.

always -> also
son of parnas
Tuesday, January 02, 2007
 
 
>> I figured you earned it by your continual superficial dismissiveness. Which brings us to your real nit:

Sorry you felt dismissed. I didn't understand the basis of your argument and asked for clarification.

> Oracle is great, but expensive, especially for a startup. Using stored procs or not is an architectural decision in the end and you have to keep your entire architecture in mind when making the decision. If you have the bucks for an Oracle RAC solution then that might be a good way to go.

Well Oracle RAC is an extremely big/expensive hammer. http://www.miracleas.dk/WritingsFromMogens/YouProbablyDontNeedRACUSVersion.pdf

Cost is relative -- when you pay for Oracle you also get a bunch of features that can lower your cost of ownership, but it's up to you to use them. In this case I think it's fair to point out that you get a lot more procedural programming power with PL/SQL than you get from SQL Server's offering. I can't comment on MySQL's procedural language, but PL/SQL has a lot of power, is a natural data processing language, and is not tricky to learn at all.

If there's one key point then it is this: Oracle becomes an expensive solution when you buy it for its storage and query power and ignore the rest of what you're getting "free" with it. With Standard Edition you get Fail Safe, Flashback Query, Application Express, Java and PL/SQL native compilation, data encryption, native XML support, Advanced Queuing, distributed transactions etc.. All this is wasted when you decide to roll your own solutions to problems that Oracle has already fixed. (Sorting, joing and referential integrity also, of course). Many of those even come with the free version.
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
> Cost is relative

Cost is absolute. No amount of benefits can offset a cost that exceeds your budget.
son of parnas
Tuesday, January 02, 2007
 
 
>> I think your point was that any logic executed inside an SP beyond just grabbing records does not actually have to run inside the DB, and could therefore be offloaded to another box, thus leaving the box hosting the DB to do nothing but service database requests.

>> If that is what you meant, this argument makes perfect sense to me.

It _sounds_ good, but given that application code is typically less intensive than db access and DML then I personally don't see it as that significant. I don't think that it neccessarily holds up to scrutiny when you consider network issues and the cost of the extra layer.

I'd rate as exceptions cases where you are facing tricky-to-resolve hardware limits or where your db procedural language sucks so badly that there are consequently significant advantages in using an application layer with a better language.
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
>> Cost is absolute. No amount of benefits can offset a cost that exceeds your budget.

Then what you have there is a boundary case of a shoestring development that can afford almost no licensing fees. As it happens you can develop and host a robust and permormant browser accessible application entirely within Oracle (Oracle Standard Edition One and Application Express) for $5K per processor perpetual. Compared to staff costs that's trivial.
David Aldridge Send private email
Tuesday, January 02, 2007
 
 
"Never use stored procedures?"

Never say "never". But I don't typically use them anyway. The benefits often cited simply don't apply to desktop apps where the database is on the client's machine along with the app itself.
anon
Tuesday, January 02, 2007
 
 
I started a discussion on this very topic a while back, but from the other side. My bias is to put all the logic in the database itself, while using the other layers only for drawing pictures and talking to the user.
Steve Hirsch Send private email
Wednesday, January 03, 2007
 
 
>> My bias is to put all the logic in the database itself, while using the other layers only for drawing pictures and talking to the user.

+1

However, that would still depend on the characteristics of the db server procedural language, right?
David Aldridge Send private email
Wednesday, January 03, 2007
 
 
"However, that would still depend on the characteristics of the db server procedural language, right?"

100%. Your code would therefore be 100% non-portable, or close thereof, so, if portability is an issue, it would be not be the first design choice.

It's interesting, I've wound up at a product company where we make a portable product. However, our installations have been 100% Oracle; it's a lot easier to do lots of things in Oracle than in WebSphere.

Also, btw, PL/SQL has some very nice OO features in it now,  if you're into that sort of thing. You can even do PL/SQL active server pages now, though why would you do that, I don't know.
Steve Hirsch Send private email
Wednesday, January 03, 2007
 
 
I think this whole stored procedures debate is one of the great debates of our industry and I am never entirely sure what the right answer is. Its surprising this debate doesnt come up more often than it does. Anyways, back to real work now!
Greg Send private email
Wednesday, January 03, 2007
 
 
Steve, I'm inclined to wonder whether there is a damping effect on the use of PL/SQL (for example) because of the difficulty of working procedurally in other databases. Does PL/SQL suffer by being categerised as a db language because of problems with others?

Also, if a company decided to go the route of providing a cross-platform product (say Oracle, DB2, SQL Server, and MySQL) but to use stored procedures (obviously coded and optimized on a per platform basis) then ...

i) which platforms would be the easy and difficult ones to do this on?

ii) how economically could this be done when based on a common interface -- in fact is a some common interface possible?

iii) how much performance improvement (or to put it another way, how much cheaper-hardware benefit) might there be in such an approach?
David Aldridge Send private email
Wednesday, January 03, 2007
 
 
"I think this whole stored procedures debate is one of the great debates of our industry"

I agree.  It's a pity that this thread, which started off being about that, turned into a pissing match over whether Oracle is the end-all be-all of databases or not.
Kyralessa Send private email
Wednesday, January 03, 2007
 
 
>> It's a pity that this thread, which started off being about that, turned into a pissing match over whether Oracle is the end-all be-all of databases or not.

Well I'd prefer to phrase it as "an education on Not All Database Being The Same", a common misconception round these parts.
David Aldridge Send private email
Wednesday, January 03, 2007
 
 
@DavidA >>if a company decided to go the route of providing a cross-platform product ... but to use stored procedures

I can speak to one cross-DB, SP-using translation: product originally implemented on Sybase' SQL Server (v10.x), ported to Oracle (v7/8).

The app was pretty complex, lots of multitable transactions, code lookups etc.  Mission-critical & time-sensitive for the client (data push to external law enforcement entities w/minute-or-so latency).  It used approximately 300 SPs containing logic, validations, complex transaction control plus a bunch of boiler plate CRUD operation procs (dropped later). 

Using ODBC, we didn't have to recode the client app access.  Movement of the basic SQL was drop-dead simple; a few syntax changes for the variance in compliance w/SQL-92 & that was done.  We used sed/awk scripts for that. 

What was horrendously expensive to convert was the different ways the procedural code (T-SQL vs PL/SQL) worked.  I can't explain it here, but you need to _think_ differently in the two environments.  Sybase returned result sets with just a SELECT; we needed to recode to cursor through final result SELECTs in Oracle (don't know if that's true now). Add in a few other wrinkles like that, and you have a killer for cross-platform SP compatibility.  We had a capable DBA busy for 6+ months just on this; following the developmental migration of the base (Sybase) product added a couple more.

Cross-platform SP support _can_ be done but IMO you need a high value proposition to make it worthwhile, at least using the proprietary transactional languages.  Maybe it'll change with support for Java or CLR language based SPs being added but not in the short term.  Even then, I think you'd want to limit the use of SPs to absolutely critical pieces if you want a reasonable support burden.
a former big-fiver Send private email
Wednesday, January 03, 2007
 
 
>> I think you'd want to limit the use of SPs to absolutely critical pieces if you want a reasonable support burden

Batch operations, perhaps.
David Aldridge Send private email
Wednesday, January 03, 2007
 
 
"Well I'd prefer to phrase it as "an education on Not All Database Being The Same", a common misconception round these parts"

You'll note most of the "examples of horizontal scalability" tossed on this thread all involve MySQL somehow.  $1,000 says most of those apps use horribly inefficient, poorly written queries to work around the shortcomings of their database vendor.

There is already a free smaller scale equivalent to Oracle.  Postgres.  That ought to scale most companies out well to the point where they can afford shelling out big bucks for Oracle.  MySQL on the other hand will choke long before that point...
Postgres Guy
Wednesday, January 03, 2007
 
 
$1,000 says most of _any_ app uses horribly inefficient queries. :)
David Aldridge Send private email
Wednesday, January 03, 2007
 
 
"Steve, I'm inclined to wonder whether there is a damping effect on the use of PL/SQL (for example) because of the difficulty of working procedurally in other databases. Does PL/SQL suffer by being categorized as a db language because of problems with others?"

Dunno. I've been pretty much in an Oracle-only world for a while now. Don't know the state of the art with other DBs.

"Also, if a company decided to go the route of providing a cross-platform product (say Oracle, DB2, SQL Server, and MySQL) but to use stored procedures (obviously coded and optimized on a per platform basis) then ...

i) which platforms would be the easy and difficult ones to do this on?

ii) how economically could this be done when based on a common interface -- in fact is a some common interface possible?

iii) how much performance improvement (or to put it another way, how much cheaper-hardware benefit) might there be in such an approach?"

All good questions, but there's only one honest answer: it depends. My best guess for those porting apps is to do the CRUD in the middle layer with Java, C#, whatever; implement any kind of complex retrieval logic using SQL views, which should be fairly standard, and any bulk loading using the database's own tools. PL/SQL and T-SQL, for example, just seem very different.
Steve Hirsch Send private email
Wednesday, January 03, 2007
 
 
My personal list of pros and cons for stored procedures.

Pros:
1) If you have multiple applications sharing the database, stored procedures provide shared code.  In this case, "applications" also include interfaces that send or receive data from other systems.

2) A change in a stored procedure is automatically deployed to all users in one shot.

3) Stored procedures can reduce network hops, which may be useful when the database is at the end of a slow connection.

Cons:
1) Updating stored procedures requires a database outage.

2) Stored procedures cause linkages between application versions and database versions. 

3) Stored procedures cannot be selectively deployed, i.e., given to one set of users on a trial basis while the rest of the world runs on the previous version.  Caveat: One could version the stored procedures by explicitly attaching a version number (for example, Do_This_v1), but this leads to coupling between application code and the database.

4) Stored procedures usually require more processing by the database and it has been my experience that performance issues arise more often at the database than the the middle tier or client.  It is also usually less expensive to throw more hardware at a middle tier problem than a database problem.

5) Having business logic split between application code and database code is often the worst of both worlds.  Problems end up being batted back and forth between application developers and database developers.  This may be more of a people issue than a technical issue, but it is a common management pain in the butt that often arises.

6) Actions done in stored procedures can lead to surprises in the application code when integration occurs.  This can be a major issue if you rely on unit tests that do not run against the database.

7) I've never personally been in an environment where developing for multiple databases was needed.  Given the previous discussion, however, if multiple databases are a requirement, then this might be a key concern.

In general, I prefer to put all logic in application code, but there are specific instances where stored procedures will be a better choice.
Wayne M.
Tuesday, January 09, 2007
 
 
Nice list Wayne. I pretty much agree with all of your points. The only one I would add to is Pro #2:

"2) A change in a stored procedure is automatically deployed to all users in one shot."

This assumes that you only have one database. Having databases on each client system (think Quicken for example) makes this a con because you have to deploy stored procedure updates to each client which is often harder to do than code updates. So this one can go both ways.
anon
Tuesday, January 09, 2007
 
 
>> Why is it cheaper or more efficient to buy a different set of CPU's to perform business logic? Why does it make a system more scalable to do so?

> Because your primary database is the only place that writes can happen transactionally. Reads can be spread to slaves but even then then the replication overhead grows too high as your system scales. By dedicating cycles to logic the database can't be doing the only job that it can do. Logic can be done anywhere.

I beg to differ.

Stored Procedures give much much better performance (10x, 100x+)
because the SP manipulates the data locally within the same machine, without incurring the delays of network I/O.

Also, webapp or SOA programmmers can be skilled in Java or .NET, but, they are usually far behind dedicated SQL programmers when it comes to tuning SQL queries.

Anyway, very complex software is never trivial to port.

If one wants to use SPs for a multi-platforms app,
then one should look at a very smart code generator for
writing the bulk of the SPs. And... plenty of sweating
for the toughest 5 or 10% remaining.

-JF
Jean Francois Send private email
Monday, January 15, 2007
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz