Speed cost for using "or" clause and functions on join statement

[Phillip]

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)

or should these statements go into the where clause?

[Brian]

Why are you using an OR clause within a JOIN clause? This is very
unorthodox. Simply continue joining namelist to members like this, where
"nl" is alias for namelist and "m" is alias for members:

join m on nl.ssn = m.id
join m on nl.custid = m.custid

OR clauses are notorious for slowing down a query - they only belong in the
WHERE clause, and even then you shoudl try to avoid them if possible

"Phillip" <pp*******@ECommunity.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

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)

or should these statements go into the where clause?

[Erland Sommarskog]

Phillip (pp*******@ECommunity.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.NameID,5) = Members.ID
OR left(namelist.SSN,9) = Members.ssn
Left(namelist.CustID,9) + '*01' = Members.CustID)
AND nl.name <> ''
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp