C# app with sqlite slow with two or more users?

Question

We have an multi-user C# application, each user has the app files locally. This app connects to a central SQLite database file in the network, shared to any user.

We know SQLite is not made for concurrent user access, but we can't install a dbms, and there is a low amount of writing made to the db.

The problem is as soon as we have 2 users reading the db (not writing), the app will become very slow in getting the value and show it to the users.

To help, i will show a minimized part of the class that handle connections to the db:

    public class clsDBConnectionManager
{
    private static clsDBConnectionManager _instance;
    private string _gblConn = "";

    private SQLiteConnection _gblCon;
    public SQLiteCommand _gblCmd;
    private SQLiteTransaction _Trans;

    private clsDBConnectionManager()
    {
        try
        {
            _gblConn = Session.Instance.SqliteConnectionString;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    public static clsDBConnectionManager Instance
    {
        get
        {
            if (_instance == null)
            {
                _instance = new clsDBConnectionManager();
            }
            return _instance;
        }
    }
    public SQLiteConnection GetConnection()
    {
        try
        {
            if (_gblCon == null)
            {
                _gblCon = new SQLiteConnection(_gblConn);
            }

            if (_gblCon.State == ConnectionState.Closed)
            {
                _gblCon.Open();
                if (_gblCmd == null)
                {
                    _gblCmd = new SQLiteCommand(_gblCon);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Connexion Impossible on " + _gblConn + Environment.NewLine + "Command :" + _gblCon + Environment.NewLine + ex.Message);
            throw;
        }

        return _gblCon;
    }

    public void CloseConnection(bool withTransaction = false)
    {

        if (withTransaction || _Trans == null)
        {
            if (_Trans != null)
            {
                _Trans.Commit();
            }

            if (_gblCmd != null)
            {
                _gblCmd.Dispose();
                _gblCmd = null;
            }

            if (_Trans != null)
            {
                _Trans.Dispose();
                _Trans = null;
            }

            if (_gblCon != null)
            {
                try
                {
                    _gblCon.Close();

                }
                catch (SQLiteException ex)
                {

                    Console.WriteLine("Closing connection failed.");
                    Console.WriteLine("Error: {0}", ex.ToString());

                }
                finally
                {
                    _gblCon.Dispose();
                    _gblCon = null;
                }
            }
        }
    }

    public DataTable GetSQLData_byReader(string commandtext)
    {
        DataTable dt = new DataTable();
        try
        {
            GetConnection();

            _gblCmd.CommandText = commandtext;

            using (SQLiteDataReader objDr = _gblCmd.ExecuteReader())
            {
                dt.Load(objDr);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

        return dt;
    }
}

Then, in another class we call the previous class to read the db and retrieve data:

public DataTable GetHolidayList(string _Year)
    {


        string _Sql = "SELECT * from t_PUBLICHOL WHERE  substr(strftime(PUBLICHOL_DATE), 7, 4)='" + _Year + "'";

        DataTable table = new DataTable();

        try
        {
            table = _sqliteManager.GetSQLData_byReader(_Sql);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            throw;
        }
        finally
        {
            _sqliteManager.CloseConnection();
        }

        return table;
    }

Does someone see something incorrect?

Answer 1

Accessing an SQLite file over a network tends not to work very well, there are a lot of posts about this problem. Even if you just read from the Db there can be issues. So the problem is not really the number of clients, or the code but the fact that you have a network accessible SQLite db file with > 1 client.

I think you need to consider a different approach to the problem.

If you have so few changes and you mostly read data, could you not cache the table data in local memory when the application starts? Then you just have a slow start, and the rest of the time it is OK. This assumes you don't have too much data to cache, and that it does not change that often.

Another approach is a server client approach. Here, you build a "server" application which controls the SQLite db file. It then gets requests from "clients" and serves them.

If the request come as REST commands then you also don't need to maintain a constant connection to your "server" which might make things easier and less complex.
But you will need a centralised location to run the server. This of course could also be a cloud based solution.

Answer 2

With multiple clients accessing the same file in read/write mode, the file server can no longer risk to allow any client to cache the file data locally.

You could try to open the connection in read-only mode:

...;Read Only=True

and to open a read/write connection only when actually needed. But there is no guarantee that this will result in better caching, or that it will keep working after OS updates.