From: meh@atlantis.Unify.Com (Mark E. Hansen)
Newsgroups: comp.databases
Subject: Re: 500'000 records - who does best?
Date: 5 Jan 93 19:10:13 GMT
Organization: Unify Corporation, Sacramento, CA, USA

In article <0g3avp6@Unify.Com>, jde@Unify.com (Jeff Evarts) writes:
|> In article <brent.725766347@cc.gatech.edu>, brent@terminus.gatech.edu (Brent Laminack) writes:
|> > 
|> > >I believe that this thread started out as a "can anyone do this REASONABLY"
|> > 
|> > >UNIFY 2000 can handle this (500K rows @ 30K per row). If you have the disk space,
|> > >we have the database. :-)
|> > 
|> > >Mischa is right... its not the users, it's the size of the database. The
|> > >UNIFY 2000 database package can have individual table segments in the 16+Mb
|> > >category, allowing over 500 rows per segment, so the table would only require
|> > >10000 segments.
|> > 
|> > Is this the same Unify company that tells me to try to keep all tables
|> > to 16 or fewer segments? After that performance is said to degrade? If
|> > memory serves, 10000 >>> 16

Just a couple of nits:

First of all, the maximum segment size is 64 megabytes. Not 16 megabytes. 
The number of rows per segment would obviously be determined by the row size.


Additionally, the breaking point is 32 segments, not 16; and this is only 
a concern on Sequential scans, and inserts. Not for locating existing data,
as these should be using indexes of some sort, which will not require the 
data base to search for the data through the segments.
 
The 32 comes from the number of segment pointers that will fit in a segment map 
of one page (2k). If this has to increase, due to the number of segments that 
you need in order to maintain your data, then it will handle that.

The segment map can grow to as large as 64mb. This will allow the addressing
of a maximum of 1048576 segments/table.

This only becomes a serious performance problem when you have hundreds or
thousands of segments. I've seen some tables with over 8 thousand segments
when the segment size was only 2k. This is unreasonable. You should always try
to size the segments so that your expected *full* table will fit within 32 
segments.

If your table is large enough that it will overflow 32 64mb segments, then I 
think you can afford a larger segment map :)



|> 
|> Cute... but
|> 
|> Performance _can_ degrade by one _virtual_ read per row access IF:
|> 
|> 	1. You have more than 16 segments in a table, AND
|> 	2. You are short on ram, AND
|> 	3. The row requested is not in a recently used segment.
|> 
|> On a system wanting to keep 1.5TB of data
|> 
|> 	1. A lack of RAM is not likely.
|> 	2. Other latencies will overshadow the additional read.
|> 
|> So it should not be a problem.
|> 
|> > 
|> > >You would still have plenty of room for indices and the rest.
|> > 
|> > >Performance is impossible to judge without getting your hands a little dirty.
|> > >It depends on hardware, access patterns, indexing strategies, and a whole lot
|> > >more, but in general UNIFY 2000 is very hard to beat in the performance area.
|> > 
|> > There are other considerations, however. Ask Unify to do an outer join.
|> > They can't.
|> 
|> Hmmm. not _strictly_ true. There are many ways to do this in UNIFY, from within
|> SQL and out. We do not have a DIRECT mechanism to do this because we try to be
|> ANSI SQL compliant and ANSI SQL uses UNIONs to do what other database companies
|> use outer joins to do. So although we do not have a nifty way to do this directly,
|> you can get the job done (often more efficiently) by taking other routes.
|> 
|> > 
|> > No brag, just facts.
|> > 
|> > Brent Laminack (brent@cc.gatech.edu)
|> > 
|> > 
|> > 					
|> > 					-Brent Laminack (brent@cc.gatech.edu)
|> 
|> Really, though. The base conversation was "can anyone handle this", and
|> "can they handle it REASONABLY". The answer is still yes to both questions.
|> 
|> Disclaimer: I'm a techie, not a sales-ie
|> 
|> -Jeff Evarts
|> --jde@unify.com
|> ---#include <sys/disclaimer>

-- 
Mark E. Hansen                                     internet: meh@Unify.Com
Unify Corporation                           ...!{csusac,pyramid}!unify!meh
3901 Lennane Drive                                   voice: (916) 928-6234
Sacramento, CA 95834-1922                              fax: (916) 928-6401

          ** If there is no solution, there is no problem **