Re: Select where id in (LARGE LIST) ?

I have been trying the chunk method and it wins in cases where the
number of ids is not too high. I was comparing:

(1) joining the main table with a table that had a column with ids and a
boolean column for selected, both id columns have indexes.
(2) select over main table where id in (...) done in 100s with prepared
statement and results accumulated in java

with a select that did a sum() of another column the results were:

No. of Selected Items | Join Select Time in sec | Chunk Select
Time in sec
30 | 0.4
| 0.007
4000 | 0.5
| 0.24
30000 | 0.7 |
1.12

All of these were with 30,000 rows in main table from JDBC.

These results don't take into account the time it takes to clear then
populate the select table with selection data from client. At the moment
this can take 1-2 seconds which is far from interactive for the user.
Which is why I am looking for a better method.

Any other suggestions? :-)

I would like to push the data size up to a million, which means the user
could in theory select a million rows and apply operation. This is going
to get very slow with the chunk method.

Many Thanks

Jasper

Csaba Nagy wrote:

>Jasper,
>
>You can chunk your operation. That means to only use 100 entries in one
>run, and repeat it until all ids were processed. Use a prepared
>statement, that will save you some overhead. The last chunk will have
>less entries than the parameter placeholders, so you will have to build
>a special last statement, or to set the superfluous parameters to null
>or to one of the values from the last chunk, depends on what kind of
>query you have. We do all our data import/export this way, and it works
>fine.
>
>Cheers,
>Csaba.
>
>
>On Fri, 2005-07-29 at 17:30, Jasper Potts wrote:
>
>
>>I am working on a gui application which has a list of ids of selected
>>items. To perform an operation on the selected items I do a
>>"select/update .. where id in(...)". There seems to be a limit of
>>100-200 items in the list. Is there a way of doing this with large
>>lists, maybe 10,000, 100,000 long?
>>
>>The best solution I have so far is to create a selection table and write
>>the select out to that and perform a join but the 100,000 inserts are slow.
>>
>>The ids are int8(long), so not talking large amounts of data, couple Mb
>>at most. Database and client running on same machine over localhost.
>>
>>Many Thanks
>>
>>Jasper
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>
>
>