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