RE: Possible dB performance increase

["Creager, Robert S" <CreagRS@LOUISVILLE.STORTEK.COM>]

I better understand the speed problem now...  I'm going to think out loud,
so stop me if you've thought of this.  If the stars are 'matched' against
the master catalog before importation, you could put in a TRIGGER which
updated a field in the master photometry indicating a recalc of that star
data was necessary.  On a weekly/daily/hourly basis, the database could be
swept for stars which need updated calculations done.  If it would be
desirable that all current out-of-date photometric stars be updated at once,
transactions could be use while re-calculating the data, and imports could
be locked to prevent problems.  But none of this would speed up the import
process...

How much of a problem is the speed of the imports with indexes in place?
Can the system the dB is currently on keep up with the data as it comes out
of the reduction pipelines?  What CPU, etc is the dB running on?

I'm asking all these question because I have access to an Oracle goddess who
works for Sun.  She is willing to give pointers, but I need to understand
what the problems are to ask and carry on an intelligent conversation with
her.  She works with, among other things, a 1.5Tb contracts dB, and her card
from Sun actually reads "... and Oracle DBA goddess"

Robert Creager
Senior Software Engineer
Client Server Library
303.673.2365 V
303.661.5379 F
888.912.4458 P
StorageTek
INFORMATION made POWERFUL



> -----Original Message-----
> From: Albertson, Chris [mailto:CAlbertson@primeadvantage.com]
> Sent: Tuesday, February 20, 2001 4:30 PM
> To: Creager, Robert S; 'Tass Mailing List'
> Subject: RE: Possible DB performance increase
> 
> 
> 
> When I said "slow", query speed wasn't the problem.  As you
> say query speed can be helped be "tuning" a bit.  You can
> build indexes to help specific queries.  As many indexes as
> required.  You can even build an index on a _function_ of multiple
> columns such as the difference of MagV and MagI.  Searches on
> Mag[V-I] would then be very fast.  There is no end to the number
> of special purpose indexes you can build.  If you have the space
> to hold the indexes, go for it.
> 
> What was slow was _building_ the database from the star lists.
> In this case Index file slow you down.  Every time you add more
> data all of the existing indexes are updated.  The more indexes
> you have the slower goes the build.  So, my software "drops"
> all the indexes before importing data.  Even so, importing the
> star lists was to slow by a large factor.
> 
> The problem is that every time you add a photometric observation
> to the database, you have to figure out which catalog star was
> observed.  If the observed star is not in the catalog then you
> must add a new entry to the catalog.  It gets tricky with blends
> that sometimes resolve and sometimes don't.  Also if you add 
> non-matches to your catalog, over time the catalog fills with
> "junk" that must be cleared out.  
> 
> The other thing my software does that IMO is unacceptable is to
> completely re-build the whole database whenever new data is added.
> We can't do this for the Mk IV.  I want to keep the data on-line
> and accessible to the Internet while at the same time new data
> are added and indexes updated.
> 
> I think the solution is to off load the catalog lookups to computers
> other then the main database computer.  The lookups should be done
> at the camera sites before the data are sent.  A catalog lookup is
> a job that scales well when you add more computers to the job.
>