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

> 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.
Newsgroups: comp.databases