Re: slow query? (from a practical newbie)
Bryan Pendleton-3 wrote > 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. I can try, but I doubt the volume of data is the issue since the where clause lessens the amount produced. Lou. -- View this message in context: http://apache-database.10148.n7.nabble.com/slow-query-from-a-practical-newbie-tp136285p136343.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: slow query? (from a practical newbie)
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)
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)
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)
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.
Re: slow query? (from a practical newbie)
select a.* from (select * from ducc.job order by stateIndex asc, id desc) a where id < 117000 Why is this query written like this, as opposed to, say: select * from ducc.job order by stateIndex asc, id desc where id < 117000 Do you get a different query plan / run time if you use a single top-level query and eliminate the sub-query? I think the optimizer does a pretty good job in general of optimizing sub queries but maybe the presence of the ORDER BY in the sub query is making it hard for the query optimizer to perform the query efficiently? thanks, bryan
Re: slow query? (from a practical newbie)
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.
Re: slow query? (from a practical newbie)
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
slow query? (from a practical newbie)
My table: Job ( primaryKey int primary key generated always as identity, id int not null, startDate bigint not null, endDate bigint not null, duration int not null, userId varchar( 64 ) not null, submitter varchar( 64 ) not null, schedulingClass varchar( 64 ) not null, debugPortDriver int not null, debugPortProcess int not null, state varchar( 64 ) not null, stateIndex int not null, completionType varchar( 64 ) not null, completionRationale varchar( 1024 ) not null, services int not null, processes int not null, initFails int not null, runFails int not null, pgin int not null, swapGbMax double not null, memorySizeGb int not null, workItemsTotal int not null, workItemsDone int not null, workItemsError int not null, workItemsRetry int not null, workItemsPreempt int not null, workItemsStatMax int not null, workItemsStatMin int not null, workItemsStatMean int not null, workItemsStatStdDev int not null, description varchar( 4096 ) not null, schedulingSharesMax int not null, logDirectory varchar( 4096 ) not null, jsonServiceDependencies clob not null, jsonJobDeployment clob not null, projection bigint not null, swapGbCurrent double not null, workItemsDispatch int not null, constraint uniqueColumns unique ( id ) ) = My indexes: CREATE INDEX id_desc ON Job (id desc) CREATE INDEX stateIndex_asc_id_desc ON Job (stateIndex asc, id desc) = Query #1 - fast! meaning sub-second using prepared statement select * from Job order by stateIndex asc, id desc offset ? rows fetch next ? rows only = Query #2 - slow :-( meaning on the order of 30 seconds on a table with 55,000 records select * from Job where id < ? order by stateIndex asc, id desc offset ? rows fetch next ? rows only = Ostensibly the where clause really slow does the query. How come and how do I remedy? Thanx! Lou.