search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.24k stars 215 forks source link

Very slow execution of queries with IN or EXISTS clause statements [CORE1769] #2194

Open firebird-automations opened 16 years ago

firebird-automations commented 16 years ago

Submitted by: Anton Roeckenwagner (roecki)

Is duplicated by CORE2344 Is duplicated by CORE5407 Duplicates CORE5748

This simple query takes about 45 seconds to complete. If I add 100 Ids to the clause, the query is not finishing within multiple hours.

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

If I modify the query to use EXISTS the execution takes about 20 seconds:

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

The apOrders table contains about 20000 rows. Appropriate Indexes are set.

I have already reported the IN clause issue (not the EXISTS clause issue), but the issue was closed without a helpfull answer. It was closed because it's a known issue, but I didn't find a issue like that.

Will this be fixed? Perhaps in 2.1? Or will this never be fixed?? If this is a known issue, can you please link to it?

I'm using a OR-Mapper and therefore can't modify the query. I guess a lot of users will have the same problem.

Here is a link to the closed issue: CORE1759

regards, Anton Roeckenwagner

firebird-automations commented 16 years ago
Modified by: @dyemanov assignee: Dmitry Yemanov \[ dimitr \]
firebird-automations commented 15 years ago
Modified by: Sean Leyne (seanleyne) Link: This issue is duplicated by [CORE2344](https://github.com/FirebirdSQL/firebird/issues?q=CORE2344+in%3Atitle) \[ [CORE2344](https://github.com/FirebirdSQL/firebird/issues?q=CORE2344+in%3Atitle) \]
firebird-automations commented 7 years ago
Modified by: Sean Leyne (seanleyne) Link: This issue is duplicated by [CORE5407](https://github.com/FirebirdSQL/firebird/issues?q=CORE5407+in%3Atitle) \[ [CORE5407](https://github.com/FirebirdSQL/firebird/issues?q=CORE5407+in%3Atitle) \]
firebird-automations commented 6 years ago
Modified by: Sean Leyne (seanleyne) Link: This issue duplicates [CORE5748](https://github.com/FirebirdSQL/firebird/issues?q=CORE5748+in%3Atitle) \[ [CORE5748](https://github.com/FirebirdSQL/firebird/issues?q=CORE5748+in%3Atitle) \]