>>Yup, that's the same plan I'm getting.
>>
>>I'm sure there's a reason, but I'll ask - why the text index on VehicleModel but not on VehicleMake?
>
>Data model was created before I came there. New systems are based on a Make and Model table using integer, thus, not having to deal with those problematic situations in searches. But, that one, it is still in the old approach. The client has entered data in any kind of ways in the vehicle model. So, it could be "Rav 4", "Civic Hatchback SI", etc. So, they want to be able to search for string inside the vehicle model. As for the vehicle make, this is usually only one word, and knowing the problematic full text search causes, they agree to not allow that type of search on that one.
OK, admittedly I don't do a lot with full text searches, though I don't recall seeing text indexes on a column so small (just 30 characters). So if anyone who has done this often wants to jump in....
I do have two thoughts:
- If you can reproduce this in a test environment and want to try something...try to do an UPDATE STATISTICS dbo.Client, and then try the query again. There are documented cases where a simple update of statistics will speed up queries on text indexes
- With some fine-tuning of the LIKE statement, you "might" be able to get away with using a simple LIKE and avoiding the CONTAINS.
These are just educated guesses - it's up to you if you want to try them.