Optimize query 2

[TheWebDataGuy]

Here is a search query I would like to optimize. The database is de-normalized to store 6 StaffIDs in the Applications table. We need to lookup applicaitons by employee names. Currently it takes a loooong time.

SELECT *
FROM Applications a
LEFT OUTER JOIN Employee e ON m.StaffID = e.StaffID
LEFT OUTER JOIN Employee e1 ON m.StaffID1 = e1.StaffID
LEFT OUTER JOIN Employeee e2 ON m.StaffID2 = e2.StaffID
LEFT OUTER JOIN Employeee e3 ON m.StaffID3 = e3.StaffID
LEFT OUTER JOIN Employeee e4 ON m.StaffID4 = e4.StaffID
LEFT OUTER JOIN Employeee e5 ON m.StaffID5 = e5.StaffID
WHERE (e.LastName = 'LastName')
OR (e1.LastName = 'LastName') OR (e2.LastName = 'LastName') OR (e3.LastName = 'LastName') OR (e4.LastName = 'LastName') OR (e5.LastName = 'LastName')
ORDER BY m.AppID DESC

Thanks in advance.

 

[mrdenny]

I'm not sure how well this will work without testing

select * from Applications a
left outer join employee e on (a.StaffID = e.StaffID)
   or (a.StaffID1 = e.StaffID)
   or (a.StaffID2 = e.StaffID)
   or (a.StaffID3 = e.StaffID)
   or (a.StaffID4 = e.StaffID)
   or (a.StaffID5 = e.StaffID)
where e.LastName = 'LastName'
order by a.AppID DESC

Also make sure that your FK Columns are properly indexed.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

First get rid of the select *, you are returning more data columns than you need. Select * is a poor programming practice and should not ever be used when you have a join.

Hopefully you have indexes on staffid in both tables and on LAst Name.

m.StaffID what table is this? It is not referenced anywhere. The alias for applications is a.

Incidentally this structure is bad and should be fixed. There is a reason why you should normalize information like this. Then you don't have to do 6 joins where 2 would do. This is creating alot of extra overhead.

Questions about posting. See faq183-874

 

[dmcmunn]

/*
If you must put up with this heinous design then clean it up some...
*/

CREATE VIEW Application_Staff
(
AppID
, StaffID
)

AS

select Applications.AppID
, Applications.StaffID
from Applications
where StaffID is not null
union all
select Applications.AppID
, Applications.StaffID1
from Applications
where StaffID1 is not null
union all
select Applications.AppID
, Applications.StaffID2
from Applications
where StaffID2 is not null
union all
select Applications.AppID
, Applications.StaffID3
from Applications
where StaffID3 is not null
union all
select Applications.AppID
, Applications.StaffID4
from Applications
where StaffID4 is not null
union all
select Applications.AppID
, Applications.StaffID5
from Applications
where StaffID5 is not null



-- then your query would look like this...

SELECT m.AppID
, m.StaffID
, e.LastName

FROM Application_Staff m

LEFT OUTER JOIN Employee e
ON m.StaffID = e.StaffID

WHERE e.LastName = 'LastName'

ORDER BY m.AppID DESC



-- OR You could do it this way, if you prefer inline tables...

SELECT m.AppID
, m.StaffID
, e.LastName

FROM (
select Applications.AppID
, Applications.StaffID as StaffID
from Applications
where StaffID is not null
union all
select Applications.AppID
, Applications.StaffID1 as StaffID
from Applications
where StaffID1 is not null
union all
select Applications.AppID
, Applications.StaffID2 as StaffID
from Applications
where StaffID2 is not null
union all
select Applications.AppID
, Applications.StaffID3 as StaffID
from Applications
where StaffID3 is not null
union all
select Applications.AppID
, Applications.StaffID4 as StaffID
from Applications
where StaffID4 is not null
union all
select Applications.AppID
, Applications.StaffID5 as StaffID
from Applications
where StaffID5 is not null
) m

LEFT OUTER JOIN Employee e
ON m.StaffID = e.StaffID

WHERE e.LastName = 'LastName'

ORDER BY m.AppID DESC

 

[vongrunt]

IMO query by mrdenny is OK... no extra joins, no unions. One minor improvement: change LEFT to INNER JOIN:

select <columns_you_wish>
from Applications A
inner join Employee E on E.StaffID in ( A.StaffID1, A.StaffID2, A.StaffID3, A.StaffID4, A.StaffID5, A.StaffID6 )
where E.LastName = 'LastName'
order by A.AppID desc

About indexes:
- over Employee.LastName definitely - it will likely tell optimizer to use less heavy bookmark lookup operator.
- over Applications.Staff1ID ... Staff6ID - no.

 

[TheWebDataGuy]

Thanks, guys, for your inputs.
I know I need to fix the DB design. Its high on my list.