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 Re: URGENT!!! JDBC SQL query taking long time for large IN clause
Date Tue, 07 Apr 2009 17:37:31 GMT
I created the indexes one by one just to see whether the performance
improves or not. I don't have much knowledge of databases - I agree. I also
created temp table using the following command:

declare global temporary table temp_object_ids (object_id int) on commit
preserve rows not logged

Inserting 1000 records took around 800 millis with prepared insert statement
and executeBatch().

I just didn't have the patience to wait for the query to end - I waited for
around 5 mins before killing it - it just kept running endlessly.

I tried removing the object_master from the select clause - it was slower
than the query which I posted. The fastest query till now has been the one
with union - unfortunately, it just hogs too much memory during query
parsing.

Best regards,

Arindam.

On Tue, Apr 7, 2009 at 8:52 PM, <derby@segel.com> wrote:

> Wow, this really sounds like a class project.
> But since you've asked... lets look at a couple of things.
>
> First your query...
> That is a lot of values in the IN Clause. It will hurt performance on
> pretty
> much most databases. Derby had an issue with performance and IN, but I
> think
> they've fixed it as best they could about a year or two ago. (Sorry I'm a
> little fuzzy when it comes to time. Its all relative. ;-)
>
> Second, with your query, its better if your format the query so that it's
> easier to read. Trust me, it saves a lot of time when you have to look at a
> lot of queries and when you have visual cues you can get a better feel for
> your query.
>
> But let's take a step back and look at what you have...
>
> In simple terms, you have a basic object table, and a category table which
> you want to map. An object can exist in more than one category and you want
> to be able to retrieve the objects based on a long list of possible
> categories.
>
> Starting with your tables and Indexes...
> > CREATE TABLE APP.OBJECT_MASTER (
> >     OBJECT_ID INTEGER NOT NULL,
> >     OBJECT_CUID VARCHAR(32) NOT NULL,
> >     PRIMARY KEY (OBJECT_ID)
> > );
> Then you create this:
> > CREATE INDEX APP.OBJECT_MASTER_INDEX ON APP.OBJECT_MASTER
> > (OBJECT_ID,OBJECT_CUID)
>
> Ok, the first question is why? Is it that you want to only hit the index
> and
> not have to read a row from the table? FORGET ABOUT IT! At least for now...
>
> You already have a primary key on object_id and this second key will pretty
> much duplicate the table and you're doubling your storage requirements.
>
> > CREATE TABLE APP.OBJECT_CATEGORY_MAPPING (
> >     OBJECT_ID INTEGER NOT NULL,
> >     CATEGORY_ID INTEGER NOT NULL
> > );
> > CREATE INDEX APP.OBJECT_CATEGORY_MAPPING_INDEX ON
> > APP.OBJECT_CATEGORY_MAPPING (OBJECT_ID,CATEGORY_ID)
> Note that since your query is looking at the objects and then wants the
> categories, this index is ok.
>
> And finally:
> > 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.CATEGORY_MASTER_INDEX ON APP.CATEGORY_MASTER
> > (CATEGORY_ID,CATEGORY_NAME)
>
> Again this index is redundant and all its really going to do is double your
> storage requirements.
>
> Now lets look at your query...
> I've reformatted it so that its easier to read:
>
> SELECT  B.category_name,
>        COUNT(B.category_name) AS category_count
> FROM    object_master A,
>        category_master B,
>        object_category_mapping C
> WHERE   A.object_id = C.object_id
> AND     C.category_id = B.category_id
> AND     A.object_id IN (  ? ,  ? ,  ? ,  ? ,  ? ,  ? ,  ? ,  ? ,
> [SNIP a lot of ?]      )
> GROUP BY      category_master.category_name
> ORDER BY      category_count DESC
>
> <RANT> One should always try to format their queries in to a readable
> pattern. This makes debugging and tuning easier. So take the extra 30
> seconds and clean up your code! If you use something like JIndent to format
> your java code, cleaning up your query syntax will make maintaining your
> code easier. </RANT>
>
>
> OK, NOW THAT YOU CAN READ YOUR CODE, CAN YOU SEE SOME PROBLEMS?
>
> 1) Why do you include your object_master table in your query?
>
> You said that your object_ids are coming from an external source. So why do
> you need to join to the object_master table? You already have your
> object_ids.
>
> Rewritten, this query should help...
> SELECT  B.category_name,
>        COUNT(B.category_name) AS category_count
> FROM    category_master B,
>        object_category_mapping C
> WHERE   C.category_id = B.category_id
> AND     C.object_id IN (  ? ,  ? ,  ? ,  ? ,  ? ,  ? ,  ? ,  ? ,
> [SNIP a lot of ?]      )
> GROUP BY      category_master.category_name
> ORDER BY      category_count DESC
>
> Note: Now you're joining 2 tables B and C and you're limiting you C based
> on
> a really, really ugly and long IN Clause.
>
> 2) There's something you're not telling us.
>
> It looks like you're generating this query on the fly. Because its dynamic,
> you're going to incur a bit of overhead in that each time you run the
> query,
> it will have to be prepared and then executed.
>
> Since we don't know much about your application, here's an alternative
> suggestion ... Look at using a temp table instead of the large IN clause.
>
> That is, create a temp table at the start of your app, prior to running the
> query, you will want to truncate the temp table and then populate it with
> the object_ids.
>
> Then your query, which you can now prepare, would look like this...
> SELECT  B.category_name,
>        COUNT(B.category_name) AS category_count
> FROM    category_master B,
>        object_category_mapping C
> WHERE   C.category_id = B.category_id
> AND     C.object_id IN(
>        SELECT object_id
>        FROM   temp_table)
> GROUP BY      B.category_name
> ORDER BY      category_count DESC
>
> This should improve the performance of your query.
>
> I would suggest looking at the time it takes to run the query using the IN
> CLAUSE versus the time it takes to truncate/populate the temp table and
> then
> run the query.
>
> Getting back to your use of the alternative index, you can add the indexes
> afterwards and then compare performances. I don't think that they'll have
> as
> major of an impact as you think.
>
> HTH
>
> -Mike
>
> > -----Original Message-----
> > From: arindam.bhattacharjee [mailto:mr.arindam.bhattacharjee@gmail.com]
> > Sent: Tuesday, April 07, 2009 6:44 AM
> > To: derby-user@db.apache.org
> > Subject: URGENT!!! JDBC SQL query taking long time for large IN clause
> >
> >
> > 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