1

I wrote this program which queries an SQL db using the C#.NET tools in Unity. I followed what everyone and their mother on YouTube does, and wrote it with a Coroutine, but this throttled my CPUs whenever the SQL query function came around, causing the program to be unresponsive for ~2 seconds. I'm not wholly sure if the 2 seconds of lag comes from the literal time it takes to query the server and get a response, or if my query is strange.

I updated the code to use async/delay instead of coroutine/WaitForSeconds, which I've read is a cleaner way to write code that doesn't get half as messy as coroutines do, but I'm still getting the same issue.

Here's some code snippets for further context:

    async Task parseSQLData () { // The function that throttles the environment every time it runs
        while (true){
            output.text = sqlStream.readSQL(); // Read from our SQL stream stored in another script
            new_reading = true; // Flip a switch after that's done so that Update() can display the change
            await Task.Delay(7000); // Wait around 12 minutes, since tables are pushed every ~15 minutes.
        }
    }

...

    public string readSQL () {
        // The SQL query being used, if relevant. Pulls a DataTable containing around 600 characters.
        string sql = @"SELECT TOP (20) [7 Different Columns]
                        FROM [SomeDB]  
                        WHERE (Timestamp >= '2023-08-01')
                        AND ([Conditionals based on columns above])
                        ORDER BY [Timestamp] DESC";
        // Queries the database with the connection string we established in another location
        DataTable result = ExecuteSqlQuery (builder, sql);

        // Parse the DataTable object into a string, delimiting items in a row by " ; "
        string beans = string.Join(Environment.NewLine, result.Rows.OfType<DataRow>().Select(x => string.Join(" ; ", x.ItemArray)));
        return beans;
    }

If I had to guess, the program is waiting on the result from readSQL despite every attempt I've made to tell it to "thread" the query, and since that requires some intranet operations, the CPU "throttles". 99.4% of my CPU gets dedicated to this code segment, with the Profiler code analytics saying that 99.3% of the CPU is throttled by DbDataAdapter.Fill(), and this drills all the way down to a similar percent at Socket.Receive_icall().

So, my overall questions are: Is there something I can do to improve the runtime of the code here to alleviate/eliminate this framerate stuttering while still using coroutines/asyncs, or do I need to bite the bullet and use ijobs for this? Does readSQL need to also be an async function, or does async simply control an entire "thread" regardless of what functions are in it?

Enigmativity
  • 113,464
  • 11
  • 89
  • 172
Khepri
  • 13
  • 4

1 Answers1

0

I think I understand what you mean by "thread", but let's clarify this to make sure we understand each other.

A method marked as "async" will not be executed immediately on another thread. This is simply an instruction to the .NET runtime that this method should be translated at the compiler level to the state machine. (More information you can find here: .NET Async / Await: How the State Machine knows when to continue it's execution? or here https://mykkon.work/async-state-machine/).

So to execute you method in another thread you should call something like this:

Task.Run(parseSQLData);

It will queues the specified work to run on the ThreadPool.

Does readSQL need to also be an async function, or does async simply control an entire "thread" regardless of what functions are in it?

In your case method "readSQL" will be executed in the same thread as "parseSQLData".

CuriousFeo
  • 51
  • 1
  • Thank you. I was misinformed that I could just call the function and get the intended outcome. – Khepri Aug 15 '23 at 14:28