Retrieving data from a database in another thread(Unity3D)

Question

I currently have a code which retrieves data from a database and visualizes it in unity3D. However, everytime it retrieves data in the FixedUpdate() function, it spikes dramatically every 1 second. I'm thinking about using threading to do this but i'm not sure what i'm doing wrong.

This is the Function i call in the thread.

 public void retrievefromDB(){
                            if (timeStep - prevTimeStep > 99) {
                                    timeStep -= 1; //special for this dataset
   query = "SELECT * FROM GridData2 WHERE timestep=" + timeStep;

                                    if (showParent)
                                            query += " AND (Level != 10)";
                                    else
                                            query += " AND (Level == 10)";

      query += " AND temperature >= " + minTemp + " AND temperature <= " + maxTemp;
                                    dt.Rows.Clear ();
                                    dt = sqlDB.ExecuteQuery (query);

                                    prevTimeStep = timeStep;
                            }

            }

This code lags the scene every 1 second therefore i tried to put it into a thread.

void FixedUpdate()
    {
    Thread testthread = new Thread(new ThreadStart(retrievefromDB));
        testthread.Start ();
}

After putting it in a thread, it keeps crashing the scene after awhile. Can anyone tell me what I did wrongly? And how do i solve it?

Answer 1

The cause of your original issue is relatively obvious: database access is slow. If you put a database call inline in the FixedUpdate method, you're going to essentially pause your game's movement while the DB access happens (which may well take a second if you have to initialise a connection, for example).

The main issue with your threaded code as posted is that you are starting a new thread every time FixedUpdate is called. That means you're starting 60 new threads per second (by default) which will very quickly cripple your game!

While it's fine to use C# threads in Unity for this sort of work, a better approach would be to create a single thread and allow that to manage the timing, rather than creating a new thread each time the job runs. That would mean creating the thread in Awake() or Start() instead, and then using Thread.Sleep or similar to handle the timing.

Coroutines (as suggested by Mihai in his answer) are great for fixing the timing of events, but they still run on the game thread: if you put your DB code in a coroutine, you'll still see pauses when it runs. If you must run this DB access every second, you need it in a proper thread.

That said, have you considered that the DB access might be unnecessary? A more performant model might be to cache all of the data up front and use it from memory when you need it. (This might not be possible if the data is very dynamic, or if you're running in a memory-restricted environment like a mobile device...)