Issue Details (XML | Word | Printable)

Key: CORE-1769
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Anton Roeckenwagner
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Very slow execution of queries with IN or EXISTS clause statements

Created: 03/Mar/08 02:28 PM   Updated: 22/Feb/18 05:42 PM
Component/s: Engine
Affects Version/s: 2.0.1, 2.1 RC1
Fix Version/s: None

Environment: Windows XP Pro SP2
Issue Links:
Duplicate


 Description  « Hide
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:
http://tracker.firebirdsql.org/browse/CORE-1759

regards,
Anton Roeckenwagner

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.