Re: slow query? (from a practical newbie)

[Bryan Pendleton]

On 1/7/2014 12:52 PM, degenaro wrote:

Bryan Pendleton-3 wrote

Are the queries that we're discussing here running slowly for you?


Yes, very slow.  With about 50,000 rows the query takes about 30 seconds
give or take.  But if I remove the where clause, the time is sub second.


Wow, that's crazy! That query should take about .03 seconds, not 30!

What happens if you change the query from

SELECT * FROM ...
to
SELECT COUNT(*) FROM ...

That is, select the count rather than the actual rows.

Also, instead of

SELECT * FROM ...
try
SELECT ID FROM ...

In both cases, I'm wondering if somehow the problem is in the output
phase, like there's a huge amount of output because there's a BLOB
column in your table or something like that, or the client side is
just taking an eternity to process the output.

Because the query plan indicates that you're just reading 57 pages,
and 50K rows, and that's nothing that should even cause Derby to
break a sweat.

thanks,

bryan

Re: slow query? (from a practical newbie)

[degenaro]

Bryan Pendleton-3 wrote
> Are the queries that we're discussing here running slowly for you?

Yes, very slow.  With about 50,000 rows the query takes about 30 seconds
give or take.  But if I remove the where clause, the time is sub second.

Lou.



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

Re: slow query? (from a practical newbie)

[Bryan Pendleton]

So I tried:

ij> select * from ducc.Job where id < 117000 order by stateIndex asc, id desc;


You're right; the ORDER BY has to be the final clause, not before the WHERE.
Thanks for correcting that.


And I see this query plan:


OK, I'm afraid I've forgotten the overall context. This query plan
looks just like the previous query plan, and in both cases they appear
to be using the index to restrict the number of rows searched.

Are the queries that we're discussing here running slowly for you?

According to the query plan, they're running quite efficiently.

thanks,

bryan

Re: slow query? (from a practical newbie)

[degenaro]

ij> select * from ducc.Job order by stateIndex asc, id desc where id <
117000;
ERROR 42X01: Syntax error: Encountered "where" at line 1, column 57.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed
directly.
Consult your DBMS server reference documentation for details of the SQL
syntax supported by your server.

So I tried:

ij> select * from ducc.Job where id < 117000 order by stateIndex asc, id
desc;

And I see this query plan:

ue Jan 07 09:06:12 EST 2014 Thread[main,5,main] (XID = 10483), (SESSIONID =
1), select * from ducc.Job where id < 117000 order by stateIndex asc, id
desc *** 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, 1, 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 instantaneous share row locking
chosen by the optimizer
Number of opens = 1
Rows seen = 5468
Rows filtered = 0
Fetch Size = 16
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






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