What Database Best Supports Quick Retrieval Of One Child Table

[Will Estes]

I have an application that has a parent record and up to ten million
child records. I am guaranteed that for this application there will
never be relational joins against other tables. In theory, which kind
of database would best support these requirements, and which
commercial product best instantiates the theory. The requirements
are:

- Must be able to very quickly create a new database and copy the one
parent record and its ten million child records into the new database

- Must be save against multiple-thread access (dozens of threads might
be active against the same database at any one time)

- Must be very fast for random access for read/write operations
against individual child records

- Locks on the child records must not lock the parent record or any
other child (i.e., record-level locks, not page locks)

Is a relational database the best kind for this sort of access?
Which commercial database product will support these requirements?

--
Will Estes U.S. Computer
Internet: wes...@usc.com POB 3150
Saratoga, CA 95070
FAX: 408-446-1013

[Joe Weinstein]

Will Estes wrote:
>
big snip...

Random access speed to children will be obtained by a suitable index
built for the child table. Any multi-user DBMS will have this
capability.

> - Locks on the child records must not lock the parent record or any
> other child (i.e., record-level locks, not page locks)
>

This is a dangerous requirement. You probably mean that other
threads can still read a parent while any of it's children are
being modified. You *don't* want anyone to be able to modify
the parent, at least the primary key value, while children are
in flux. In fact there is much to be considered about what you
want to enforce when a parent with children is changed or deleted.
Any row-level-locking DBMS will not lock non-involved children.

> Is a relational database the best kind for this sort of access?
> Which commercial database product will support these requirements?

The stated demands are very simple for any multi-user DBMS, relational
or otherwise.

[Chester West II]

Not to sound biased or anything, but I personally like Oracle. I
think before you make any decisions, you will need to come up with a
few more requirements.... For example, what type of scalability is
needed? Do you prefer a specific platform for the OS & HW, and how do
the commercial RDBMSes rate on it? Do youhave a need for spatial data
now or in the future? What languages do your developers know now and
how do they compare to the commercial RDBMSes native languages in
syntax, capabilities and style? Would you prefer a relational or OO
database? ... also, a list of restrictions... for example, The DB
server must run on Windows NT or Novell. We can't spend bu
$10,000.00. We already have expertise in... and best of all, We need
to be able to integrate with...

Chet West
Tactics, Inc. (Oracle BAP)
http://www.tacticsus.com

wes...@usc.com (Will Estes) wrote:

>I have an application that has a parent record and up to ten million
>child records. I am guaranteed that for this application there will
>never be relational joins against other tables. In theory, which kind
>of database would best support these requirements, and which
>commercial product best instantiates the theory. The requirements
>are:

>- Must be able to very quickly create a new database and copy the one
>parent record and its ten million child records into the new database

>- Must be save against multiple-thread access (dozens of threads might
>be active against the same database at any one time)

>- Must be very fast for random access for read/write operations
>against individual child records

>- Locks on the child records must not lock the parent record or any

>other child (i.e., record-level locks, not page locks)

>Is a relational database the best kind for this sort of access?

>Which commercial database product will support these requirements?

>--

[John Caputo]

Will Estes wrote:
>
> I have an application that has a parent record and up to ten million
> child records. I am guaranteed that for this application there will
> never be relational joins against other tables. In theory, which kind
> of database would best support these requirements, and which
> commercial product best instantiates the theory. The requirements
> are:
>
> - Must be able to very quickly create a new database and copy the one
> parent record and its ten million child records into the new database
>
> - Must be save against multiple-thread access (dozens of threads might
> be active against the same database at any one time)
>
> - Must be very fast for random access for read/write operations
> against individual child records
>
> - Locks on the child records must not lock the parent record or any
> other child (i.e., record-level locks, not page locks)
>
> Is a relational database the best kind for this sort of access?
> Which commercial database product will support these requirements?
>
> --
> Will Estes U.S. Computer
> Internet: wes...@usc.com POB 3150
> Saratoga, CA 95070
> FAX: 408-446-1013

You live and work in Silicon Valley and you have to post this question
in a newsgroup? You'll get as many good responses by rolling down your
window at the next stop light and asking somebody. Or maybe lean over to
the person next to you in MacDonald's the next time you're there.

John