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
1 - This issue should have been reported/discussed on the Support list.
2 - This is a well known issue, already reported, with the handling of IN clause statements.
3 - The statement/query can be re-written to use appropriate indexes. (Hint: SELECT ... FROM ( SELECT DISTINCT ADDRESSID FROM ORDERS_Table ...) JOIN ADDRESS_Table
I didn't find a reported issue with the handling of IN clause statements. Could you please link that issue(s)?
The query is generated by an o/r mapper, so i can't re-write the query. I would appreciate it very much, if this issue would be fixed, as i have (most probably) no other chance to get around this. I realy don't want to swap the database only because of this issue, as i'm very happy with firebird (besides this issue).
I guess a lot of users of o/r mappers will run into this issue. In my opinion the optimization of the IN statement is a very important thing.
Submitted by: Anton Roeckenwagner (roecki)
A relative simple query is running extremly slow. The following query takes about 45 seconds to complete:
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 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.
I couldn't find a reason, why this query runs so unacceptable slow. So I guess there is a bug in firebird.
Here is some additional information form the IBExpert Performance Analysis:
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)
Adapted Plan
------------------------------------------------
PLAN (apOrders INDEX (INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654, INTEG_654))
PLAN (address NATURAL)
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 42.516,00 ms
Avg fetch time: 6.073,71 ms
Memory
------------------------------------------------
Current: 34.918.376
Max : 35.133.556
Buffers: 2.048
Operations
------------------------------------------------
Read : 1.897
Writes : 0
Fetches: 36.208.955
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| address| 0 | 0 | 143681 | 0 | 0 | 0 |
| apOrders| 0 | 7182825 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
The text was updated successfully, but these errors were encountered: