CODEWITHSUNDEEP
sql
Baper
Baper

Reputation: 1543

Sql Join and Performance

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

Upvotes: 2

Views: 3686

Answers (4)

James Anderson
James Anderson

Reputation: 27478

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.

Upvotes: 2

navi
navi

Reputation: 179

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

Upvotes: 0

manurajhada
manurajhada

Reputation: 5380

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

Upvotes: 2

Garry
Garry

Reputation: 41

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.

Upvotes: -1

Related Questions