SQLite.NET performance using SQLiteParameter with LIKE statement

Question

I am having a problem using SQLiteParameters and the LIKE statement in a SQLite query. Here is a snippet of code, and I apologize if I don't have enough code here. If so, I can post more.

Poor performance:

using (OdysseyDataContext entities = new OdysseyDataContext()) { var results = entities.SearchResults.SqlQuery( "SELECT * FROM SearchResults WHERE ContactName LIKE @ContactName", new SQLiteParameter("@ContactName", "test") ); } 

Great performance:

 using (OdysseyDataContext entities = new OdysseyDataContext()) { var results = entities.SearchResults.SqlQuery( string.Format( "SELECT * FROM SearchResults WHERE ContactName LIKE '{0}'", "test" ) ); } 

Another important code:

 public class OdysseyDataContext : DbContext { public DbSet<SearchResult> SearchResults { get; set; } } public class SearchResult { [Key] public Guid Id { get; set; } public string ContactName { get; set; } } 

The first example runs 700 ms, which my supervisor considers unacceptable. The second example takes 7 ms to complete. Why is the difference? Is there something that I am doing completely wrong to earn the status of newcomers?

Thanks in advance!

Answer 1

So, I think I may have narrowed it down to a problem with System.Data.SQLite. I tried the following code in C ++:

 #include "sqlite3.h" #include <stdio.h> void xProfile(void* pArg, const char* query, sqlite3_uint64 pTimeTaken) { printf("%s\n", query); printf("%I64d ms\n", pTimeTaken / 1000000); } void PoorPerformance(); void GoodPerformance(); int main() { printf("Poor Performance:\n"); PoorPerformance(); printf("Good Performance:\n"); GoodPerformance(); return 0; } void PoorPerformance() { int rc; int rowCount = 0; sqlite3 *db; if (sqlite3_open("<<File Here>>", &db)) { printf("Could not open the database."); return; } sqlite3_profile(db, &xProfile, NULL); sqlite3_stmt *statement; if (!sqlite3_prepare_v2(db, "SELECT * FROM SearchResults WHERE ContactName LIKE @ContactName;", -1, &statement, 0)) { int result = 0; int parameterIndex = sqlite3_bind_parameter_index(statement, "@ContactName"); sqlite3_bind_text(statement, 1, "test", -1, NULL); while (result != SQLITE_DONE) { result = sqlite3_step(statement); if (result == SQLITE_ROW) { rowCount++; } } sqlite3_finalize(statement); } printf("%d rows\n", rowCount); sqlite3_close(db); } void GoodPerformance() { int rc; int rowCount = 0; sqlite3 *db; if (sqlite3_open("<<File Here>>", &db)) { printf("Could not open the database."); return; } sqlite3_profile(db, &xProfile, NULL); sqlite3_stmt *statement; if (!sqlite3_prepare_v2(db, "SELECT * FROM SearchResults WHERE ContactName LIKE 'test';", -1, &statement, 0)) { int result = 0; while (result != SQLITE_DONE) { result = sqlite3_step(statement); if (result == SQLITE_ROW) { rowCount++; } } sqlite3_finalize(statement); } printf("%d rows\n", rowCount); sqlite3_close(db); } 

Both the PoorPerformance and GoodPerformance functions gave 1 ms with 11 lines. Is there something else between what I did and what System.Data.SQLite should have done? Hope this is just what I can report as an error with System.Data.SQLite and maybe apply my own fix.

Answer 2

Since I do not see any difference between the two queries, but the fact that one uses sqliteparameter and the other is a full sql statement as a string, I just looked for your problem and came across that .

There, he points out that there is a ParameterCheck property on the SQLiteCommand object, which can lead to some performance loss.

You can try rewriting your code to pass an SQLiteCommand object and set the ParameterCheck property to false. I think you need to speed this up a bit.

At least it's worth taking a picture :)

Answer 3

I also had performance issues with System.Data.SQLite, some of which I was able to solve and improve, and others not.

However, I recently discovered this alternative C # SQLite library: http://code.google.com/p/csharp-sqlite/

It did not give me any performance problems, and I actually replaced System.Data.SQLite with this in an existing project (almost no changes in the syntax - I more or less literally just replaced the DLL and the usage directive. There were a couple of lines where I had to to attribute), and it was surprisingly surprising. There were times when I was expecting the order of seconds using System.Data.SQLite, and now the execution is instantaneous.