>We have several huge normalized tables (~1GB-4GB) with our data.
>We also have several lookups tables. Lots of them contain only few (less than 15) records. But 3 (Towns, Usage, Lenders) are pretty large (200 - 1000 records). We assume, that our users may select from multilists, based on these lookups tables. They will not select more than 50 items.
>Multilists have an Exclude option (the usage of it, I believe, would be seldom).
Then you'll need NOT IN () for the Exclude. If they search for NOT IN one of the very small lookup tables, it might not be so bad.
>
> In my current implementation I analyze the list of comma-delimited codes (as a long string) and if I have more than 24, I reconstruct my criterion as
>SearchFiled IN (ListOfComma-DelimitedCodes), otherwise I use INLIST(SearchField,ListOfComma-DelimitedCodes).
>
> I got the Error message, which I described, for 32 codes. I tested this on only one table (exclude all joins) and I got this message again.
>So, the problem is in IN clause. I'm a little confused right now, what should I do.
>
>1) Do I need to play with SYS(3055) function?

If your code works for up to 32 and you only need to get to 50, by all means see if adjusting SYS(3055) will help. It might do the trick. I have never tried it. I don't know how much more memory your program will suck up. If the resulting speed is acceptable, maybe you can use existing code and spend no more time on it. But considering the size of the tables, the bug that Ed remembered, and (I infer) the importance of the operation, it's likely worth testing all the choices for speed. Of course, you can hard-code some SQL statements and race them before you write the builder that will create them.

>
>2) Do I need to create cursors, add additional Joins and make these two app share the same DS?
>
>3) Do I need to create cursors, add subqueries in my Query and make these two app share the same DS?
>
> First approach doesn't require any changes in my current class design.
>2) and 3) require couple of changes, but I know, how I can implement this...

2) and 3) are similar. Some think that 2) would be faster, but I don't know. I sent you another answer before I read this post. That one says more about how I think you might do either of these. From this post, I gather that the search could involve several lookup tables at once, which makes me believe that the method I described in the other post is the right one.