db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sunitha Kambhampati <ksunitha...@gmail.com>
Subject Re: derby performance and 'order by'
Date Sat, 17 Sep 2005 00:55:20 GMT
Scott Ogden wrote:

> I have observed some interesting query performance behavior and am 
> hoping someone here can explain.
>
> In my scenario, it appears that an existing index is not being used 
> for the ‘order by’ part of the operation and as a result the 
> performance of certain queries is suffering.
>
> Can someone explain if this is supposed to be what is happening and 
> why? Please see below for the specific queries and their performance 
> characteristics.
>
> Here are the particulars:
>
> ---------------------------------
>
> create table orders(
>
> order_id varchar(50) NOT NULL
>
> CONSTRAINT ORDERS_PK PRIMARY KEY,
>
> amount numeric(31,2),
>
> time date,
>
> inv_num varchar(50),
>
> line_num varchar(50),
>
> phone varchar(50),
>
> prod_num varchar(50));
>
> --Load a large amount of data (720,000 records) into the ‘orders’ table
>
> --Create an index on the time column as that will be used in the 
> ‘where’ clause.
>
> create index IX_ORDERS_TIME on orders(time);
>
> --When I run a query against this table returning top 1,000 records, 
> this query returns very quickly, consistently less than .010 seconds.
>
> select * from orders
>
> where time > '10/01/2002' and time < '11/30/2002'
>
> order by time;
>
> --Now run a similarly query against same table, returning the top 
> 1,000 records.
>
> --The difference is that the results are now sorted by the primary key 
> (‘order_id’) rather than ‘time’.
>
> --This query returns slowly, approximately 15 seconds. Why??
>
> select * from orders
>
> where time > '10/01/2002' and time < '11/30/2002'
>
> order by order_id;
>
> --Now run a third query against the same ‘orders’ table, removing the 
> where clause
>
> --This query returns quickly, around .010 seconds.
>
> select * from orders
>
> order by order_id;
>
> ---------------------------------------------
>
If you run with derby.language.logQueryPlan=true, the actual query plans 
used for the following queries will be written to derby.log. This will 
show what indexes was used by the optimizer. Also see 
http://db.apache.org/derby/docs/10.1/tuning/rtunproper43414.html .

Query with 'order by' will require sorting. Usually, sorting requires an 
extra step to put the data into the right order. This extra step can be 
avoided for data that are already in the right order. For example, if a 
single-table query has an ORDER BY on a single column, and there is an 
index on that column, sorting can be avoided if Derby uses the index as 
the access path.

I think in case of your first and third query the optimizer will pick 
the available index thus probably avoiding requiring the sort step.

Your second query involves more work than the first query, since it has 
a search condition on time, and an order by order_id. Thus if the 
optimizer picks the index on time, that will involve a sort step on 
order_id.
____________

Thanks,
Sunitha.


Mime
View raw message