Why is "Like" faster than "=" 1

[VB400]

I have a query that selects records based on LastName and FirstName (the table has an index by LastName, FirstName):

SELECT * FROM CUSTOMERS WHERE LastName = 'Smith' AND FirstName = 'Susan'

This takes an average of 24 seconds to return 88 records

If I change the SQL statement to:

SELECT * FROM CUSTOMERS WHERE LastName = 'Smith' AND FirstName LIKE 'Susan'

It then takes less than one second to return the same data -- No other parameters are involved.

What is it that causes this? Tarek

 

[tlbroadbent]

In Query Analyzer, select "Display Estimated Execution Plan" for each query and see where if and how the queries use the indexes. You'll be able to see where the bottlenecks occur in each query - find the steps with highest cost. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[VB400]

Terry,

I just did that and there is a difference:

When I use "=", SQL Server uses Table Scan

When I use "Like", SQL Server uses Index Seek, Filter and Bookmark Lookup

Why is this happening? Tarek

 

[tlbroadbent]

Those are the results I would expect. What I don't expect is the timing difference. Run UPDATE STATISTICS tablename and see if that makes a difference. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[VB400]

That didn't seem to help! However, I changed the index to be clustered and that made all the difference in the world.

Is this the right thing to do? I'm new to SQL Server so I'm not yet familiar with the "Standard Operating Procedures". It is my understanding that you can only have one clustered index and since this index is my main/only way of searching for customers, it seems logical to cluster this index.

Any thoughts/advise on that? Tarek

 

[tlbroadbent]

Usually, it makes sense to have a clustered index on the most frequently searched columns. I think I understand now why the query was taking so long. Seeking on the non-clustered index obtained pointers to the data. SQL then had to get the data pages. With the clustered index the index and data are on the same page. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[VB400]

So, eventhough it was trial and error on my part, it seems to have been the right solution in this case!

-- I knew that -- X-)

Thanks for your help Terry Tarek