Printable View
I have a sql join 3 tables, like this:
select *
from tableA a
left join tableB b
on a.id1 = b.id1
left join tableC c
on b.id2 = c.id2
It works with the correct result, but takes so long time. For example, in each table, there is about 300 rows --> the above query takes ~ 20 seconds.
Does anyone know how to improve in this case?
Create proper indexes. It will improve the performance.
Also check execution plan of your statement.
try to make the smaller table (as per rows) as your left most table. That might make so difference.
[QUOTE=debasisdas;32392]try to make the smaller table (as per rows) as your left most table. That might make so difference.[/QUOTE]
The very purpose of the left outer join is to get the records meets the join condition + rest of the rows from left table.
The left most table will the table that he wants to get all the records whether it satisfies the join condition or not.
Changing the smaller table as left most table may not serve its purpose.
Select only the desired columns in the 'select' statement
Indexing the best option in this situation
Are the index created on the tables? Again you have only 300 records so creating an index might not work out for you becuase the data volume is very very less.
Since the query has " Select * ", server will not able to understand which " id "column to select . So please specify all the common columns in the select statement. This will help you.
Check all the ids columns in the where clause have values. Could be because of null values in the ids columns , if these are ids then must have unique values. Performance could be because of your data.