1

I have a sqlite database with around 22 million records. I have indexes on the right columns( on the "where" columns) When I do a query (which returns around 80 000 rows) using sqlitestudio (a sqlite editor) the query takes around a second to run. But I suspect that this is due to some form of pagination.

However.. when I run the same query in C# using system.sqlite.data, it take a long time to iterate through that 80 000 records.

Is there any way to speed this up? Should I perhaps write a wrapper on the sqlite.dll itself?

I checked the execution plan and its the same in sqlitestudio and in the c# code. Similar issue

While guessed answers are appreciated and can provide an insight (and sometimes an answer) I would appreciate concrete answers (i.e. ones you know that actually works)

Heres the code...

using (var command = new SQLiteCommand(conn))
{
    command.CommandText = "select cell, lat, lon from cell_towers where mcc = @mcc and net = @net ";
    command.Parameters.AddWithValue("@mcc", MCC);
    command.Parameters.AddWithValue("@net", MNC);
    using (SQLiteDataReader rdr = command.ExecuteReader())
    {
        // In here it takes around 1-2 minutes to loop through. Even with no code inside the loop
        while (rdr.Read())
        {

        }
    }
}

Edit 1:

I've run the same query using sqlite3.exe (the command line program one can download from sqlite.org) and its ouput is going to the console window. Its taking a long time to loop through all the records and print it to the command window.. but remember ... its PRINTING to the COMMAND WINDOW....

Fortega
  • 19,463
  • 14
  • 75
  • 113
Eminem
  • 7,206
  • 15
  • 53
  • 95
  • No idea, wasnt me - but.. it takes 1-2 minutes per loop? that does seem incredibly slow, what happens if (for want of something better to do) as you've hidden what it does in the loop you just take the data from your reader and just write the raw object to local disk.. so nothing optimizies it out, does it still take that long? – BugFinder Jan 03 '17 at 11:26
  • That is how long it takes for the entire result set to be looped through even with NOTHING in the loop. Thats the part that's driving me nuts. But even with the code in (simple assignment of class properties from columns) it still takes as long. Do you think perhaps an index on the result columns will help? – Eminem Jan 03 '17 at 11:30
  • Made a slight change to my code comment – Eminem Jan 03 '17 at 11:33
  • it depends, so.. lets say it takes 2 mins, is the sql lite server local? can you monitor it and see whats going on, major disk io? cpu load? if you connect using anything else, is it just as slow to return the same? exactly what indexes are on that table? – BugFinder Jan 03 '17 at 11:34
  • Its on my local machine. The indexes are on the mcc and net columns (the ones in the where clause) Good thinking on the monitoring note. I will check it out – Eminem Jan 03 '17 at 11:58
  • I used sqlitestudio (a sqlite editor) and the results are returned much faster. – Eminem Jan 03 '17 at 11:59
  • Eminem, my apologies for the premature answer, transactions indeed only help when doing multiple selects. – prof1990 Jan 03 '17 at 13:23
  • It's doing around 800 rows per second. That's pretty quick. How fast do you think it should be? Think of what is being done: Data read from disk, converted to dotnet data types, including string allocation (if cell is a string). On a 2GHz machine that suggests about 2400 clock cycles per row. That's not really that many. – Ben Jan 03 '17 at 13:27
  • @Ben ... let me ask you that question (don't take it as me being rude but think about what you are asking me) How fast do YOU think it CAN be? sqlitestudio fetches AND displays the first 1000 rows in 0.070 seconds.. on a COLD run. 0.002 after warmup. So yeah... I think it can MOST definitely be improved. – Eminem Jan 03 '17 at 13:52
  • I've run the same query using sqlite3.exe (the command line program one can download from sqlite.org) and its ouput is going to the console window. Its taking a long time to loop through all the records and print it to the command window.. but remember ... its PRINTING to the COMMAND WINDOW.... Next up.. A thin C# wrapper over the actual sqlite.dll – Eminem Jan 03 '17 at 14:04
  • 1) 1000 rows in 2 milliseconds is 500 rows per second. C# is giving you about the same or faster speed. Why do you think it should be faster than that? 2) Test C# sending to a command window for a fairer test. 3) Try sqlite3.exe sending to a file, that's quicker than the console. – Ben Jan 03 '17 at 15:28
  • I have written a c# wrapper that calls the functions from sqlite3.dll directly. I simply connect to the database and loop over the rows. Its STILL slow.. – Eminem Jan 04 '17 at 11:17

1 Answers1

0

Just indexing each column is not always useful. A lookup on two columns would require a single index on both columns:

CREATE INDEX cell_towers_mcc_net ON cell_towers(mcc, net);

A covering index for this query might be even faster, by a little bit:

CREATE INDEX cell_towers_mcc_net_cov ON cell_towers(mcc, net, cell, lat, lon);

but probably needs more storage than it's worth.

If the mcc/net columns are the primary key, consider using a clustered index instead.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for the answer. I was just wondering about this. I have indexes on those columns (mcc, net) Im just using the sqlite3.exe to export the data to csv. It takes around 20s.. which is much faster than the minute or 2 I have to wait. So Im considering dumping things to file then reading it from there with StreamReader – Eminem Jan 04 '17 at 11:49