SQLIte memory usage in C#

Question

I have a simple sqlite database with only one table and 7-8 string fields with ~2.000.000 records. Database file on disk is about 450-500mb...

When i open db file in my c# app and execute simple "select * from tbl" query, computer memory usage goes to 3gb.

Here is code i'm using to select data from database:

        DataSet ds = new DataSet();
        dataGridView1.DataSource = null;
        string s = Cstr();
        try
        {

            SQLiteConnection conn = new SQLiteConnection(s);
            SQLiteCommand command = new SQLiteCommand(conn);
            command.CommandText = "select * from log;";
            conn.Open();
            command.ExecuteReader();
            var da = new SQLiteDataAdapter(command.CommandText, conn);
            da.Fill(ds);

            dataGridView1.DataSource = ds.Tables[0];
            conn.Close();
            GC.Collect();

        }
        catch (Exception)
        {
        }

I tried same thing with mysql5 db and memory usage is normal for database of that size. edit: I tried to load same sqlite database with Delphi XE5 and it uses only ~600mb of memory.

Is there any way to avoid this?

Mike Dinescu · Accepted Answer · 2014-01-20 17:04:25Z

4

This is not an authoritative answer on this but I suspect the following is the cause of what you see.

SQLite is an in-process database management system. As such, when you perform that query which selects all records in your 2,000,000 rows table you are essentially loading all that data in memory complete with the overhead that comes from whatever index data structures SQLite uses to reference the data.

Contrast that with MySQL which runs as a separate process. Most likely when you send a SELECT * FROM .. query to MySQL via the MySQL data access driver it doesn't really send back all the data at once but instead you're only getting results back gradually, as you read the next record via a DataReader.

answered Jan 20, 2014 at 17:04

Mike Dinescu

56k17 gold badges124 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Netorica Over a year ago

well you have the point. concerning the size of the SqlLite file itself its quite large and needed to completely load in the memory(I am not sure) but one thing weird is why his computer reaches the memory load of 3GB? its really weird. I tried myself now on a blank C#.NET project 4.0 reading an old SQLlite file of 1.2GB. in my computer memory it only consumes ~80MB

Netorica · Accepted Answer · 2014-01-20 17:09:47Z

0

I encountered this same problem before with C#.net framework 4.0 and here was my observations.

if you're doing a lot of process on the SQLite at the same time(using threads) you will create a big overhead because SQLite locks the whole database because it can only handle one process at a time.

the workaround I came to solve this problem was to dedicated myself on using transaction during CRUD operations and using prepared statements.

edited Jan 20, 2014 at 17:09

answered Jan 20, 2014 at 17:00

Netorica

19.5k19 gold badges78 silver badges109 bronze badges

1 Comment

onedevteam.com Over a year ago

I'm not doing anything except single "select" query. i'll add some code to clarify question.

Collectives™ on Stack Overflow

SQLIte memory usage in C#

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related