Phillip (pp*******@ECom munity.com) writes:
Select member
from NameList
Inner join Members
on (Left(Namelist. NameID,5) = Members.ID
OR (left(namelist. SSN,9) = Members.ssn
OR (Left(namelist. CustID,9) + '*01' = Members.CustID)
where
namelist.name <> ''
How do I speed up a process like this? Can I create indexes on the
members table based on a function
Like an index based on the left(members.id ,5)
There are at least two problems with your query in terms of performance.
1) Say that you have an index on Namelist.NameID . That index is not very
useful here, because NameID appears in an expression. There for SQL Server
cannot seek the index. That is, look up a value using the index tree. At
best SQL Server can scan the index.
2) As Brian pointed the OR clauses can be problematic.
However, if you have non-clustered indexes (member, NameID, name),
(name, SSN, member) and (member, CustID, name), SQL Server can scan
the three indexes and do index intersection.
And, yes could create indexes on a computed column of which the
vale is leff(NameID, 5).
Then again, if NameList is the small table, and Members the big one,
this matters little anyway.
Finally, my guess is that you query is best expressed as:
SELECT nl.member
FROM NameList nl
WHERE EXISTS (SELECT *
FROM Members m
WHERE Left(Namelist.N ameID,5) = Members.ID
OR left(namelist.S SN,9) = Members.ssn
Left(namelist.C ustID,9) + '*01' = Members.CustID)
AND nl.name <> ''
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp