You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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
The text was updated successfully, but these errors were encountered: