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 15:55:29 GMT
Yes, I think it is unusual.

Can you tell what your system is doing while this query is running? Is it
very CPU-busy? Very disk-busy? Mostly idle?
[Arindam] Nothing much. Just an idle laptop.

What version of Derby? What operating system? What version of Java?
[Arindam] 10.1.3.1; Windows XP; JRE 1.6

How large (in megabytes on disk) are the object_master table and its index?
(You can use the SPACE_TABLE system procedure, I think, to get this info.)

CONGLOMERATENAME        ISINDEX    NUMALLOCPAGES    NUMFREEPAGES
NUMUNFILLEDPAGES    PAGESIZE    ESTIMATEDPAGESAVING


CATEGORY_MASTER            0    103    0    0    4096    0
SQL090406091302600        1    55    0    0    4096    0
SQL090406091302601        1    160    0    1    4096    0
SQL090406091302730        1    1    0    1    4096    0
OBJECT_MASTER            0    10497    0    0    4096    0
SQL090406091302760        1    5340    0    1    4096    0
SQL090406091302761        1    16708    0    410    4096    0
OBJECT_CATEGORY_MAPPING        0    150794    0    0    4096    0
OBJECT_CATEGORY_MAPPING_INDEX    1    112177    0    57    4096    0

Can you experiment with larger and smaller sets of values for the IN clause?
If you have just 50 items in the IN clause, do you get the same behavior?
[Arindam] 50 in in-clause is very fast - the whole query returns in 40
millis.

What about if you have 5,000 items in the IN clause? (Actually I'm not sure
if Derby can handle that many.)
[Arindam] I will try this and send it after a few mins.

I suspect that you'll find that it takes 30 seconds regardless of the number
of items in the IN clause, but I may be wrong.
[Arindam] Well actually that doesn't seem to be the case - anything between
1 and 50 is very fast.

Also, what about the query:

 select count(object_id) from object_master
[Arindam] 2 consecutive run:
time taken execute statement = 1421
count = 990099
time taken to get the count value = 2563
time taken execute statement = 219
count = 990099
time taken to get the count value = 2609

How long does this query take?

On Tue, Apr 7, 2009 at 8:52 PM, Bryan Pendleton
<bpendleton@amberpoint.com>wrote:

> query: select object_id from object_master where object_id in (? ..1000
>> times.. ?) time taken enumerate result set = 31813 (isn't this unusual?
>> enumeration of results taking so much time?)
>>
>
> Yes, I think it is unusual.
>
> Can you tell what your system is doing while this query is running? Is it
> very CPU-busy? Very disk-busy? Mostly idle?
>
> What version of Derby? What operating system? What version of Java?
>
> How large (in megabytes on disk) are the object_master table and its index?
> (You can use the SPACE_TABLE system procedure, I think, to get this info.)
>
> Can you experiment with larger and smaller sets of values for the IN
> clause?
> If you have just 50 items in the IN clause, do you get the same behavior?
> What about if you have 5,000 items in the IN clause? (Actually I'm not sure
> if Derby can handle that many.)
>
> I suspect that you'll find that it takes 30 seconds regardless of the
> number
> of items in the IN clause, but I may be wrong.
>
> Also, what about the query:
>
>  select count(object_id) from object_master
>
> How long does this query take?
>
> thanks,
>
> bryan
>

Mime
View raw message