Re: its really SLOW!!!!!

On Mon, Dec 02, 2002 at 05:11:45PM -0500, Adler, Stephen wrote:
> create master (
> masterindex integer not null primary key
> );
>
> create magnet (
> masterindex integer,
> current integer,
> voltage integer
> );
>
> insert into master values (1);
> insert into master values (1);
> insert into magnet values (1, 100, 100);
> insert into magnet values (1, 102, 99);
> insert into magnet values (1, 99,100);
> insert into magnet values (2, 100, 101);
> insert into magnet values (2, 99,103);
> insert into magnet values (2, 100, 99);
>
> The idea being that I have a group of values which
> are related to the master index. Here I have 2 records
> in my master table, and 3 groups of values for each
> master record. The select goes like this
>
> select * from magnet where masterindex=1;
> select * from magnet where masterindex=2;
>
> These look ups are very slow. One thing I should remind
> you guys, is that the master table has over 50,000,
> the magnet table has about 6 times that many entries,
> 6 entries for each master record, unlike the example
> above where I only have 3 entries per record. What I'm
> wondering is that I need to make masterindex a real
> index. The idea of course is that I want to do a select
> on the master table with a join with the magnet table
> so that I pull out a list of magnet current and voltage
> settings for each master record. But right now I'm just
> doing the queries on the magnet table.

Stephen --

What, exactly, is your query? Are you running 50,000 statements in a row
like "SELECT * FROM magnet WHERE masterindex=[x]"? Do you need all this
raw data? Or are you trying to aggregate it or summarize it?

Are you trying to get

index current1 current2 current3 voltage1 voltage2 voltage3
1 100 101 105 100 95 94
2 99 102 102 99 101 95

(for example, made-up data)

or what?

What indexes are on your tables? Definitely, magnet.masterindex should
be indexed.

--

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant