db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Performance Tuning Problem ?
Date Mon, 25 May 2009 12:46:47 GMT
George Anestis <ganest@ced.tuc.gr> writes:

>> Another difference is that the previous query had to access the base
>> table (big) to get the id column, whereas the latest query finds all the
>> columns it needs in the indexes. You may want to try to add an extra
>> index to speed up the previous query:
>>
>>   CREATE UNIQUE INDEX BIGNAMEID ON BIG(NAME, ID)
>>
>>   
> If I understand well, the creation of the new index (BIGNAMEID) will
> solve the second problem you mentioned,
> the id column of table big will be taken from the index BIGNAMEID and
> not from the base table, but the first issue
>
> "the index on bigref.bigid was accessed almost completely
> randomly, and a large number of pages all over the table had to be
> visited."
>
>
> will remain? Is than correct ?

Correct.

> For the record, the creation of the index you proposed speeds up the
> query in a spectacular way !!

That's great! :)

> Is it correct to assume that the main source of the problem was the
> access of the base table (big) to get the id
> column and not the almost random access of the index on bigref.bigid ?

Yes, that sounds reasonable. The accesses to the base table are random
too, since the base table and the BIGNAME index are ordered
differently. The index on BIGREF.BIGID is much more compact than the BIG
table, so the random reads there probably use the cache more efficiently
and don't hurt the performance that much.

-- 
Knut Anders

Mime
View raw message