Re: Speed of locating tables?

Cary O'Brien wrote:
>
> Steve Wampler wrote:
> > Thanks! I'm also open to suggestions on other ways to
> > represent the data that would provide better access
> > performance - you can probably tell I'm new to the world of
> > databases.
> >
>
> I wouldn't want a database with 100,000 tables. How about
> something like this...
>
> create table run_time_parameters (
> id integer,
> name varchar,
> value varchar,
> ...
> )
>
> create index run_time_parameters on run_time_parameters(id);
>
> ... insert data ...
>
> vacuum analyze run_time_parameters;

This sounds promising. Most of the insertions will take place in the
morning (before sunrise) so I should have time to do a vacuum analyze
at that point. However some insertions will take place after that, while
the database is being accessed. I assume that the vacuum analyze done
earlier (on 90% of the total insertions) will still be a net win? I'll
have to test this.

> One final point. You have a non-trivial performance requirement.
> You should probably set up a simple working prototype and verify
> that you can get the performance you need using tables that are
> the size you expect to handle. No sense in having any suprises
> when you roll out your real system. Scripting languages like
> TCL and Python are great for generating data and running tests.

I'm in that stage now, but want to narrow down the number of possible
solutions to try out... For example, I've thought about having one
row per configuration (id, attributeSet) and manage the access of
individual attributes outside the database. That seems clumsy to
me, however.

> Feel free to ask me if you have any more questions.

Thanks! Most helpful!
--
Steve Wampler- SOLIS Project, National Solar Observatory
swampler(at)noao(dot)edu