|
|
Posted by Erland Sommarskog on 05/05/06 00:40
das (Adityanad@gmail.com) writes:
> SELECT * FROM EMPLOYEE e
> INNER JOIN
> EMPLOYEE_BENEFITS eb ON (e.employee_id = eb.employee_entity OR
> e.empsid_id = eb.employee_entity)
> INNER JOIN
> EMPLOYEE_TRACK et ON eb.employee_benefits_id = et.employee_track_entity
>
>
> The above SQL I wrote is this: the second inner join uses a OR to join
> either of the columns in the first table EMPLOYEE. There is performance
> degradation with this SQL. With huge data It takes about 30 seconds to
> execute. I know this is not the perfect way to do it, can anyone of the
> SQL Gurus please enlighten me to a faster approach?
You could try using UNION:
SELECT *
FROM EMPLOYEE e
JOIN EMPLOYEE_BENEFITS eb ON e.employee_id = eb.employee_entity
JOIN EMPLOYEE_TRACK et
ON eb.employee_benefits_id = et.employee_track_entity
UNION
SELECT *
FROM EMPLOYEE e
JOIN EMPLOYEE_BENEFITS eb ON e.empsid_id = eb.employee_entity
JOIN EMPLOYEE_TRACK et
ON eb.employee_benefits_id = et.employee_track_entity
Or even:
SELECT *
FROM (SELECT *
FROM EMPLOYEE e
JOIN EMPLOYEE_BENEFITS eb ON e.employee_id = eb.employee_entity
UNION
FROM EMPLOYEE e
JOIN EMPLOYEE_BENEFITS eb ON e.empsid_id = eb.employee_entity)
AS eb
JOIN EMPLOYEE_TRACK et ON
eb.employee_benefits_id = et.employee_track_entity
The latter is uses a derived table and is more compact. Which performs
the best, I don't know.
A derived table is logically a temp table within the query, but SQL Server
may recast computation order. They are a very powerful tool to write
complex SQL queries.
You may have to replace the * in the derived table, if there are name
clashes between the tables.
> If I dont use the OR I can try left join on the same table
> EMPLOYEE_BENEFITS twice by changing the join types, but If I did this
> what table alias can I use to join to the 3rd table?
>
> SELECT * FROM EMPLOYEE e
> LEFT JOIN
> EMPLOYEE_BENEFITS eb1 ON e.employee_id = eb.employee_entity
> LEFT JOIN
> EMPLOYEE_BENEFITS eb2 ON e.empsid_id = eb.employee_entity
> INNER JOIN
> EMPLOYEE_TRACK et ON [???].employee_benefits_id =
> et.employee_track_entity
You would have to write
coalaesce(eb1.employee_benefits_id, eb2.employee_benefits_id)
but I would stay away from this solution.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|