Re: Client/Server query strategy

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:
>I need some opinions on how SQL front end tools should be used against
>an Oracle 7 database.
>
>We're doing a data warehouse project to enable end users (not
>programmers) to do ad-hoc queries. A number of people in the I.S.
>department are suggesting that we select a query tool that allows us to
>do all of the sorting and data aggregation (sums, avgs, etc.) entirely
>on the client side. That is, if you want to summarize large amounts of
>data down to a dozen or so rows, perhaps dollar totals and counts by
>some code, that we download the entire set of pertinent detail rows to
>the PC client and let the client do all of the sorting and totaling.

Really?

How big did you say the tables are you need to deal with?

With (literally) millions of rows of data, they want you to summarize a database table by shipping EVERY row across the network to a user's PC, then grind for (hours?, days?, weeks?) to perform the summarization and display? Times how many users? Can you say, "Network meltdown?" Can you say, "DUMB IDEA!"

I thought you could!

All facetiousness aside, I cannot conceive of any good reason why your IS people recommend that approach..

A typical large database runs on hefty iron (large midrange or mainframe) that can perform many times more "real" operations per unit time that even a "fast" PC (don't be fooled by mere "MIPS" numbers - they can be REALLY misleading). Doing the summarization within the database engine might  require only a small increment in CPU cycles over simple retrieval whereas the PC is going to have to receive the data, parse it and then process it, at PC speeds. No win there, either.

Although individual situations may vary, the best rule of thumb is to do as much processing as **feasible** on the big iron and then let the PC's do local manipulations or the result set only. If you have a large number of users, and have common queries that might result in a common result set over an extended time period (say the data only changes once a day or once a "shift") you might even want to consider "precalculating" the common result set immediately after the data has been updated and placing THAT where the uses can get at it. Yes, I know you're not supposed to have calculated/derived data stored in the database but under many real-world circumstances, you have to do what you have to do to prevent having the system be unusably slow.

Just my $0.02 worth.

--
Bill Meahan,  Senior Developer  |        wmeahan_at_ef0424.efhd.ford.com
Electrical & Fuel Handling Division, Ford Motor Company
Opions expressed herein are those of the author and in no way represent
any official statement or opinion of Ford Motor Company