IN() operator problem

i have a list of ID's (indexed) more than 2500 items.
first i have to select these records from one and then Update in
another table. when i pass the list to sql with IN() operator in
select query it takes too long (about 2 mins) and when i run update
query with same criteria it takes too much time.
i am using VB6 for development.
the field is indexed.

what i should do to improve performance?

Khurram Rao[posted and mailed, please reply in news]

Khurram (khurramanis@.gmail.com) writes:
> i have a list of ID's (indexed) more than 2500 items.
> first i have to select these records from one and then Update in
> another table. when i pass the list to sql with IN() operator in
> select query it takes too long (about 2 mins) and when i run update
> query with same criteria it takes too much time.
> i am using VB6 for development.
> the field is indexed.

Yes, the IN operator with many elements performs badly.

This article discusses different alternatives:
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I have a list of ID's (indexed) more than 2500 items. First I have
to select these records [sic] from one and then Update in another
table. When I pass the list to SQL with IN() operator in select query
it takes too long (about 2 mins) and when I run update query with same
criteria it takes too much time.

Rows are not like records at all. Please post your code and DDL.
You are describing procedural steps, not a relational approach.|||um - create a temp table with your list, and join to it in the update
statement?

"Khurram" <khurramanis@.gmail.com> wrote in message
news:60ff3d6c.0412040236.6e0466ea@.posting.google.c om...
>i have a list of ID's (indexed) more than 2500 items.
> first i have to select these records from one and then Update in
> another table. when i pass the list to sql with IN() operator in
> select query it takes too long (about 2 mins) and when i run update
> query with same criteria it takes too much time.
> i am using VB6 for development.
> the field is indexed.
> what i should do to improve performance?
> Khurram Rao