Does OR effect index 1

[wooody1245]

I have a store procedure to pull data from two different tables. This is a simplified version of the stored procedure:

select t1.fname, t1.lname, address from table1 t1
  inner join table2 t2 on t1.member_id = t2.member_id
where (table1.fname like @fname or table2.fname like @fname)
and (table1.lname like @lname or table2.lname like @lname)

This query takes about 1:30 to run. If I remove the or and have either table1 or table2, the indexes are used and the query takes about 5 seconds.

Why would OR affect the execution time?

 

[gmmastros]

Why would OR affect the execution time?

There are a lot of optimization techniques that the query engine will use to determine the execution plan. In this case, all rows from both tables must be joined together (based on member id) and then compared.

The exclusion of data cannot occur before the tables are joined. This will potentially add more comparisons and therefore take longer to execute. Add to that.... you cannot have an index that spans multiple tables, so you can pretty much guarantee that this will perform an index scan on the tables instead of the faster index seek.

There are some things you can do to speed this up. For example, you could use a union query to get a list of member_id's and then join that back tot he tables to get the names and addresses. This may speed up performance. Personally.... 1.5 minutes is unacceptable. You gotta figure something out.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Are the first and last names of the same person differnt in two differnt tables? If so you have much bigger problems than a slow running query! You have lost all data integrity and your data is unreliable. Clearly you have major design issues that need to urgently be addresed if this is so.

If they aren't different (and why are you storing them both places anyway), then you don't need the or condition.

If the actual people being named are differnt (one table contains workers and one contains managers for instance) then I think George's union idea might be for the best.

"NOTHING is more important in a database than integrity." ESquared