SQL Server CE Using too much Memory

Question

I am using SQL Server CE as database in my C# WinForm application, my application is harvesting proxies from various websites, and inserts into a SQL Server CE table, only one column.

Proxy Table:

1. IPAddress

But it seems every insert, memory consumption is increasing slowly, the application starts with 11,000 of memory, by the time it reaches 1,00,000 count, the memory usage is around 87,000 and the queries becomes very slow, which is bad for my app, initially I thought there is a memory leak, but could not find any, here is my code for insert.

public void InsertData(DemoTable _table)
{
    string strInsertQuery = string.Format("INSERT INTO DemoTable (Value) VALUES ({0})", _table.Value);

    using (connection = new SqlCeConnection(connectionString))
    {
        connection.Open();
        using (command = connection.CreateCommand())
        {
            command.CommandText = strInsertQuery;
            command.ExecuteNonQuery();
        }
    }
}

Is this normal for SQL Server CE or something is wrong with my application, I also tried to force garbage collection but no use.

My application will be collecting around 1 Million proxies max, the harvesting process is very fast around 500 to 1700 proxies per second, I am displaying them using DataGridView virtual mode paging, so all collected proxies are visible to user but using paging, but i am not able to get same performance out of the SQL Server CE database, it is not keeping up with the other process.

Please suggest, do I have an alternative to SQL Server CE, which is perfect for this type of job, or any way that I can keep up with the fast harvesting process?

Answer 1

Switching to SqlCeResultSet and SqlCeUpdatableRecord will definitly help speed things up. I'm not sure that it will help with the memory usage.

Also, keeping your database connection open will help. SQL CE does not handle connection pooling like SQL Server does and there is considerable overhead involved with opening a new connection for each query. That might explain some of the memory usage that you reported.

I would also seriously consider moving to SQL Server Express or SQLite. Either one would be better suited to this task.