Thanks Zlatko!

This query runs very fast! Thanks!

The only problem is that the query is generated by an OR-Mapper. I
have forwarded this to the vendor of the OR-Mapper. I hope the vendor
will change this.

Thanks,

Anton

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

>
> --- 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
>