Hi Group,

Some background:

I am just writing a work around in our dataset to CSV conversion
function. Basically it accepts a TDataset and a field name, then
creates a distinct list of every visible record in that Dataset.

So a table like

2
1
2
3
4
3
5

would come back as

'1','2','3','4','5'

This can then be inserted into an IN statement and has worked very
well to date. Now obviously there is a problem with this approach when
you hit the maximum 1500 records (in fact the maximum is 1499). We
have done some analysis on our software and believe it is more
scalable than our current base (except for a few minor UI design
issues), but we are likely to hit this 1500 mark in particular IN
predicates.

AFAIR, this is due to a limitation of some scratch space it internally
uses, and it can be split into multiple IN statements using an OR.

I have modified the logic so you can pass in a database field name and
it then generates something as follows

Again the original table

1
2
3
..... -- a lot of records snipped :)
2000
2001
2002

You also pass in the database field name, in this case 'ID', and the
function returns, inserting brackets so as not to confuse order of
operations with other items in the where clause.

(ID IN ('1','2','3',......,'1499') OR ID IN
('1500','1501','1502',......,'2002'))

I am in the middle of developing some dunit test cases to prove it
works for all scenarios, but initial quick tests show it works.

Now the question:

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?

Thanks in advance for your help.

Adam