Re: in(...) clause and PreparedStatement

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