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.

Stored procedure,or no stored procedures

We are developing a web application,wherein we will have a web server and a database(sql 2005) server(different machines).I have read people arguing about not using SP's for business logic.The confusion is,suppose i need to do perform some calculations using data,then I will need data on web server and perform calculations on that data,and then fire update/insert query.Wouldn't this be a slow process(since network call will be involved to get the data to webserver from database server),compared to,performing same calculations in database itself.? How can I come to a conclusion,what steps I need to take to verify the difference?
Thursday, August 30, 2007
1) Write the app in the simplest manner possible, ignoring performance.  This usually means putting it in your application code, not a stored procedure. 
2) If performance is unacceptable, profile your code.  Find the bottlenecks.  If you must use a stored procedure, make it as small and as simple as possible.

This advice assumes that your team's main expertise is in your application language (Java, C#, PHP, whatever).  I've also seen shops where the team expertise, and therefore the application logic, all resides in the database.  In that case, it can make sense to just toss everything into the database and have a thin query layer to display the app.  However, that ends up as a maintenance nightmare.  There are better languages than T-SQL / PL-SQL to express business logic.
Tim Morton
Thursday, August 30, 2007
use SPs and triggers whenever it makes sense to do so.
Totally Agreeing
Thursday, August 30, 2007
I feel that in a situation like this where you can cleary see that passing a lot of unwanted data from the database is going to cause performance issues then you should implement some logic in a stored proc, this logic shouldn't be much more than culling the amount of or type of data you will be returning.

If from your design you can clearly see a bottleneck then you should design around the bottleneck from an early iteration.
rtw Send private email
Thursday, August 30, 2007
"Wouldn't this be a slow process(since network call will be involved to get the data to webserver from database server),compared to,performing same calculations in database itself.? "

No it won't be a slow process. We do about 50 database round trips on each web page access and we have no performance problems. But we also aren't writing web sites that handle thousands of page requests per minute. Hitting a database and bringing small amounts of data back is very quick. Hitting a database and bringing back 100,000 rows is very slow. You decide which one is closer to what you intend to do in your app.
dood mcdoogle
Thursday, August 30, 2007
SP are only evil if misused, but generally should be avoided unless they offer a more elegant solution. I know a former Oracle developer who started his a very successful company which does *everything* inside of the database. Even the HTML pages of the web application are generated within the database. Its quite impressive, in a scary way.

Some examples were I've used SPs are for data loads / extracts. For example, in our ETL processes we load all of the source data first to a staging table and then kick of a SP to migrate it to production tables. This gives us a lot of flexibility without impacting production tables such as cancelling the job or merging multiple data sources which construct one final entry.

For most scenarios, performing the task in the application and heavy usage of caching is best. When it really makes sense to use a SP, do it.
Benjamin Manes Send private email
Friday, August 31, 2007
1) Do not concern yourself with performance initially.
2) Setup unit tests from the beginning.
4) Setup continues integration from the beginning. If you can, automate deployment as much as possible too.
5) Write the simplest code that will do the specs.
6) Profile your code regularly (once a week) using some typical (or vital) click-paths and keep your eyes on the function that takes up the most time (can be called many times). If you don't see any real simple way to make it faster dont touch it unless you start to notice it in your UI.
7) If you see a performance problem coming from a mile away make the simplest adjustment in the design to fix it.
8) Deploy to production as early as possible and have counters in place to give you readings on some critical areas (avarage order time or what ever).
9) Iterate. Keep your changes short and simple and redeploy as soon as possible.

The unit tests and the continues integration are there to allow you to change things and be 'notified' when you've broken something. This makes experimentation easier.
You want to get to production early because that is always a different scenario (different hardware, real users!). It doesn't matter if it runs fast (or slow) on your machine (we're not shipping your machine! ;-); only production matters.

Personally, I typically do watch for how many round trips per web request you put out, for it is a good indication how that page will do under stress. If your web content is also in the database: get it all in one round trip for the current page (return multiple result sets). Business logic induced roundtrips are often harder to unify.

SPs are not faster that normal queries. The only reason to write a SP is when you have a lot of data intensive operations to perform without a lot of (flow) logic. SQL is *not* a programming language it is a information-set language.

my 2 cents
Marc Jacobi Send private email
Friday, August 31, 2007
PL/SQL is a full featured programming language, easy to learn and very powerful.

So if you are using Oracle, you might as well use it. You've payed for it after all.

If you keep your business logic outside the database and view your DB as a "datastore", you might as well use something free, like Postgres, MySql or Firebird.
fritz Send private email
Friday, August 31, 2007
I personally like to not use stored procedures, syntax are not nice to read, lack of namespace supporting and not easy to write unit tests.

I've collected a few links about this -
Carfield Yim Send private email
Friday, August 31, 2007
>> I personally like to not use stored procedures, syntax are not nice to read, lack of namespace supporting and not easy to write unit tests.

Syntax is a matter of preference; PL/SQL has lexical scope within procedures, package spec and implementation separation; and Steven Feuerstein and colleagues have produced the very handy PLUnit for unit testing.

But the OP was talking about T-SQL, and there I really can't say.
George Jansen Send private email
Friday, August 31, 2007
From what I gather, PL/SQL is a lot more elegant than T-SQL, though I can't say that with 100% certainty. I've had a lot of success putting business logic into PL/SQL, it's very robust and has a nice library available to it.
Steve Hirsch Send private email
Tuesday, September 04, 2007
I don't think we have enough information on what you want to do. If you're loading and discarding a lot of information, then SQL's a pretty big win. If you can do your calculations on sets of data, rather than a row at a time, SQL might be the way to go. If you're loading a couple of values, doing some math on them and writing them to the DB, the benefit isn't nearly as apparant. It's possible to have too much application code on the DB, and end up slowing down the DB bits with all of your application logic.
Mike Swaim Send private email
Tuesday, September 04, 2007
For run of the mill CRUD apps (as opposed to, say, compilers and operating systems), just think how much simpler and more computationally efficient our world might be if SQL was (a) more record oriented and (b) capable of expressing things as simply and elegantly as a real language.
Greg Send private email
Wednesday, September 05, 2007
PostgreSQL, and MySQL from version 5.0 onward, support stored procedures. PostgreSQL, at least, allows the choice of several languages for the SP, including a PL/SQL version.
dot for this one
Monday, September 10, 2007
i) Do not concern yourself with performance intially
ii) Treat all databases as if they act the same
iii) find that the performance sucks
iv) find that ignoring performance has left you with extensive re-architecting to do with no time left on the schedule
v) blame the database
vi) demand that the dba fix your performance problems for you
vii) tell everyone that stored procedures = bad performance
viii) Go to (i) and repeat
David Aldridge Send private email
Monday, September 17, 2007

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

Other recent topics Other recent topics
Powered by FogBugz