ra8009 wrote:

> My user wants to select a specific category or ALL categories. They
> may have 100,000 to 200,000 records and speed will be important as the
> user may run this hundreds of times per day.
>
> Is the best way to hanldle this (I'm using IB Objects) to:
>
> a) Build the SQL statement dynamically with the "where" clause or
> without as needed
>
> b) Use "Between" with two paramters. When one category is needed I
> make them the same, when ALL are needed I set them to high and low
> values above and below the range.
>
> c) some other method

It depends what you are doing. IBObjects does a very good job of only
fetching the records it needs - unless you deliberately break it ;)
If you are scrolling a new grid, IBO will get the next set of records
that it needs to display as it is already processing the SQL internally.
This is an aspect of IBO that people may not be aware of, and is one of
the main reasons that performance of a lot of things is improved with
IBO. Using 'COUNT' or 'FetchAll' will break the automatic actions, as
will complicating the SQL so that IBO can not 'do it's thing'.

You need to move to the IBO list if you have a particular performance
problem with this query
http://groups.yahoo.com/group/IBObjects/

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services