Re: in(...) clause and PreparedStatement

Thanks a lot for the insightful advice !!!
I can't understand how I haven't get such design by myself - it seems so
clear now :). It will definitely improve my code and overall design a lot.

Thanks again and best regards, Sergey.

Csaba Nagy пишет:

>Sergey,
>
>Doing big processing in chunks is a good thing to do. It will avoid long
>running transactions, and allow you to interrupt the operation if
>needed.
>However, there might be a better way to do it then retrieving the id's
>and using "IN": use a temporary table to store the id's, and then use
>the subselect query on that table. It should be fast, cause it will only
>contain the id's you want to process. The "temporary" table could easily
>be a permanent table, and contain more than one set of processing ids,
>and a special "transactionid" field you can select on. After processing
>you can delete the unnecessary rows to keep the table small, and
>probably you want to vacuum it often.
>The big advantage is that with this solution you can use prepared
>statements, the code will be simpler, chunking is easily achieved by
>only selecting so many ids to the temp table, and it is probably also
>the fastest way you can process the data, as you don't have to move any
>data back and forth between the server and the client.
>
>HTH,
>Csaba.
>
>
>On Tue, 2005-06-14 at 12:20, Sergey Pariev wrote:
>
>
>>Thanks to all for the instant and detailed replies.
>>
>>I have to say I posted simplified example of the query I need to run, in
>>reality I don't know how many items will be in the IN clause. I have to
>>do 3 queries (update, then insert ... select from this table and delete
>>) on particular table given the set of keys which I retrieve in the
>>other query, which it complex so I can't dublicate it 3 times - so I
>>can't just write
>>
>>SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where
>>...).
>>
>>Currently I retrieve keys at first and concatenate them into string like
>>1,2,3,... , then I issue my queries like
>>
>>st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ;
>>
>>and so on. It is working as for now, but is really ugly, so I desided to
>>improve things and was just wandering if there a way to use variable
>>number of parameters.
>>
>>Considering the thing you guys wrote I'm propably will rewrite my code
>>to use fixed number of parameters - it wouldn' t be too hard since I'm
>>processing it in chunks anyway. It will be only in the next version of
>>my app though - don't want to fix the thing which are working :).
>>
>>Thanks again, Sergey.
>>
>>Csaba Nagy пишет:
>>
>>
>>
>>>Sergey,
>>>
>>>Additionally to what Dave wrote you: if you want to use the prepared
>>>statement with variable number of parameters, you can sometimes use a
>>>query with a fixed number of parameters, and if you have more
>>>parameters, execute it chunk-wise, if you have less parameters then set
>>>the additional ones to null. This variant complicates your code
>>>considerably though, but the query is prepared and reusable. I would
>>>think it only matters if you reuse the prepared statement for a large
>>>number of executions.
>>>
>>>This only works if you don't have to have all the parameters processed
>>>in one statement, i.e. you can chunk your query and the final cumulated
>>>results will be the same.
>>>
>>>In any other case you will not be able to use prepared statements, i.e.
>>>you'll need to build your query each time. It is still advisable to do
>>>it via JDBC prepared statements, and not build it directly by hand,
>>>because that will take care for all the escaping necessary for your
>>>parameter values. This means you should build a query with as many ?
>>>signs as many parameters you have, and then set them in a loop or so.
>>>
>>>HTH,
>>>Csaba.
>>>
>>>
>>>On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
>>>
>>>
>>>
>>>
>>>>Yes, because it thinks "1,2,3" is a string
>>>>
>>>>you would have to do
>>>>
>>>>IN(?,?,?)
>>>>
>>>>then
>>>>
>>>>setObject(1, 1);
>>>>setObject(2, 2);
>>>>setObject(3, 3);
>>>>
>>>>Dave
>>>>
>>>>On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>Hi all.
>>>>> It may be stupid question, but is there way to use
>>>>>PreparedStatement with queries like
>>>>>SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
>>>>> I've googled but haven't found nothing explicitly said on this
>>>>>topic.
>>>>> I've tried the following
>>>>> PreparedStatement st = conn.prepareStatement("SELECT * FROM
>>>>>mytable WHERE t_id IN ( ? )");
>>>>> st.setObject(1,"1,2,3");
>>>>>
>>>>> and get error complaining on type mismatch.
>>>>>
>>>>> Thanks in advance, Sergey.
>>>>>
>>>>>---------------------------(end of
>>>>>broadcast)---------------------------
>>>>>TIP 2: you can get off all lists at once with the unregister command
>>>>> (send "unregister YourEmailAddressHere" to
>>>>>majordomo(at)postgresql(dot)org)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 6: Have you searched our list archives?
>>>>
>>>> http://archives.postgresql.org
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if your
>> joining column's datatypes do not match
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>