Hello,
I would like my query below to return within 100 millisecs. Please help me,
and the values for the IN clause comes from outside hence cannot really
change the IN clause to a join on an existing table.
time taken to prepare statement = 0
time taken execute statement = 33375
time taken enumerate result set = 63
DDLs:
CREATE TABLE APP.OBJECT_MASTER (
OBJECT_ID INTEGER NOT NULL,
OBJECT_CUID VARCHAR(32) NOT NULL,
PRIMARY KEY (OBJECT_ID)
);
CREATE TABLE APP.OBJECT_CATEGORY_MAPPING (
OBJECT_ID INTEGER NOT NULL,
CATEGORY_ID INTEGER NOT NULL
);
CREATE TABLE APP.CATEGORY_MASTER (
CATEGORY_ID INTEGER NOT NULL,
CATEGORY_NAME VARCHAR(255) NOT NULL,
PARENT_ID INTEGER,
PRIMARY KEY (CATEGORY_ID)
);
CREATE INDEX APP.OBJECT_MASTER_INDEX ON APP.OBJECT_MASTER
(OBJECT_ID,OBJECT_CUID)
CREATE INDEX APP.OBJECT_CATEGORY_MAPPING_INDEX ON
APP.OBJECT_CATEGORY_MAPPING (OBJECT_ID,CATEGORY_ID)
CREATE INDEX APP.CATEGORY_MASTER_INDEX ON APP.CATEGORY_MASTER
(CATEGORY_ID,CATEGORY_NAME)
Data inside the tables:
OBJECT_MASTER = 1,000,000 rows
CATEGORY_MASTER = 10,000 rows
OBJECT_CATEGORY_MAPPING = 20,000,000 rows
SYSCS_GET_RUNTIMESTATISTICS() output:
Statement Name:
null
Statement Text:
select category_master.category_name,
count(category_master.category_name) as category_count from object_master,
category_master, object_category_mapping where
object_master.object_id = object_category_mapping.object_id and
object_category_mapping.category_id = category_master.category_id and
object_master.object_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )
group by category_master.category_name order by category_count
desc
Parse Time: 94
Bind Time: 31
Optimize Time: 125
Generate Time: 94
Compile Time: 344
Execute Time: 33235
Begin Compilation Timestamp : 2009-04-07 16:03:55.859
End Compilation Timestamp : 2009-04-07 16:03:56.203
Begin Execution Timestamp : 2009-04-07 16:03:56.296
End Execution Timestamp : 2009-04-07 16:05:36.515
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 1068
Rows returned = 1068
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=1068
Number of rows output=1068
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 33235
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 18811881.00
optimizer estimated cost: 95276340.92
Source result set:
Project-Restrict ResultSet (10):
Number of opens = 1
Rows seen = 1068
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 33172
next time (milliseconds) = 47
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 18811881.00
optimizer estimated cost: 95276340.92
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 19000
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of merge runs=1
Number of rows input=19000
Number of rows output=1084
Size of merge runs=[18220]
Sort type=external
constructor time (milliseconds) = 0
open time (milliseconds) = 33172
next time (milliseconds) = 47
close time (milliseconds) = 0
optimizer estimated row count: 18811881.00
optimizer estimated cost: 95276340.92
Source result set:
Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 19000
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 33001
close time (milliseconds) = 16
restriction time (milliseconds) = 0
projection time (milliseconds) = 16
optimizer estimated row count: 18811881.00
optimizer estimated cost: 95276340.92
Source result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 19000
Rows seen from the right = 19000
Rows filtered = 0
Rows returned = 19000
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 32954
close time (milliseconds) = 16
optimizer estimated row count: 18811881.00
optimizer estimated cost: 95276340.92
Left result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 1000
Rows seen from the right = 19000
Rows filtered = 0
Rows returned = 19000
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 32209
close time (milliseconds) = 16
optimizer estimated row count: 18811881.00
optimizer estimated cost: 4772381.42
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 104896
Rows filtered = 103896
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 31769
close time (milliseconds) = 16
restriction time (milliseconds) = 30628
projection time (milliseconds) = 0
optimizer estimated row count: 297031.20
optimizer estimated cost: 63997.96
Source result set:
Index Scan ResultSet for OBJECT_MASTER using
constraint SQL090406104857810 at read committed isolation level using share
row locking chosen by the optimizer
Number of opens = 1
Rows seen = 104896
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 31
next time (milliseconds) = 1126
close time (milliseconds) = 16
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=566
Number of rows qualified=104896
Number of rows visited=104897
Scan type=btree
Tree height=3
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count:
297031.20
optimizer estimated cost: 63997.96
Right result set:
Index Scan ResultSet for OBJECT_CATEGORY_MAPPING
using index OBJECT_CATEGORY_MAPPING_INDEX at read committed isolation level
using share row locking chosen by the optimizer
Number of opens = 1000
Rows seen = 19000
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 47
next time (milliseconds) = 408
close time (milliseconds) = 16
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=4119
Number of rows qualified=19000
Number of rows visited=20000
Scan type=btree
Tree height=4
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 18811881.00
optimizer estimated cost: 4708383.46
Right result set:
Index Row to Base Row ResultSet for CATEGORY_MASTER:
Number of opens = 19000
Rows seen = 19000
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 186
next time (milliseconds) = 497
close time (milliseconds) = 0
optimizer estimated row count: 18811881.00
optimizer estimated cost: 90503959.49
Index Scan ResultSet for CATEGORY_MASTER using
constraint SQL090406104857680 at read committed isolation level using share
row locking chosen by the optimizer
Number of opens = 19000
Rows seen = 19000
Rows filtered = 0
Fetch Size = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 186
next time (milliseconds) = 419
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=38000
Number of rows qualified=19000
Number of rows visited=19000
Scan type=btree
Tree height=2
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 18811881.00
optimizer estimated cost: 90503959.49
Best regards,
Arindam.
--
View this message in context:
http://www.nabble.com/URGENT%21%21%21-JDBC-SQL-query-taking-long-time-for-large-IN-clause-tp22927332p22927332.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
