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=tb_bauteile.[FC_Plus]) LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Minus ON Fehlercodes_akt_Liste_FC_Minus.ID=tb_bauteile.[FC_Minus]) LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Unterbrechung ON Fehlercodes_akt_Liste_FC_Unterbrechung.ID=tb_bauteile.[FC_Unterbrechung]) LEFT JOIN Fehlercodes_akt_Liste AS Fehlercodes_akt_Liste_FC_Aderschl ON Fehlercodes_akt_Liste_FC_Aderschl.ID=tb_bauteile.[FC_Aderschl]
WHERE (((tb_Fahrzeug.ID)=[forms]![frm_fahrzeug]![id]));

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

score 3 · Answer 1 · answered May 23 '12 at 07:10

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).

score 1 · Accepted Answer · edited Dec 05 '17 at 20:18

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.

score 0 · Answer 3 · answered May 23 '12 at 07:27

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];

score -1 · Answer 4 · answered May 23 '12 at 07:03

-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 '12 at 07:03

Garry

41
4

Sql JoiN and Performance

4 Answers4