We are required to check our employee list, and our passenger manifests
regularly against a master list for possible matches. The criteria are two
matches per three field name record in any order, which works out to 18
possible matching combinations. So a record like:
"Mike William Davis" could be a match with "William Blake Davis" or
"Davis William Miller", or even "William <blank> Davis".
I am looking for the best way to speed this query up. both tables in
question have the first, middle and last name fields indexed. there are
18000 records in the tsa table and a few hundred in the employee table. In
addition to the code examples below, I have tried using cursors as well, but
those results are still well above 10 seconds.
So, I have written the base query in the following syntax(this is the best
timed result, but the execution plan for the stored proc it is in reports
it's cost at 94% of the batch total, so I have the potential to make this
thing really fast, I hope):
(Average Execution Time for all 18 combinations is 5 seconds +/- 3 seconds)
SELECT e.first,e.middle,e.last,l.firstname,l.middlename,l.lastname,l.sid,0
FROM pax_employees e,pax_tsa_no_fly_list l
Where l.firstname = e.first
AND l.middlename = e.middle
UNION
SELECT e.first,e.middle,e.last,l.firstname,l.middlename,l.lastname,l.sid,0
FROM pax_employees e,pax_tsa_no_fly_list l
Where l.firstname = e.first
aND l.middlename = e.last
UNION
SELECT e.first,e.middle,e.last,l.firstname,l.middlename,l.lastname,l.sid,0
FROM pax_employees e,pax_tsa_no_fly_list l
Where l.firstname = e.first
AND l.lastname = e.middle
And so on with the other 15 combinations
I have tried many different iterations of this, also trying the following
syntax:
(This syntax runs the slowest, averaging over 35 seconds for all 18
combinations)
SELECT e.first,e.middle,e.last,l.firstname,l.middlename,l.lastname,l.sid,0
FROM pax_employees e JOIN pax_tsa_no_fly_list l ON
(l.firstname = e.first
AND l.middlename = e.middle
)
OR
( l.firstname = e.first
AND l.middlename = e.last
)
OR ( l.firstname = e.first
AND l.lastname = e.middle)
And so on with the other 15 combinations
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
