db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From degenaro <lou.degen...@gmail.com>
Subject Re: slow query? (from a practical newbie)
Date Mon, 06 Jan 2014 14:54:07 GMT
Bryan Pendleton-3 wrote
>> Ostensibly the where clause really slow does the query.  How come and how
>> do I remedy?
> 
> Here's a good place to start:
> 
> http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
> 
> thanks,
> 
> bryan

>From ij show indexes:

DUCC                |JOB                 |PRIMARYKEY          |false |3   |A  
|NULL    |NULL
DUCC                |JOB                 |ID                  |false |3   |A  
|NULL    |NULL
DUCC                |JOB                 |ID                  |true  |3   |D  
|NULL    |NULL
DUCC                |JOB                 |STATEINDEX          |true  |3   |A  
|NULL    |NULL
DUCC                |JOB                 |ID                  |true  |3   |D  
|NULL    |NULL

from derby log:

Mon Jan 06 09:22:10 EST 2014 Thread[main,5,main] (XID = 10384), (SESSIONID =
1), select a.* from (select * from ducc.job order by stateIndex asc, id
desc) a where id < 117000 ******* Sort
ResultSet:
Number of opens = 1
Rows input = 5468
Rows returned = 5468
Eliminate duplicates = false
In sorted order = false
Sort information:
        Number of rows input=5468
        Number of rows output=5468
        Sort type=internal
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        optimizer estimated row count: 5453.00
        optimizer estimated cost: 426177.87
Source result set:
        Index Row to Base Row ResultSet for JOB:
        Number of opens = 1
        Rows seen = 5468
        Columns accessed from heap = {0, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
32, 33, 34, 35, 36, 37}
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                optimizer estimated row count: 5453.00
                optimizer estimated cost: 426177.87
                Index Scan ResultSet for JOB using constraint UNIQUECOLUMNS
at read committed isolation level using share row locking chosen by the
optimizer
                Number of opens = 1
                Rows seen = 5468
                Rows filtered = 0
                Fetch Size = 1
                        constructor time (milliseconds) = 0
                        open time (milliseconds) = 0
                        next time (milliseconds) = 0
                        close time (milliseconds) = 0
                        next time in milliseconds/row = 0

                scan information:
                        Bit set of columns fetched=All
                        Number of columns fetched=2
                        Number of deleted rows visited=0
                        Number of pages visited=57
                        Number of rows qualified=5468
                        Number of rows visited=5469
                        Scan type=btree
                        Tree height=2
                        start position:
                                None
                        stop position:
                                >= on first 1 column(s).
                                Ordered null semantics on the following
columns:
                                0
                        qualifiers:
                                None
                        optimizer estimated row count: 5453.00
                        optimizer estimated cost: 426177.87

As best as I can tell from this, for this query the composite index
(stateIndex asc, id desc) is not used, which is why the query is so slow?  

If there is an example or strategies for how to accomplish "complex"
queries?  There are 2 columns of interest: id and stateIndex.  The desired
ordering is based upon the combined (stateIndex asc, id desc) and the
starting row is to be a specified id (e.g. the where clause).

Thanks.

Lou.





--
View this message in context: http://apache-database.10148.n7.nabble.com/slow-query-from-a-practical-newbie-tp136285p136295.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Mime
View raw message