CODEWITHSUNDEEP
c#databasesqlitequery-optimization
Parimal Raj
Parimal Raj

Reputation: 20575

SQLite - Fastest way to read Data from SQLite Database?

i have a local SQLite database

TABLE DETAILS

-- Describe PREFIX_LIST
CREATE TABLE PREFIX_LIST(ITEM VARCHAR(25) PRIMARY KEY)

-- Describe SUFFIX_LIST
CREATE TABLE SUFFIX_LIST(ITEM VARCHAR(25) PRIMARY KEY)

-- Describe VALID_LIST
CREATE TABLE VALID_LIST (
    "PART1" TEXT,
    "PART2" TEXT,
    PRIMARY KEY(PART1, PART2)
)

now this list is really huge, and i need need to save data from it.

Here is my implementation.

SQLiteConnection con = null;
SQLiteCommand cmd = null;
Connect(DbPath, ref con, ref cmd);

cmd.CommandText =
    "SELECT PART1 || '@' || PART2 FROM VALID_LIST 
 WHERE NOT EXISTS 
   (SELECT * FROM PREFIX_LIST WHERE VALID_LIST.PART1 LIKE '%' || ITEM || '%') 
   AND NOT EXISTS
   (SELECT * FROM SUFFIX_LIST WHERE VALID_LIST.PART2 LIKE '%' || ITEM || '%')";

var reader = cmd.ExecuteReader();

if (reader.HasRows)
{
    string savePath;

    if (SaveTextFile(out savePath) == DialogResult.OK)
    {
        TextWriter writer = new StreamWriter(savePath);
        while (reader.Read())
        {
            writer.WriteLine(reader.GetString(0));
        }
        writer.Close();
        writer.Dispose();
    }

}

reader.Close();
reader.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();

MessageBox.Show("List Saved!.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);

I need some better way i can save list faster. total entries in VALID_LIST is 2639117

and it took 15 minutes to save it for the above SQL QUERY!

please lmk if the sql query can be optimized!

Thanks in advance

Upvotes: 6

Views: 7413

Answers (3)

Jawad Khan
Jawad Khan

Reputation: 21

I am not sure if this is what you want but it will help speed up the writing process.Try accumulating the strings you read from database in a string builder and then write to the file. For example you can read 100k string then write those 100k to the file at once.

    StringBuilder builder = new StringBuilder();
    int count = 0; //to limit the number of rows stored in string builder.
    while (reader.Read())
    {

        builder.AppendLine(reader.GetString(0));
        count++;

        //store every 100k or so rows at once. 
        //This number depends on how much RAM 
        //you can allocate towards storing the string of rows.
        //If you have 2GB of free RAM
        //this number can easily be 1 million but it always depends on the
        //size of each string stored in database.
        if(count == 100000) 
        {
           File.AppendAllText(path, builder.ToString()); //Append all rows to the file
           builder.Clear(); //clear the string for next 100k rows of data
           count = 0; //Clear the counter value
        }
        count++
    }

Let me know if it helped.

Upvotes: 0

Tom Kerr
Tom Kerr

Reputation: 10720

Queries with LIKE are going to be very slow in general unless the wildcard is attached to the suffix. A predicate such as LIKE '%foo' cannot be indexed via typical string indexing.

You can however replace heavy LIKE usage in sqlite with its full text search (FTS) feature.

The FTS3 and FTS4 extension modules allows users to create special tables with a built-in full-text index (hereafter "FTS tables"). The full-text index allows the user to efficiently query the database for all rows that contain one or more words (hereafter "tokens"), even if the table contains many large documents.

They have an example that look promising in terms of performance on your use case.

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table *

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

Upvotes: 6

Larry Lustig
Larry Lustig

Reputation: 50970

Consider using full text search.

For this to work, the values in PREFIX and SUFFIX have to be tokenized (they must be separate words), and the ITEM you're trying to match must be a distinct token in one of the these values (not part of a word or two words together). For instance, the strings in PREFIX and SUFFIX have to be something like 'RED BLUE GREEN' or 'DOG, CAT, CAPYBARA' and the values for ITEM must be RED, BLUE, GREEN, DOG, CAT, or CAPYBARA.

If those conditions are met you could enable full text search, recreate these tables as full text tables, and replace LIKE (and the wildcards) with MATCH. In this case, SQLite will maintain on index on every token found in PREFIX or SUFFIX and that part of the searching will be much, much faster.

Unfortunately, enabling FTS in SQlite involves compiling the product from source code with one or more compile-time flags set. I have no experience with this.

Upvotes: 2

Related Questions