--- In firebird-support@yahoogroups.com, "roecki" <roecki@...> wrote:

>
> Hi,
>
> there is a bug in Firebird 2 in handling the IN clause statement:
>
> The following simple query takes about 45 seconds to complete. If I
> add more than 100 Ids to the where clause the query is not

finishing

> within multiple hours. The tables are containing about 20000 rows.

I

> have tested this with Firebird 2.1 RC1 and Firebird 2.0.1.
>
> SELECT "address".ID,
> "address"."EnvironmentID",
> "address"."eBayUserDBID" AS "EBayUserDBID",
> "address"."SiteID",
> "address"."Name",
> "address"."Company",
> "address"."Phone",
> "address"."Street1",
> "address"."Street2",
> "address"."CityName",
> "address"."StateOrProvince",
> "address"."Country" AS "CountryBasevalue",
> "address"."CountryName",
> "address"."PostalCode",
> "address"."AddressID",
> "address"."ExternalAddressID",
> "address"."AddressOwner" AS "AddressOwnerBasevalue",
> "address"."AddressStatus" AS "AddressStatusBasevalue",
> "address"."IntlName",
> "address"."IntlStateAndCity",
> "address"."IntlStreet",
> "address"."ts" AS "Ts",
> "address"."Salutation",
> "address"."Firstname"
> FROM "address"
> WHERE
> ("address".ID IN (
> SELECT "apOrders"."ShippingAddressID"
> FROM "apOrders"
> WHERE
> ("apOrders".ID IN (243, 244, 245, 246, 247,

248,

> 175, 176,
> 177, 178, 179, 180, 181, 182, 183, 184, 186,

187,

> 188, 189,
> 190, 191, 192, 193, 194, 195, 196, 197, 198,

199,

> 200, 201,
> 202, 203, 204, 205, 206, 207, 208, 209, 210,

211,

> 212, 213,
> 215, 216, 217, 218, 219, 220))
> ))
>
>

Hi Anton
try this:

SELECT "address".ID,
"address"."EnvironmentID",
"address"."eBayUserDBID" AS "EBayUserDBID",
"address"."SiteID",
"address"."Name",
"address"."Company",
"address"."Phone",
"address"."Street1",
"address"."Street2",
"address"."CityName",
"address"."StateOrProvince",
"address"."Country" AS "CountryBasevalue",
"address"."CountryName",
"address"."PostalCode",
"address"."AddressID",
"address"."ExternalAddressID",
"address"."AddressOwner" AS "AddressOwnerBasevalue",
"address"."AddressStatus" AS "AddressStatusBasevalue",
"address"."IntlName",
"address"."IntlStateAndCity",
"address"."IntlStreet",
"address"."ts" AS "Ts",
"address"."Salutation",
"address"."Firstname"
FROM "apOrders"
inner join address on apOrders.ShippingAddressID = address.ID
WHERE
("apOrders".ID IN (243, 244, 245, 246, 247, 248,
175, 176,
177, 178, 179, 180, 181, 182, 183, 184, 186, 187,
188, 189,
190, 191, 192, 193, 194, 195, 196, 197, 198, 199,
200, 201,
202, 203, 204, 205, 206, 207, 208, 209, 210, 211,
212, 213,
215, 216, 217, 218, 219, 220))
))


Regards,
Zlatko

P.S.

I am posting this message third time (didn't saw previos two posted)
so excuse me if this same message appears more than once