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
OUTER JOIN INEFFICIENT if foreign key exists (especially with views) [CORE1940] #2379
Comments
Modified by: Tim Kelly (m00bh000)description: Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined: Consider the following: CREATE TABLE_A CREATE TABLE_B SELECT TABLE_A.PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK CREATE VIEW VIEW_A SELECT * FROM VIEW_A CREATE VIEW VIEW_B SELECT FIELD_A from VIEW_B => Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined: Consider the following: CREATE TABLE_A CREATE TABLE_B SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK //According to performance analysis TABLE_B is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. You may think big deal serve the silly DBA for writing such a stupid query, but consider: CREATE VIEW VIEW_B SELECT FIELD_A from VIEW_B Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out? |
Commented by: Tim Kelly (m00bh000) If the answer is no I suspect it has something to do with pre-compiled PLAN's ?! |
Modified by: Tim Kelly (m00bh000)description: Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined: Consider the following: CREATE TABLE_A CREATE TABLE_B SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK //According to performance analysis TABLE_B is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. You may think big deal serve the silly DBA for writing such a stupid query, but consider: CREATE VIEW VIEW_B SELECT FIELD_A from VIEW_B Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out? => Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined: Consider the following: CREATE TABLE_A CREATE TABLE_B ALTER TABLE TABLE_A ADD PRIMARY KEY (PK); ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK); SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK //According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider: CREATE VIEW VIEW_B SELECT FIELD_A from VIEW_B Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out? summary: OUTER JOIN INEFFICIENT (especially with views) => OUTER JOIN INEFFICIENT if foreign key exists (especially with views) |
Modified by: Tim Kelly (m00bh000)description: Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined: Consider the following: CREATE TABLE_A CREATE TABLE_B ALTER TABLE TABLE_A ADD PRIMARY KEY (PK); ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK); SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK //According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider: CREATE VIEW VIEW_B SELECT FIELD_A from VIEW_B Not so silly now, but inefficient as TABLE_B is unnecessarily queried (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out? => Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined: Consider the following: CREATE TABLE_A CREATE TABLE_B ALTER TABLE TABLE_A ADD PRIMARY KEY (PK); ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK); SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK //According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider: CREATE VIEW VIEW_B SELECT FIELD_A from VIEW_B Not so silly now, but inefficient as TABLE_B's index is unnecessarily fetched (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out? |
Commented by: Sean Leyne (seanleyne) Tim, 1 - You have not provided details regarding the PLANs generated for the statements/Views. Without these details it is not possible to prove/disprove your concerns. 2 - The name of your analyzer would be also valuable, since the issue may be a known problem with the tool. 3 - I believe that you are confusing/misreading the PLAN details, since the statements provided require that Table B be accessed in order to to resolve FIELD_B in the VIEW_B. 4 - Did you perform COMMITs after the Primary Keys and Constraints were defined? |
Commented by: @dyemanov Honestly, I doubt the optimizer should be that clever. Some people use JOINs in the meaning similar to IN/EXISTS, i.e. to ensure that there's a corresponding record in the linked table. They don't need any data from the linked table(s). With the suggested optimization, their queries won't work anymore, although they're pretty valid from the SQL standard point of view. |
Commented by: Smirnoff Serg (wildsery) Moreover, this query may return more than one record on each record for TABLE_A. |
Commented by: Tim Kelly (m00bh000) Thanks for your quick response. I am using a trial version of SQL Manager 2008. And the Create Index's were just to give you an Please compare the two queries below. The two queries can only ever return the same result set, but as you can see In practise my queries are machine generated, at compile time so i don't know which fields i will need. ========================================================================= SELECT FIELD_A PLAN (TABLE_A NATURAL) Query Time Memory Operations Plan: Enchanced Info: ===========================================================================SELECT FIELD_A PLAN JOIN (TABLE_A NATURAL, TABLE_B INDEX (RDB$PRIMARY20)) Query Time Memory Operations Plan: Enchanced Info: |
Commented by: Tim Kelly (m00bh000) Dmitry this is a LEFT JOIN so it does not matter if there's a corresponding record in the linked table or not! |
Commented by: Tim Kelly (m00bh000) Smirnoff - "Moreover, this query may return more than one record on each record for TABLE_A." Good point, I hadn't thought of that although as you point out my keys prevent this from occuring, mayb it is too much to expect it to work it out! To give you a bit of background i've got a number of tables which each add a level of inheritence to an objects properties, say: ADDRESS <- PERSON <- EMPLOYEE <- SUPERVISOR So a supervisor's properties are spread over the four tables. I just happened to notice that when I was only requesting properties stored in the SUPERVISOR table It just seemed a shame to keep querying the ADDRESS table when for example I only wanted to know the EMPLOYEE'S hard hat size! |
Commented by: Tim Kelly (m00bh000) Actually having thought about it in the bath I don't think it would be rocket science for this to be optimized, although I have never dared even look at the fire bird source! For simple 1 column joins, pseudocode: void OptimizeLeftJoin(JOIN) ...I suspect this pseudo-code might cause firebird to no longer compile if it was copy-and-pasted! But at least I hope I made some1 chuckle?! |
Submitted by: Tim Kelly (m00bh000)
Unless my query performance analyzer is crap or I am being a loony, I think there is scope for greater efficiency by ignoring redundant tables which are outer joined:
Consider the following:
CREATE TABLE_A
(pk INTEGER, FIELD_A integer)
CREATE TABLE_B
(pk INTEGER, FIELD_B integer)
ALTER TABLE TABLE_A ADD PRIMARY KEY (PK);
ALTER TABLE TABLE_B ADD PRIMARY KEY (PK);
ALTER TABLE TABLE_B ADD FOREIGN KEY (PK) REFERENCES TABLE_A(PK);
SELECT TABLE_A.PK, FIELD_A FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK
//According to performance analysis TABLE_B's index is queried even though clearly no fields are required from it and because it's left joined it can't affect the rows returned from table_A. The weird thing is that if the index doesn't exist then TABLE_B is optimized out! You may think big deal serve the silly DBA for writing such a stupid query, but consider:
CREATE VIEW VIEW_B
AS
SELECT TABLE_A.PK as PK, FIELD_A, FIELD_B FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.PK = TABLE_B.PK
SELECT FIELD_A from VIEW_B
Not so silly now, but inefficient as TABLE_B's index is unnecessarily fetched (according to my analyzer). Can't we detect that TABLE_B is not actually required at all and optimize it out?
The text was updated successfully, but these errors were encountered: