Re: choices regarding where to place code - in the database or middletier

Daniel Morgan wrote:

> Joe Weinstein wrote:
>
>> Daniel Morgan wrote:
>>
>>> Render under to database everything you can do in the database and
>>> let the middle tier do what it does best ... fail-over, load
>>> levelling, and serving up the front-end.
>>
>> Sure, and and mindless
>> repeat queries, and acting as a transaction monitor/controller to get
>> the best
>> performance out of the DBMS, etc. Otherwise you should explain why
>> Oracle chooses
>> to use BEA as middleware when it simply tries to demonstrate it's own
>> performance
>> potential in TPC-C benchmarks.
>
> There are things a lot more important than benchmarks and performance.

Well, not to some marketing types... ;), and I'm sure you know of businesses whose application performance *is* a top-tier concern. Benchmarks wouldn't be done if they didn't intend to hint at proving something that customers want. So, let's agree that the individual customers will decide for themselves whether (and how much) performance is important to them.

> We all know all RDBMS vendors do whatever they need to get the magic
> number ... the one better than the competition.

Sure, but the point is that middleware (which is used in exactly the same way in the real world, not *just* for benchmarks) is crucial to get the best performance out of a DBMS, even in a benchmark that is tailored specifically to be DBMS-centric.

> But all of what you refer to as "protecting the DBMS from uncontrolled
> ad-hoc connections" as middle-ware is not valid on its face. You put up
> the best middleware tool you can and then give me SQL*Plus, MS Access,
> whatever ... and all of your protections are null and void. The
> middleware only protects from those connections routed through the
> middleware ... and it is for that reason specifically we see far too
> much data corruption.

Well, surely you know what I mean. All your DBMS-resident protections are also null and void if I get into your disk farm with a sledge hammer and a tape eraser... The point is that *when used as designed and intended* by official users, middleware can multiplex the work of thousands of users through a small, controlled, fully-used set of full-time DBMS connections. This saves the DBMS from polling and creating and closing thousands of per-user, lightly used connections, and it saves the clients from the relative slowness of making new real connections. I'm not saying that *DBMS* security shouldn't be in the DBMS, but other security is very well done in the middle tier too, where it can implement single-sign-on security across a set of applications and cross-DBMS access. It provides for unified encryption of communications etc.

>>> Try tuning all that rotten SQL coming from those fat front-ends
>>> sometime and you will understand why those here that have experience
>>> with PeopleSoft, SAP, Baan, and Siebel are remarkably unhappy.
>>
>> Well, sure. I have done so, in fact. The stupidities are legion. Many
>> of these
>> applications are ridiculous 'ports' of 70's era COBOL/ISAM
>> applications to
>> 'client-server' by simply swapping in row-by-row cursor fetches to the
>> DBMS
>> instead of the previous ISAM call. Intelligent client-server architecture
>> (as I've said) puts the sawmills where the trees are, not in the
>> client. However,
>> client-server is dead, at least to the extent that now everyone in the
>> company
>> or the internet may want the info in the DBMS(s), and transactions now
>> involve
>> serveral DBMSes and other resources. Thats where the middle tier makes
>> it's
>> contribution, and depending on what's done, and what degree of
>> concurrency,
>> isolation, atomicity etc that particular data requires, it can now find a
>> profitable home in either the DBMS or the middle tier, at least as a
>> cache.
>> As an example from the Baan suite, they really did architect their
>> application
>> so it queried the DBMS for a list of the countries in Europe hundreds
>> of times
>> a day for each user. A current state-of-the-art application would have
>> a generic
>> browser as one of it's clients, and in such a case, the middle tier is
>> ideal for
>> caching the list of the countries in Europe, and making is available
>> to all
>> clients, and given the political instability in Europe, maybe
>> refreshing it's
>> list once daily from the DBMS? It saves a lot of DBMS cycles...
>> IMHO...
>> Joe
>
> I'm not arguming the valid of your company's product or middleware.

Sure. You are clearly being honest and thoughtful. You aren't an Oracle bigot. We may both just be biassed in the other's eyes, me toward application servers, and you toward the DBMS. :)

> Only the fact that data security and integrity must be protected at the RDBMS
> level.

I agree, but would only add that there is need for *other* security and integrity, and even for such things as transactional integrity, the multiresource  nature of current state-of-the-business transactions requires external transaction coordinators to guarantee that integrity.

>That you may layer a little more on top is fine but anyone
> depending on it rather than the RDBMS is begging for problems.

We differ here 'a little' because I think there's *a lot* that can be offered and built and accomplished in that middle layer (in an application-serving layer). Depending on an external layer for what the DBMS is best at, *is* begging for problems. However, I believe there's a lot that the DBMS can't do well, and depending on the DBMS to do that stuff is also begging for problems, in performance to start.
To repeat the inarguable example, the DBMS can't even do the basic job it was designed to do by itself, in a test designed specifically to exercize a DBMS, as fast by itself, as it can when it uses an intelligent middle tier.

    We're focussing on our differences, but I am also a DBMS guy, and we would probably agree for the most part on specific functionality that should be in the DBMS.
Joe Weinstein at BEA Received on Wed Jan 21 2004 - 11:01:02 CST