Sql Join and Performance

Question

I have a question about JOINS. Does Sql JOINs reduce performance in a query? I have query with many JOIN in it . Can I say that the bad performance is come from these JOINS? if yes ,what should I do instead of JOIN in a query?

here is a piece of my query

...
FROM (((((tb_Pinnummern 
INNER JOIN tb_Fahrzeug 
    ON tb_Pinnummern.SG = tb_Fahrzeug.Motor_SG) 
INNER JOIN tb_bauteile 
    ON tb_Pinnummern.Bauteil = tb_bauteile.ID) 
LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Plus 
    ON Fehlercodes_akt_Liste_FC_Plus.ID = b_bauteile.[FC_Plus]) 
LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Minus 
    ON Fehlercodes_akt_Liste_FC_Minus.ID = b_bauteile.[FC_Minus]) 
LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Unterbrechung 
    ON Fehlercodes_akt_Liste_FC_Unterbrechung.ID = b_bauteile.[FC_Unterbrechung]) 
LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Aderschl 
    ON Fehlercodes_akt_Liste_FC_Aderschl.ID = b_bauteile.[FC_Aderschl]
    WHERE (((tb_Fahrzeug.ID) = [forms]![frm_fahrzeug]![id]));

see: stackoverflow.com/questions/1200295/sql-join-vs-in-performance , only way to get a definitive answer for your particular test is measure — Sam Saffron
– Sam Saffron, Commented May 23, 2012 at 7:06
also ... please format your query better, scrolling horizontally is impossible — Sam Saffron
– Sam Saffron, Commented May 23, 2012 at 7:07

Aakash Malhotra · Accepted Answer · 2017-12-05 20:18:59Z

2

Well joins obviously need to be processed and this processing will consume cpu, memory and IO.

As well as this we have to consider that joins can perform really, really badly if the right indexes etc are not in place.

However, an SQL join with the correct supporting indexes will produce the result you require faster than any other method.

Just consider what you would need to do to calculate the same result as your SQL above. Read the first table, then sort into the correct order, then read the second table and sort it then merge the two result sets before proceeding to the third table ...... Or read all the rows from the first table and for each row issue SQL to retrieve the matching rows.

edited Dec 5, 2017 at 20:18

Aakash Malhotra

5405 silver badges13 bronze badges

answered May 23, 2012 at 7:09

James Anderson

27.5k7 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

manurajhada · Accepted Answer · 2012-05-23 07:10:20Z

2

Yes it does.. increasing Number of records and joins among tables will increase time of execution.. A LEFT/RIGHT JOIN is absolutely not faster than an INNER JOIN. INDEXING on right column of tables will improve query performance.

If you have too much join in your query and its execution frequency is high, take an alternative i.e. create SQL VIEW or Materialized VIEW (Materialized VIEW - if you are using Oracle).

answered May 23, 2012 at 7:10

manurajhada

5,4004 gold badges28 silver badges43 bronze badges

Comments

navi · Accepted Answer · 2012-05-23 07:27:21Z

Joins will definitely degrade the performance the SQL query that you will be executing. You should generate the SQL plan for the SQL that you have written and look at methods to reduce the cost of the SQL. Any query analyzing tool should help you with that.

From what I understand in the query that you have defined above, you are trying to fetch all rows from the tables that are in the inner joins and get specific columns (if present) from the tables in the left join.

That being the case, a query written in the below given format should help :

select (select Fehlercodes_akt_Liste_FC_Plus.column1 from Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Plus where Fehlercodes_akt_Liste_FC_Plus.ID=tb_bauteile.[FC_Plus]),
    (select Fehlercodes_akt_Liste_FC_Minus.column2 from Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Minus where Fehlercodes_akt_Liste_FC_Minus.ID=tb_bauteile.[FC_Minus]),
    (select Fehlercodes_akt_Liste_FC_Unterbrechung.column3 from Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Unterbrechung where Fehlercodes_akt_Liste_FC_Unterbrechung.ID=tb_bauteile.[FC_Unterbrechung]),
    (select Fehlercodes_akt_Liste_FC_Aderschl.column4 from Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Aderschl where Fehlercodes_akt_Liste_FC_Aderschl.ID=tb_bauteile.[FC_Aderschl]),
    <other columns>
FROM
(tb_Pinnummern INNER JOIN tb_Fahrzeug ON tb_Pinnummern.SG = tb_Fahrzeug.Motor_SG)
INNER JOIN tb_bauteile ON tb_Pinnummern.Bauteil = tb_bauteile.ID) as <aliastablename>

WHERE <aliastablename>.ID=[forms]![frm_fahrzeug]![id];

Garry · Accepted Answer · 2012-05-23 07:03:59Z

-1

Sql Joins do not at all reduce performance : on the contrary : very often they will exponentially speed up a query, assuming offcousre the underlaying database model is well implemented. Indexes are very important in this matter.

answered May 23, 2012 at 7:03

Garry

414 bronze badges

Collectives™ on Stack Overflow

Sql Join and Performance

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related