You can, of course, try to write a stored procedure with code along the
lines of:

for select distinct ao."ShippingAddressID" FROM "apOrders" ao
WHERE
ao.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) into :SAID do
for select a.ID,
a."EnvironmentID",
a."eBayUserDBID" AS "EBayUserDBID",
a."SiteID",
a."Name",
a."Company",
a."Phone",
a."Street1",
a."Street2",
a."CityName",
a."StateOrProvince",
a."Country" AS "CountryBasevalue",
a."CountryName",
a."PostalCode",
a."AddressID",
a."ExternalAddressID",
a."AddressOwner" AS "AddressOwnerBasevalue",
a."AddressStatus" AS "AddressStatusBasevalue",
a."IntlName",
a."IntlStateAndCity",
a."IntlStreet",
a."ts" AS "Ts",
a."Salutation",
a."Firstname"
FROM "address" a
WHERE
a.ID = :SAID
INTO :EnvironmentID, :eBayUserDBID... do
SUSPEND;

I hope you get the idea, even though I don't write a full stored
procedure (and even in this tiny sample, there is likely a few errors, I
don't write stored procedures every month and Thunderbird doesn't have
any Firebird syntax checker as far as I know ;o).

HTH,
Set

roecki wrote:

> 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=E6r
> <svein.erling.tysvaer@...> wrote:
>> Hi Anton!
>> =20
>> I've no answers, just a couple of questions.
>> =20
>> 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.
>> =20
>> 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 =3D "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))
>> =20
>> 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.
>> =20
>> 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.
>> =20
>> HTH,
>> Set
>> =20
>> -----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
>> =20
>> Hi,
>> =20
>> there is a bug in Firebird 2 in handling the IN clause statement:
>> =20
>> 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.
>> =20
>> 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))
>> ))
>> =20
>> =20
>> Here is some additional information form the IBExpert Performance
>> Analysis:
>> =20
>> 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)
>> =20
>> 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.
>> =20
>> http://tracker.firebirdsql.org/browse/CORE-1759
>> =20
>> Is a fix for this issue planed?
>> =20
>> Thanks,
>> Anton