SQLite.NET Performance Using SQLiteParameter with LIKE operator

Question

I am having an issue using SQLiteParameters and the LIKE operator in a SQLite query. Here is a snippet of code, and I apologize if I don't have enough code here. If that is the case, I can easily 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"
        )
    );
}

Other 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 takes 700 ms to execute, which my supervisor finds unacceptable. The second example takes 7 ms to execute. Why the difference? Is there something I am doing completely wrong to earn me newbie status?

Thanks in advance!

Are you running multiple tests to eliminate caching artefacts? Do they both return the same rows? Are you using wildcards? — Tim
– Tim, Commented Jun 21, 2011 at 19:15
Also, in implementations of SQLite where the LIKE function is overridden, indexes are unavailable. What do you get with ...where contactname GLOB @contactname [case sensitive, BTW] ? — Tim
– Tim, Commented Jun 21, 2011 at 19:24
Hi Tim. Yeah, I'm running multiple tests. The first time is a little over 1 second and the average over 15 tests is about 700 ms. Yes, they return the same rows. No, I'm not using any wild cards. I know it seems strange, but I'm using the like statement to remove the case sensitive searching so it will use my index setup on ContactName COLLATE NOCASE. — Terry
– Terry, Commented Jun 21, 2011 at 19:55
In your "great performance" query, you're missing a closing double-quote. Doubt that has anything to do with your issue, but just thought I'd bring it up. — user610650
– user610650, Commented Jun 29, 2011 at 14:46
Can we see the SqlQuery method source? Are you sure it's the query itself you are timing; or is it including the overhead of creating a parameter and adding it to the collection? (It should be almost no overhead, but we won't know till we see the method's source) — vcsjones
– vcsjones, Commented Jun 29, 2011 at 17:10

Terry · Accepted Answer · 2011-07-08 15:27:28Z

So, I think I may have narrowed it down to an issue 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 yielded 1 ms with 11 rows. Is there something different between what I did and what should have been done by System.Data.SQLite? Hopefully this is just something I can report as a bug with System.Data.SQLite and perhaps apply my own fix.

Do you have any update on your issue with System.Data.SQLite?

Gambrinus · Accepted Answer · 2011-07-05 16:43:27Z

1

Since I cannot see any difference between the two queries, but the fact, that one uses sqliteparameter and the other one a complete sql-statement as string - I just googled your problem and stumbled upon that.

There it indicates that on the SQLiteCommand object there is a property called ParameterCheck, which can cause some performance loss.

You could try to rewrite your code to pass a SQLiteCommand object and set the ParameterCheck property to false. I think you should gain some speed doing this.

At least it's worth a shot :)

answered Jul 5, 2011 at 16:43

Gambrinus

2,13616 silver badges26 bronze badges

1 Comment

Terry Over a year ago

I was so hoping that this would solve my problem! Unfortunately this uses Devart.Data.SQLite rather than System.Data.SQLite.

user1032657 · Accepted Answer · 2012-04-15 05:16:55Z

0

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

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

It has given me no performance issues, and I actually replaced System.Data.SQLite with this one in an existing project (almost no changes in syntax - I more or less literally just replaced the DLL and the using directive.. there were a couple lines where I had to typecast something), and it sped things up marvelously. There were times where I was waiting on the order of seconds with System.Data.SQLite, and now the executions are instantaneous.

answered Apr 15, 2012 at 5:16

user1032657

2,4816 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

SQLite.NET Performance Using SQLiteParameter with LIKE operator

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related