Alternative for LEFT JOIN
-
Hi,
I have a comlex select query which uses lot of left joins. Most of the left joins are made on the same table specific with a column's value.
The simple format of the select query woulb be like
select Tabl1.id,B.col1 as X,C.col1 as Y,D.col1 as Z
FROM Tabl1 LEFT JOIN Tabl2 as B on(a.id=b.id and B.col2='X')
LEFT JOIN Tabl2 as C on(A.id=C.id and C.col2='Y')
LEFT JOIN Tabl2 as D on(A.id=D.id and D.col2='Z')
The number of LEFT JOINS used is nearly 18, so the query takes more time. Do u have any alternate ways so that the query can be altered to get results faster
-
Apply index on Tabl1.id, clustered index on (Tabl2.Id + Tabl2.col2)
And think again if you really need such fat recordset.
Problem is not in LEFT JOIN, problem is in application design.
_____________
Code for TallyGenerator -
The Tables are using proper indexes
-
What do you mean "proper"?
If there is a clustered index on Tabl2.Id?
Actually the structure you described repeates the structure of system tables sysobjects and syscolumns. And it takes no time to get the list of columns for any particular table as a header for a recordset.
Why you cannot achieve the same performance with your tables?
_____________
Code for TallyGenerator -
What we did in a similar situation was to create a dummy key (say id=0) and tehn populated all the tables that had an id of NULL to 0 and changed the applciation to also do that. This way we could always do an Inner Join which helped performance tremendously.
-
hi
use * after '=' notation
for eg:
Select * from Employee E, Department D
where E.DeptCode =* D.DeptCOde
-
Noooo.... don't use *=. It is generally the same as LEFT JOIN, only it is written in an "old" way, which means that it is not fully supported in recent version (in certain situations gives incorrect - or at least not expected - results) and may stop working at all in near future.
-
Aravind, try this solution and see if it does the trick for you
SELECT a.ID,
MAX(CASE WHEN b.Col2 = 'X' THEN b.Col1 END) X,
MAX(CASE WHEN b.Col2 = 'Y' THEN b.Col1 END) Y,
MAX(CASE WHEN b.Col2 = 'Z' THEN b.Col1 END) Z -- This row and 15 more like it
FROM Tabl1 a
LEFT JOIN Tabl2 b ON b.ID = a.ID
GROUP BY a.ID
N 56°04'39.16"
E 12°55'05.25"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply