firebird-support - Re: Bug in Firebird 2 with IN clause statement

Subject	Re: Bug in Firebird 2 with IN clause statement
Author	roecki
Post date	2008-02-28T14:59:07Z

Hi svein_erling,

thanks for your answer!

1) When using your query it takes 23 seconds instead of 45 seconds.
It's still not acceptable.

2) The apOrders Table has 8 BLOB fields. If i drop all of them the
performance isn't any better.

Anton

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> Hi Anton!
>
> I've no answers, just a couple of questions.
>
> 1) Is this SQL equally slow? Firebird used to be lousy at handling

IN (subselect), but Arno improved it a lot before Fb 1.5.

>
> 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
> EXISTS(SELECT * FROM "apOrders"
> WHERE
> "address".ID = "apOrders"."ShippingAddressID"
> AND
> "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))
>
> 2) Do the "apOrders" table contain some big fields or BLOB fields? A

few days ago, someone had a different problem with Fb 2.1, where it
mattered whether BLOB fields not at all mentioned in the query
contained information.

>
> If the EXISTS alternative performs decently, I don't think it really

matters whether IN (subselect) is quick or slow - it is just a matter
of getting used to a slightly different coding style. I think the
developers continually try to improve Firebird, but I've no idea
whether this will be addressed or not.

>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] On Behalf Of roecki

> Sent: 28. februar 2008 13:38
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Bug in Firebird 2 with IN clause statement
>
> 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,