Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Simple query runs extremly slow (up to multiple hours) [CORE1759] #2183

Closed
firebird-automations opened this issue Feb 24, 2008 · 4 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Anton Roeckenwagner (roecki)

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.

Thanks,
Anton

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant