Large list in select ... where ... in

[Nina G.]

I need to run a simple query like

select fee from charges where code in ('value1', 'value2', ..., 'valueN')

where values are 5 character strings. Is there anything bad in having
between 500 and 1000 values in the value list?

Thank you,

Eliyahu

[amit]

Post by Nina G.
I need to run a simple query like
select fee from charges where code in ('value1', 'value2', ..., 'valueN')
where values are 5 character strings. Is there anything bad in having
between 500 and 1000 values in the value list?
Thank you,
Eliyahu

500-1000 values would slow down the performance, but you can use it.
The limit for 'in' clause is 1024 so
make sure the it doesn't exceed that.

Thanks
Amit

[SalmonTraining]

Amit is correct.

If you think you're going to exceed the limits and are looking for an
alternative design, you may get better performance by loading in your
values into a temp table, and then using the "where exists" syntax
(which ensures the query stops once it finds a value).

John Winter
www.SalmonTraining.com/SybaseZone

[Eliyahu G.]

Thank you Amit and John.

Post by SalmonTraining
Amit is correct.
If you think you're going to exceed the limits and are looking for an
alternative design, you may get better performance by loading in your
values into a temp table, and then using the "where exists" syntax
(which ensures the query stops once it finds a value).
John Winter
www.SalmonTraining.com/SybaseZone

[Michael Peppler]

Post by Nina G.
I need to run a simple query like
select fee from charges where code in ('value1', 'value2', ..., 'valueN')
where values are 5 character strings. Is there anything bad in having
between 500 and 1000 values in the value list?

I'm guessing that you are generating the IN list programmatically. Why not
stuff that data into a temp table, and then join with the main table ?

Michael