db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George Anestis <gan...@ced.tuc.gr>
Subject Re: Performance Tuning Problem ?
Date Mon, 25 May 2009 12:17:02 GMT
Dean Knut,

Thanks for answer,

> The accessed data has higher locality in this query than in the previous
> query, and therefore the page cache is used more efficiently.
>
> The previous query used the indexes on big.name and bigref.bigid, which
> are ordered differently. So even if the index on big.name was scanned
> sequentially, the index on bigref.bigid was accessed almost completely
> randomly, and a large number of pages all over the table had to be
> visited.
>
> In the latest query, the indexes on big.id and bigref.bigid are
> used. Those indexes are ordered the same way, and all the interesting
> rows are located consecutively on a relatively small number of pages. So
> even if the number of page accesses is higher, the number of different
> pages accessed is probably much lower. And since the rows in both
> indexes are accessed in the same order that they are stored, the
> requested page is found in the page cache most of the time.
>
> 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 ?

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

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 ?

Anyway, I would not like to become (more :-)) annoying, thanks a lot for 
your answers.

Best regards,
George


Mime
View raw message