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


Here is some additional information form the IBExpert Performance
Analysis:

Plan
------------------------------------------------
PLAN (apOrders INDEX (RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513,
RDB$PRIMARY513, RDB$PRIMARY513, RDB$PRIMARY513))
PLAN (address NATURAL)

I have reported this in the firebird bug tracker, but the answer was
not helpfull. The issue was closed, because it is a known issue. But I
couldn't find another issue regarding to that, so i don't know
anything about it.

http://tracker.firebirdsql.org/browse/CORE-1759

Is a fix for this issue planed?

Thanks,
Anton