System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 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!

<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?