System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: ad3705a4f46f28de5076e82de95ef718ef936f70
Title: LIKE Performance Issue
Status: Closed Type: Performance
Severity: Important Priority: Immediate
Subsystem: LINQ Resolution: Unable_To_Reproduce
Last Modified: 2011-10-11 05:05:32
Version Found In: 1.0.74
Description:
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!


mistachkin added on 2011-07-09 17:14:24 UTC:
Could you provide the CREATE TABLE statement and some C# sample code that demonstrates the issue?