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 <[email protected]>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 >
