Re: theory and practice: ying and yang
Date: Tue, 31 May 2005 11:27:15 +0100
Message-ID: <429c3c03$0$553$ed2619ec_at_ptn-nntp-reader03.plus.net>
mountain man wrote:
> My thesis is that if you take this migration to its logical
> conclusion there may be achievable an optimal stage in which *all*
> application software in internal to the DBMS in the form of stored
> procedures.
>
> That is precisely what this tool does. It enables a service level
> portal to the RDBMS for the user which is based solely on the
> execution of stored procedures, and the ability to handle the
> consequent sets of data being returned to the user.
Where does the GUI fit into all of this? Even if the DBMS package is bundled with a graphical frontend (e.g. Enterprise Manager for SQL Server), it's logically separate from the DBMS itself.
>> Isn't what you've done standard practice in designing enterprise >> database reporting systems? i.e. to have a stored procedure (or in >> an ideal world, a view) for each report?
>
> Yes. However, the tool enables dynamic drill-down from summary to
> intermediate summary to detail to log levels on the simple basis that
> one stored procedure can be called from another. Therefore there
> are a series of stored procedures for each report, dependent upon the
> analytical solution being provided.
Why stored procedures and not views? Views can be nested just as well, if not more easily. In practice I use stored procedurs as well, but only as a workaround for optimizer inefficiencies. In an ideal DBMS would you use views?
>> Are you saying that this method is preferrable to the "fat client" >> or "middleware" method, where you have business logic held outside >> the database in things like Cognos, Business Objects, or Crystal >> Reports? I thought this was pretty much the same as most other >> people think.
>
> Absolutely preferable, because it entirely OBVIATES the client. End
> of story. There is no client required, and therefore no middleware
> required, at all. Minimal number of moving parts in that all the
> code and data are being defined once and for all in ONE SOFTWARE
> LAYER.
Well you have to have some sort of client, surely, because the whole way that DBMSs work is by having a central server that holds the data and multiple clients requesting information from it (whether via raw SQL or via stored procedures, and whether the clients sit on the same physical machine or on remote machines). I have to talk to the database somehow, and that requires a client, however thin.
> This is financially preferable because the change management of
> multiple software layers is an ongoing huge expense, and really all
> that is being done is the coordination of schema-related tasks
> between the database and anything external to it (ie: the application
> layer).
OK, that sounds reasonable. But don't you still have the problem that if the table designs change, you have to coordinate the corresponding changes in all the stored procedures that access those tables? Granted, they're now both in the same language, SQL, but it's still an issue.
>> Is the point you're making that the procedural language used in >> stored procedures isn't part of the relational model?
>
> Not in the least, I believe that SQL is computationally complete and
> there is in fact no problem -- however difficult -- that cannot be
> overcome using SQL and other native utilities that are available to
> the current industry RDBMS machinery.
How can SQL talk to a remote machine to draw a window on the screen and populate it with controls? Surely it requires drivers to enable clients to perform these functions?
>> In which case all the stored procedures you have could be replaced >> with views, which are part of the relational model. This approach >> would only break down for the cases I mentioned before i.e. when >> you have several data manipulation statements in a single >> transaction. But I don't see any of these cases in your example >> application.
>
> The dynamic drill-down information reporting obtained by chaining
> multiple series of stored procedures together cannot be replaced by a
> view.
I don't quite understand what you mean by "drill-down" and I can't find references to this on your site. Surely you can have one view that provides detailed information, then a second that aggregates the first view some more? Are you talking about returning multiple result sets in a single go?
> You dont see any views in my example because I have never yet found
> an instance where a view is necessarily a better implementation that
> by performing the task without use of a view.
>
> Theoretical limitations about SQL and Views by Date et al are not
> limitations at all from my perspective, because views are not
> essential. End of story. ;-)
I think views are nicer from the end-user point of view, but I suspect they are a lot more difficult to implement from the point of view of the DBMS designer, being declarative rather than procedural.
Paul. Received on Tue May 31 2005 - 12:27:15 CEST