Accessing SQLite database in Unity with a coroutine

Question

I've created a menu in Unity which is populated by results from an SQLite DB. However when I create the menu, the whole game freezes for a moment while it queries the DB.

To fix this, I'm trying to separate the creation of the menu and the populating of it with data (i.e. the menu will just say "loading" until the query is complete).

I have been trying to use a yield-return co-routine to do this but the game is still freezing. Below I have some pseudo-code illustrating what I am doing...

void createMenu () {

    // code to create menu... 

    StartCoroutine(getData());

}

IEnumerator getData () {

    List<string> sqlResults = Database.query("SELECT * FROM table");

    yield return null;

    updateMenu();

}

void updateMenu() {

   // replaces "loading" strings with sql data results 

}

Am I going about this the wrong way, or am I using a coroutine incorrectly?

Answer 1

It looks like the database operation is blocking the main thread. Run it in a new Thread with the ThreadPool.QueueUserWorkItem function. When it's done, use UnityThread.executeInUpdate function from this post to call the updateMenu() function. You need to get the UnityThread class from this post in order to use this solution.

void Awake()
{
    UnityThread.initUnityThread();
}

void runQuery(string query)
{
    ThreadPool.QueueUserWorkItem(RunInNewThread, query);
}

private void RunInNewThread(object a)
{
    string query = (string)a;
    List<string> sqlResults = Database.query(query);


    //We are in another Thread. Use executeInUpdate to run in Unity main Thread
    UnityThread.executeInUpdate(() =>
    {
        updateMenu();
    });
}

USAGE:

runQuery("SELECT * FROM table");