I ran into a performance issue with System.Data.SQLite when using a SQLiteParameter with a LIKE clause. Consider the following queries:
Query A:
"SELECT * FROM SearchResults WHERE ContactName LIKE @ContactName"
Query B:
"SELECT * FROM SearchResults WHERE ContactName LIKE 'test'"
Query A takes 100 times longer than Query B. The table has a COLLATE NOCASE index on ContactName with about 300,000 records. Query A takes about 500-700 ms while Query B takes 5 ms. I have been attempting to resolve the issue on my end (see http://stackoverflow.com/questions/6427721/sqlite-net-performance-using-sqliteparameter-with-like-operator) but have been unsuccessful.
Thanks!
<hr /><i>mistachkin added on 2011-07-09 17:14:24 UTC:</i><br />
Could you provide the CREATE TABLE statement and some C# sample code that demonstrates the issue?
|