Hi Zlatko,

thanks for your answer.

In the meantime I'm already doing it the way you suggested on some
critical points. It works very well so far.

The OR-Mapper (LLBLGen) is automatically fetching related
rows/entities with this kind of query. E.g. if I load the orders, the
OR-Mapper automatically loads Shippingaddresses, Billingaddresses,
Customerdata and so on.

There are dozens or hundreds of relations of this kind. It's not
possible for me (or way too much effort) to implement a separat fetch
mechanism for all of these related entities. I guess, switching the
database system would be less effort and a less "dirty" solution.

This kind of query is slowing down my whole application. There are
only a few queries that take more than 20 seconds to execute. These i
have fixed with the workaround you suggested. But there are a lot of
queries that take 1-3 seconds to complete instead of 50 or 100 ms. And
if the row count is growing the execution time is growing exponential.

Unfortunately, the vendor of the OR-Mapper will not change the query.

So I hope the Firebird Team will fix this issue.

Anton




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

> Don't know which OR mapper you use, but you can try to break one
> statement retreive to two statements.
>
> First Statement would be to get all ShippingAddressID's from apOrder
> Second Statement would be to get all Addresses for set of
> ShippingAddressID's from first step. I think it would run faster.
>
> Regards,
> Zlatko
>
> > > >
> > > > 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))
> > > > ))
> > > >
> > > >
> > >
>