Reducing CPU Load on a remote select from a database

Question

I am remotely selecting results from a custom production database with a criteria of around three minutes from a C# application.

Every time the select command is executed, the server PC that I am using CPU goes up to around 50%. But surely, the load should be on the database that I am connecting to?

Why would the C# application rocket to 50% until the data is retrieved for reading?

Some background

• I have worked out from debugging that the Select statement on the remote database takes around 30-40 seconds, baring in mind I am selecting with a criteria that uses the indexed column.
• At the same time of selecting data from the remote DB, I have monitored the TaskManager and the CPU sits at 50% until the select as complete.. this can last around 30-40 seconds each loop.
• If I select in the native sql engine for the remote DB, there is no lag on the select, the data (if any) is returned immediately.
• I know its not the parsing of result set that is taking up the CPU load as some selects will return nothing.

Here is some code I am using.

OdbcConnection remoteConn = new OdbcConnection(ConfigurationManager.ConnectionStrings["remoteConnectionString"].ToString());

            remoteConn.Open();

            OdbcCommand remoteCommand = new OdbcCommand();
            remoteCommand.Connection = remoteConn;

            using (remoteConn)
            {
                string localSql = "";
                string remoteSql = "select * from tracking where last_update > 212316247440000000"; // Julian No = 2015-07-12 11:24:00

                remoteCommand.CommandText = remoteSql;

                OdbcDataReader remoteReader;

                remoteReader = remoteCommand.ExecuteReader();

                while (remoteReader.Read())
                {


                    for (int i = 0; i < 68; i++)
                    {
                        localSql += ",'" + remoteReader[i].ToString() + "'";
                    }

                }

            }

I ran a performance and diagnostic test on the application and it yielded this result.

How, if any, can I reduce this CPU load or even eradicate it completely. It is completely out of the ordinary and I have no clue on how to go about it.

Thanks

Answer 1

Thanks for the information, Dan. Here are my thoughts...

I believe the primary reason why your app is consuming so much CPU is because of the drivers you are using.

Since you are connecting to an SQL Server database, you should use the SQL Server drivers which know how to optimize transportation of data between client and server.

To use the appropriate drivers, make sure you use the SqlConnection, SqlCommand, etc.

This will allow SQL Server to stream the results to your client as you query the data reader.

Secondly, do not use the ExecuteReader() method on the DbCommand object. One of the many wonderful features unique to the SQL Server drivers is the ExecuteReaderAsync() method.

Since this command is an IO-bound operation (not compute-bound) then there is no need to block the calling thread. When the result come back they will arrive on an IO completion thread.

Here is a code sample of what your code might look like after the change.

using (var remoteConn = new SqlConnection(ConfigurationManager.ConnectionStrings["remoteConnectionString"].ToString()))
{
    remoteConn.Open();
    using (var remoteCommand = new SqlCommand())
    {
        remoteCommand.Connection = remoteConn;
        string localSql = "";
        string remoteSql = "select * from tracking where last_update > 212316247440000000"; // Julian No = 2015-07-12 11:24:00

        remoteCommand.CommandText = remoteSql;
        var remoteReader = await remoteCommand.ExecuteReaderAsync();
        while (remoteReader.Read())
        {
            for (int i = 0; i < 68; i++)
            {
                localSql += ",'" + remoteReader[i].ToString() + "'";
            }
        }
    }
}