Hi Adam,

Thanks for the comprehensive feedback, especially as we've come up
against similar challenges.

We just could not see any way to perform certain queries soley (and
adequately) using SQL with or without sp's etc, etc. - hence the need
for techniques and solutions we are discussing.

Your method is interesting and could possibly be the way for us to go,
especially as:
1. New requirements mean different sorts of sources for the query.
2. Ability to issue parts of the query in threads.
3. Start returning results to user quicker.
4. Allow (what appears to the user) to be a query 'cancellation'.

Q's:
- Why did you go the memory dataset route rather than internal list
(TList, TStringlist, etc)?

> The other options I have seen and attempted is to insert the records
> into a holding table with the current transactions ID, then to join

to

> that table. The downside to that approach is the number of inserts

per

> second you can practically achieve.

This is the method we are currently using. Generally it performed
well, but nine months ago two new 'master table' columns starting also
generating IN() criteria that hit the system. Also, one of the sites
is remote, using a 2k link with increasing numbers of users (30+) at
that site and this is also stressing the system. Lastly, it puts the
processing on the server (where it ought to belong!) and may be the
easiest method for a generic solution (that is also reusable).

> 50 constants is nothing :)

Ok. Seeing the index used 50 times is just ever-so-little-bit
disconcerting (!) plus in actual fact, a user-defined area/territory
feature is sometimes generating maybe in the hundreds.

> To save you some time, here are some important things you should

know

> about the IN predicate.
>
> One index hit per member:

Q's:
- Is this behaviour changed in any of the future versions of FB?
(2.0, 3.0?)
- Is this behaviour usual?
(i.e do other RDBMS's have the same challenge to overcome)

>
> 64K query size
>
> From memory this will kick in somewhere between 12000 and 13000
> members in the IN. (and remember that is a lot of unnecessary hits

on

> the index).

> This is how we solved this problem...
> We have a function along the lines
> DatasetToConditionClause(ds : TDataset; ...

Q's
- Are you very happy with your solution?
- level of performance achieved?
- aprox. amount of data being queried?



Cheers,
Tom