|
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. 2) Add a special check for empty tables into the "fast-route" code. 3) Do nothing and treat this as a "no issue". My answer:
3) Do nothing and treat this as a "no issue" Natural Join are the dumbest thing that a user/developer could do. 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. I'm going to implement a fourth option, which should work fine regardless of the optimizer decision :-)
Dmirty, so what is the fourth option?
Improve the cross join algorithm to stop as soon as any of the involved streams is detected as empty.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Improvement
Closed
Minor
Extremely slow executing a cross join of 3 tables in Firebird 2.X
=========
1. Extract all files from the zip archive.
2. Restore db_2_0.fbk into firebird 2.X server
3. Execute statement: SELECT A.Code FROM AGENT A, AR_PM B, AR_KnockOff C
4. It takes more than 170 seconds to response
Firebird 1.5.X
===========
1. Restore db_1_5.fbk into firebird 1.5 server
2. Execute statement: SELECT A.Code FROM AGENT A, AR_PM B, AR_KnockOff C
3. you get immediate response