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:04:24 GMT
Time taken to execute 5000 INs:

time taken to prepare statement = 500
time taken execute statement = 2127703
 result set count = 5269

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