home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases
- Path: sparky!uunet!haven.umd.edu!darwin.sura.net!spool.mu.edu!agate!rsoft!mindlink!a269
- From: Mischa_Sandberg@mindlink.bc.ca (Mischa Sandberg)
- Subject: Re: 500'000 records - who does best?
- Organization: MIND LINK! - British Columbia, Canada
- Date: Sat, 9 Jan 1993 17:18:19 GMT
- Message-ID: <19432@mindlink.bc.ca>
- Sender: news@deep.rsoft.bc.ca (Usenet)
- Lines: 72
-
- > Ben Polk writes:
- > 900k rows, 250Mb? That's not big for Sybase on an RS/6000, unless
- > you find yourself scanning tables... I've talked to people that
- > start to run into data load and index performance problems when
- > their databases reach the multi-gigabyte range, but the query
- > performance is still good even up to 10Gb. Loading a 10Gb database
- > at 250Mb per hour is the problem.
- I may be repeating what you've already read, but here goes:
- my company's clients have a "volatile" business: frequent database
- restructuring, reloading, adapting. "Transactions" amount to bcp'ing
- 5-40k rows into a scratch table, validating and altering it then
- applying it to a main table or two. Single-row updates and single-row
- queries never happen.
-
- We also have very limited time windows in which to work. Typically,
- 2-4 hours to process several such transactions then emit a
- "net change to database" transaction which is replicated/split
- into relevant slices for 200-700 remote databases that maintain
- partial views of the central one.
-
- Other than those transactions, our database users are query-intensive.
- Most queries use all rows of a given table, given that searches tend
- to be data-selective rather than key-selective; but even where indexes
- are usable, we frequently get queries that hit the time limit (about
- 10 minutes on a system with 10 interactive users performing 10
- queries per day, within a four-hour window). One simple form of query
- is such an incredible dog, we've taken it outside the Sybase database
- and process it using a multipage-indexed Unix file:
- "Given 10000 customers buying from a range of 2000 products
- (typical), give me a list of which are the top ten products for
- each customer, according to sales."
- If you have some insight into processing this query efficiently,
- I'd be happy to here it. It's what I mean by a data-selective query.
-
- I'd agree that loading a 10Gb database with BCP would not be a problem,
- done once.
-
- Unfortunately, there are foreign-key validations and summary-updating
- functions associated with each "load" in our world. As far as I can
- tell, we have optimal indexes, though in a couple of places I've had
- to force the query plan --- yes, *after* determining that the query
- optimizer was intent on choosing a silly plan when left to its own
- devices.
-
- On a Sun, we can pull a few tricks that tend to bring a table into
- contiguous physical disk cylinders; under AIX, no such options.
- >
- > Mischa, one thing you should check: if you have Sybase 4.2, you should
- > never give it over 32Mb of memory, as performance will DEGRADE
- > as you feed it more memory than this. Sybase 4.9.1 is supposed
- > to fix this.
- Thanks, yes, I was aware of this -- we lived through it on one
- of our Sun SPARC systems with 80Mb. Since 4.8 on the Suns fixed that
- problem, I certainly hope 4.9 didn't *lose* it :-).
-
- Your comment on the TPC benchmarks was something the original poster
- ought to know; you didn't see me quoting such benchmarks, did you?
- Both the original poster and I seem to have applications to which
- that benchmark has no relevance at all, it appears.
-
- Thanks for the comment. Given what I've said above do you still feel
- strongly confident that a mid/high-range RS6000 would have no problem
- with that? I'd appreciate being able to better understand what your
- 10Gb database looks like and does.
-
- --
- Mischa Sandberg ... Mischa_Sandberg@mindlink.bc.ca
- or uunet!van-bc!rsoft!mindlink!Mischa_Sandberg
- *-*-*-*-*-*-*-*-*-*-*
- Engineers think equations are an approximation of reality.
- Physicists think reality is an approximation of the equations.
- Mathematicians never make the connection.
-