Rohit wrote:
>> (4) Is it faster to work at application level or at the database level?
Richard Huxton wrote:
> Probably faster in the database, assuming you have only one machine. If
> you have more than one machine then you can have each machine designed
> for its purpose. Of course, faster to run might be at the cost of more
> difficult to write. It might also mean your process code can crash the
> database server.
Which would you rather have, a wrong answer really quickly, a correct answer
slowly, or no answer at all?
Be clear on the metric for "faster" before devising strategies. Personally I
prefer "more correct" first. As Richard hinted, reliability and stability are
usually important also, I'd say more important.
"Faster to run" also depends on the degree of client concurrency. Sometimes
middleware can scale logic better than the database engine can. Systems can
achieve higher aggregate throughput with perhaps a reduction in individual
response time.
If you put logic where it "belongs" that can help scalability. The database
tends to be a central resource for all concurrent clients, so logic in that
layer is potentially a bottleneck. Danger in that layer is danger to everyone.
Business logic that relies on data but performs session-specific processing
should live in the middleware in many cases. One can throw extra hardware at
that layer to parallelize client services, and errors tend to not escape a
specific client session.
Business logic that is actually data-integrity logic could break the system if
it were in the middleware layer. Deadlocks, race conditions and other
nastiness argue that that type of logic belongs in a more central location,
with the data. One can then exploit the database engine capabilities for this
stuff.
As Richard metioned, there are plenty of boundary cases that require a
judgment call. Try to analyze which approach will have more risk in such
cases; sometimes that helps discriminate. It ain't always easy; that's why
they pay us the big bucks.
--
Lew
