Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

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

1 view
Skip to first unread message

Nina G.

unread,
Jun 20, 2007, 6:59:43 PM6/20/07
to
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

unread,
Jun 21, 2007, 10:57:17 AM6/21/07
to

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

unread,
Jun 21, 2007, 3:47:27 PM6/21/07
to
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.

unread,
Jun 21, 2007, 5:40:25 PM6/21/07
to
Thank you Amit and John.

"SalmonTraining" <nos...@salmontraining.com> wrote in message
news:1182455247.6...@u2g2000hsc.googlegroups.com...

Michael Peppler

unread,
May 21, 2009, 1:42:13 AM5/21/09
to

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
--
Michael Peppler - Peppler Consulting SaRL
mpep...@peppler.org - http://www.peppler.org
Sybase DBA/Developer - TeamSybase: http://www.teamsybase.com
Sybase on Linux FAQ - http://www.peppler.org/FAQ/linux.html

0 new messages