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
Extremely slow executing a cross join of 3 tables in Firebird 2.X [CORE2200] #2628
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: Chau Chee Yang (cheeyang) Firebird 2.X1. Extract all files from the zip archive. Firebird 1.5.X1. Restore db_1_5.fbk into firebird 1.5 server |
Modified by: Chau Chee Yang (cheeyang)Attachment: CORE.2200.zip [ 11173 ] |
Modified by: @asfernandessummary: Extremely slow executing a natural join of 3 tables in Firebird 2.X => Extremely slow executing a cross join of 3 tables in Firebird 2.X |
Commented by: @dyemanov The problem is slightly unusual. With a cross join without any predicates, it actually makes no difference which table to start from. FB 1.5 ignores this fact and always tries to start from a smaller table, as for a regular predicate-driven join. FB 2.0 pretends to be smarter and avoids the [sometimes expensive] cost calculation, starting from the any independent table (in the current implementation, this is the last one in the list). In fact, both solutions should deliver nearly the same performance in all cases but one: when any of the tables is empty. In this case, it would be ideal to put it into the first place in order to skip reading other tables at all. But I wouldn't say that processing empty tables in cross joins is a common real-world practice, so perhaps you're the only person affected :-) So, I see three possibilities here: 1) Remove the clever "fast-route" code and rely on costs, as in FB 1.5. This is dumb but it handles the empty table case automagically. |
Commented by: Sean Leyne (seanleyne) My answer: 3) Do nothing and treat this as a "no issue" Natural Join are the dumbest thing that a user/developer could do. |
Modified by: @dyemanovpriority: Critical [ 2 ] => Minor [ 4 ] |
Commented by: Chau Chee Yang (cheeyang) The reason I wrote this natural join query is to retrieve a structure (metadata). My statement may have 2, 3, 4, or 5 statements. It is costly if I perform checking on the row count in each table and re-arrange the join tables sequence. I personally feel this is an issue as MSSQL or MYSQL doesn't has this problem at all. It should be a problem in Firebird 2.X engine that didn't handle this case well. Please do consider to fix it. Thank you. |
Commented by: @dyemanov I'm going to implement a fourth option, which should work fine regardless of the optimizer decision :-) |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1.2 [ 10270 ] Fix Version: 2.5 Beta 1 [ 10251 ] Fix Version: 2.0.6 [ 10303 ] |
Commented by: Sean Leyne (seanleyne) Dmirty, so what is the fourth option? |
Commented by: @dyemanov Improve the cross join algorithm to stop as soon as any of the involved streams is detected as empty. |
Commented by: Chau Chee Yang (cheeyang) Thank you very much |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @pavel-zotov IMO, engine should not begin ANY kind of join (not only cross) when absence of rows in at least one of it's data sources forces final result to be empty. Sample:recreate table t0(id int); insert into t1 select * from t0; set stat on; select 1 select 1 select 1 Result:select 1 select 1 select 1 Trace:Query-1: Table Natural Index Query-2: Table Natural Index Query-3: Table Natural Index All scans of tables `t1` & `t2` looks like wasted labour... |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: No test => Done successfully |
Submitted by: Chau Chee Yang (cheeyang)
Relate to CORE3127
Attachments:
CORE.2200.zip
I have 3 tables:
1. Agent (0 row)
2. AR_PM (4,300+ rows)
3. AR_KnockOff (18,000+ rows)
I execute the following statement in Firebird 2.1.1:
SELECT A.Code FROM AGENT A, AR_PM B, AR_KnockOff C
It takes long times to get the first response back to my client software (IB Expert). However, this situation doesn't happen on Firebird 1.5 running the same query and same amount of data.
Case 1:
Firebird 2.1.1: 173 seconds
Firebird 1.5.X: 0 seconds
Case 2:
If I append one row into AGENT table, I get the response immediately after running the query:
Firebird 2.1.1: 0.093 seconds
Firebird 1.5.X: 0.031 seconds
I suspect there is some problem with Firebird 2.X. I also attach my database backup for both Firebird 1.5 and 2.1 for you to try out. Please pay attention on the time spent to run the query on Case 1.
Commits: 6e4c0cd 8120245 810f89b 84cd9b7 6220614 939d262 c77de9d
The text was updated successfully, but these errors were encountered: