Ok…

 

1)       Why are you using ‘on commit preserve rows?

2)       Why are you using executeBatch()

3)       What does your query look like when you use the temp table?

 

With respect to #1: You don’t need to preserve the rows outside of your current transaction. You shouldn’t be in a transaction, but since you’re doing the inserts in to the temp table you should have something like the following:

BEGIN WORK:

     Insert data in to temp

     Run query

END WORK;

Clean up and quit…

 

Your data need only persist for the running of your select statement and when you’re done, it should be cleaned up. Otherwise when you run multiple transactions, you’ll have to truncate your temp table each time.

 

With respect to #2:

            Huh? Ok, why are you running executeBatch()?

Here’s the url to the API: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeBatch()

All you are doing is running the same insert statement multiple times. Not a batch or block of different SQL statements.

In an iterated loop, run either execute() or executeUpdate(). If you’ve prepared your insert statement this should be fairly quick.

Also how many object_ids are in the list and are they all unique?

 

With respect to #3:

I have to wonder why your query jumped up to not being completed.

Something isn’t right.

 

 

Since I don’t know much about you, I have to ask… is this a school assignment?

 

-Mike

 

 

 


From: Arindam Bhattacharjee [mailto:mr.arindam.bhattacharjee@gmail.com]
Sent: Tuesday, April 07, 2009 12:38 PM
To: Derby Discussion; msegel@segel.com
Subject: Re: URGENT!!! JDBC SQL query taking long time for large IN clause

 

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.