Showing posts with label fldname. Show all posts
Showing posts with label fldname. Show all posts

Friday, March 23, 2012

Optimize "LIKE"

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.

Using '%word%' will always scan every row in the entire table.

Hope this assists.

Optimize "LIKE"

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.

Using '%word%' will always scan every row in the entire table.

Hope this assists.