March 27, 2012 at 6:11 am
Hi, just my 2cents. In your scenario, it make sense to use full text index. It will out perform the LIKE by about 10 fold.
However, in your table, the engine is using the different Index because of computed column + ' ' +, which it cannot tell from your search string. You can, however, force it to use the correct index by using:
From tablename with( index( index name ) )
This is a good example why the with index clause is made for. Hope this helps you.
P/s you should really look into full text.
March 27, 2012 at 6:40 am
I think you absolutely, positively must split the inputs into a firstname and lastname in the UI. That must be part of an efficient solution I think, and it is much cleaner AND you can search on only one OR the other AND both if you choose.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 27, 2012 at 8:38 am
From a result correctness standpoint, also check your data for where suffixes can go; some systems put them in first name, some in last name, some in a separate field, and some have them in any or all of the above on a row by row basis.
"Bob" "Smith" is boring (except, perhaps, when it's stored as "Robert" "Schmythe").
"Roberto, Jr." "O'Hallahan-Humperdink MVP, AMA, PRC" is a little more interesting.
March 27, 2012 at 9:50 pm
I appreciate the feedback. The reason I've resisted splitting the names is that the part I'm not telling you is that what they have done is put a google like text box on the UI where users can type in any part of a name, or type in a member number and the search figures out what they're trying to do. It's quite intuitive actually, so I've been just trying to make it work. Thanks to everyone here, I've done that. It's working very well now that I've optimized it. The first search has gone from 1:20 on our developer laptops to virtually instantaneous. I'm a hero.
.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply