CODEWITHSUNDEEP
c#odbccpucpu-usage
SK2017
SK2017

Reputation: 773

Reducing CPU Load on a remote select from a database

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

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.

enter image description here

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

Upvotes: 7

Views: 1293

Answers (4)

Pedram
Pedram

Reputation: 828

The best way for your IO Bound operations is using the Task, becouse it does not need to change existing code:

var task = Task.Factory.StartNew(() => {
    //your code or @Randy code with little change here:
    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 = remoteCommand.ExecuteReader();
            while (remoteReader.Read())
            {
                for (int i = 0; i < 68; i++)
                {
                    localSql += ",'" + remoteReader[i].ToString() + "'";
                }
            }
        }
    }
});
//use can use 'task.wait();' to waiting for complete the task.

Upvotes: 0

Vikhram
Vikhram

Reputation: 4404

I am assuming that the select command is run from a separate computer than the one hosting the SQL DB.

Frankly it is a bit baffling. The only thing that comes to my mind is that it might be taking time passing and processing the metadata. Have you tried changing your query to return say only one piece of data?

For example: 

select top 1 last_update from tracking where last_update > 212316247440000000
select count (*) from tracking where last_update > 212316247440000000

This will clarify if the data+metadata is causing the issue.

Upvotes: 1

Randy
Randy

Reputation: 2358

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() + "'";
            }
        }
    }
}

Upvotes: 5

Jonas Bergstr&#246;m
Jonas Bergstr&#246;m

Reputation: 767

Could it be parsing of the result? Are you using some sort of datalayer that automatically parse resultsets into domain objects? That could be very CPU intense..

Upvotes: 0

Related Questions