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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
