Subject Re: [firebird-support] Re: 1500 values in IN (...) clause fails
Author Kjell Rilbe
Den 2011-04-01 07:40 skrev karolbieniaszewski såhär:
> Having 1500 values in "in" is not good choice
> but try change this to
> in (
> ...
> )
> but better will be to fill some temporary table with this data

Yes, I'm aware that this isn't optimal. It's the way ECO fetches data
for a list of object identities (i.e. primary keys). With ECO, if you
navigate from a list of objects to related objects, e.g. from a list of
"Car" objects to their "Tires", ECO will, if you so require, load data
for all the cars' Tyre objects. It will do this by issuing a select,
listing the PK:s for the Tyre objects in an IN clause.

It is possible that it would be better with a temp table, but 1) it's
not the way it currently works, and 2) would it really perform better?
Inserting 50000 PK:s in a temp table just to join it with a normal
table, and then deleting the temp table seems like more work than to
issue 50000/1500 = 34 selects with 1500 keys each in an in (...) clause.
But maybe I'm wrong...

Anyway, the issue is not if this is the optimal way to do things, the
issue is that FB has a bug, albeit rather unimportant. It says to
support 1500 values but supports no more than 1499.

Kjell Rilbe
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64