Time taken to execute 5000 INs:
time taken to prepare statement = 500
time taken execute statement = 2127703
result set count = 5269
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?