470,826 Members | 1,818 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,826 developers. It's quick & easy.

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

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?

Jul 23 '05 #1
2 2038
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?

Jul 23 '05 #2
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Sara Khalatbari | last post: by
3 posts views Thread by Colleyville Alan | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.