select .. where ..in TOO LONG

I need to perform a select based on a list of id's , it's fine when the
number of id's is not too big, but when It can be very large..

what options could I have ?

create a temporary table and match with this table ... each time I need
to perform the select... ?
no better option in term of performances ?

thanks for yoru lights

joss

Strange problem to have. I would nuke a view everytime since i think you
would get better string length in a view. And then select the view.
Assumming nonquery can take larger sql statement then datareader.




"Josselin" <josselin@wanadoo.fr> wrote in message
news:4587b04c$0$27412$ba4acef3@news.orange.fr...
>I need to perform a select based on a list of id's , it's fine when the
>number of id's is not too big, but when It can be very large..
>
> what options could I have ?
>
> create a temporary table and match with this table ... each time I need to
> perform the select... ?
> no better option in term of performances ?
>
> thanks for yoru lights
>
> joss
>

Josselin wrote:
> I need to perform a select based on a list of id's , it's fine when the
> number of id's is not too big, but when It can be very large..
>
> what options could I have ?
>
> create a temporary table and match with this table ... each time I need
> to perform the select... ?
> no better option in term of performances ?
>
> thanks for yoru lights
>
> joss
>

The method used depends on how you choose which ID's to look for. If the data is
derived from the database and not user input, then you can use a derived table -
basically a view on-the-fly:

select field1,field2 from tablea where id in (select id from table2 where
somevalue=somecriteria);

or

select temptab.field1, temptab.field2 from (select id, field1,field2 where
somevalue=someothervalue) temptab ;

or use a join

select a.field1, b.field2 from table1 a join table2 b on a.id=b.id
where b.status = 1;

You have a database engine and these are just starting points...

--
Michael Austin.
Database Consultant

On 2006-12-20 04:28:37 +0100, Michael Austin <maustin@firstdbasource.com> said:

> Josselin wrote:
>> I need to perform a select based on a list of id's , it's fine when the
>> number of id's is not too big, but when It can be very large..
>>
>> what options could I have ?
>>
>> create a temporary table and match with this table ... each time I need
>> to perform the select... ?
>> no better option in term of performances ?
>>
>> thanks for yoru lights
>>
>> joss
>>
>
> The method used depends on how you choose which ID's to look for. If
> the data is derived from the database and not user input, then you can
> use a derived table - basically a view on-the-fly:
>
> select field1,field2 from tablea where id in (select id from table2
> where somevalue=somecriteria);
>
> or
>
> select temptab.field1, temptab.field2 from (select id, field1,field2
> where somevalue=someothervalue) temptab ;
>
> or use a join
>
> select a.field1, b.field2 from table1 a join table2 b on a.id=b.id
> where b.status = 1;
>
> You have a database engine and these are just starting points...

thanks ..
the data is.. well ... not actually derived from a DB it's coming from
a serialized Ruby Array of arrays , stored into a text column (km25)
in a table 'cities' (I am using Rails..) , the original array is like
[ [ 23455, 2.45025], [ 45896, 4.56876], ..... ]
km25 means 'all cities around 25 km) , each array store the id of the
city (int) and the km- distance (float).

Getting a current_city from the table in memory, brings this data ,
getting immediatly all the id's/distance of all cities around 25km
makes a BIG difference in performance vs calculating it from the
LAT-LONG data
then I need to extract 'proposals' for the current city (one-to-many)
AND from the cities in this list... that's why I am using the SELECT ..
WHERE cities_id IN (the array of id's)....

drawback : I had to run a batch app to perform the calculous for all
the cities in the table (40'000 ) and it tooks many hours... but these
data will never change... no new city.. and the distances are
immutable (at least until the global earth warming... ;))))

It's running fine now .. ! I got it

joss