--- In firebird-support@yahoogroups.com, "Dmitry Yemanov"
<dimitr@u...> wrote:

> "Adam" <s3057043@y...> wrote:
> >
> > (ID IN ('1','2','3',......,'1499') OR ID IN
> > ('1500','1501','1502',......,'2002'))
>
> What amount of rows is your predicate expected to match? 5% of the

table?

It varies and depends on the permissions of who is using it. On a
site level, generally only a small percentage is visible, maybe up to
100 or 200 records, 5% may be realistic, but it depends on the
customer. Some customers have a few large sites, others have multiple
small sites. The main problem we found during testing is where an
area office or head office is involved. They tend to have effectively
God mode in the application, so they can include up to 100% of the
individuals, however they tend to look at a subset of date ranges
which means the percentage of the table drops as time goes on.

> Do you understand that if ID is indexed (I suppose it is) your

query will

> force 2000 separate index scans? Unless the output covers only a few
> percents of the huge table, I really doubt this is faster than a

natural

> scan.

I will force a natural scan (ID+0 in ()) and compare the performance.
It may not be the PK of the table, I used ID as an example. In fact
our IDs have no business meaning to the customer, it is far more
likely to be a list of foreign key values.

>
> > It seems that I can not get the PLAN using any of my normal

tools. (It

> > comes back blank). On further investigation, it is actually

coming up

> > with a sensible plan, (IBPlanalyzer and IBAdmin both show it

making

> > indexed reads and it is definately not doing a table scan). What

would

> > cause this, and is it something I should be concerned about?
>
> Plan buffer (passed by the tool) is not big enough.
>

Thats alright then.

By the way, my dunit test cases have found the next limit (64KB in a
single SQL statement), but I knew that anyway. It hits after about
12000 members using this technique in case anyone is interested. It
is a significant improvement over 1499.

Thanks for the answer.

Adam