db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "arindam.bhattacharjee" <mr.arindam.bhattachar...@gmail.com>
Subject URGENT!!! JDBC SQL query taking long time for large IN clause
Date Tue, 07 Apr 2009 11:44:20 GMT

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.


Mime
View raw message