CODEWITHSUNDEEP
c#performancecpusqldatareader
peteisace
peteisace

Reputation: 2772

CPU performance when creating lots of objects with SqlDataReader

I have a stored procedure that returns several resultsets and within (some) resultsets 1000's of rows. I am executing this stored proc using x threads at the same time, with a maximum of y concurrently.

When i just run through the data:

using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
    do
    {
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                var value = reader.GetValue(i);
            }
        }
    }
    while (reader.NextResult());
 }

I get reasonable throughput - and CPU. Now obviously this is useless, so i need some objects! OK, now i change it to do something like this:

using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
    while(reader.Read())
    {
        Bob b = new Bob(reader);
        this.bobs.Add(b);              
    }

     reader.NextResult();

    while(reader.Read())
    {
        Clarence c = new Clarence(reader);
        this.clarences.Add(c);
    }

    // More fun
}

And my implementation of data classes:

public Bob(SqlDataReader reader)
{
   this.some = reader.GetInt32(0);
   this.parameter = reader.GetInt32(1);
   this.value = reader.GetString(2);
}

And this performs worse. Which is not surprising. What is surprising, is that the CPU drops, and by about 20 - 25% of total (i.e. not 25% of what it was using; it's close to 50% of what it was using)! Why would doing more work, drop the CPU? I don't get it... it looks like there's some locking somewhere - but i don't understand where? i want to get the most out of the machine!

EDIT - changed code due to bad demo code example. Oops.

ALSO: to test the constructor theory, i changed the implementation of the one that creates the object. This time it also does a for loop over the fields, and does a getValue, and will create an empty object. So it doesn't do what i want yes, but i wanted to see if creating the large numbers of objects is the issue. It isn't.

SECOND EDIT: looks like adding the objects to the list is the issue - CPU drops immediately as soon as i add these objects to the list. Not sure how to improve this... (or if it's worth investigating; the first scenario is obviously stupid)

Upvotes: 0

Views: 1170

Answers (3)

ChrisLively
ChrisLively

Reputation: 88072

Well, the reason the CPU isn't maxed out is simply that it's waiting on something else. Either the disk or the network.

Considering your first code block simply reads and immediately discards a value there are several possibilities: One is that the compiler could be optimizing out the allocation of the value variable because it is limited in scope and never used. This would mean the processor doesn't have to wait for memory allocation. Instead it would be limited mainly by how fast you can get the data off of the network wire.

Normally memory allocation ought to be super fast, however, if the amount of memory you are allocating causes windows to push stuff out to disk then this be held back by your hard drive speeds.

Looking at your second code block, you are constructing lots of objects (more memory usage) and keeping them around. Neither of which allows the compiler to optimize the calls out; and both of which put increased pressure on local system resources beyond just the processor.

To determine if the above is even close, you need to put watch counters on the amount of memory used by the app vs the amount of available RAM. Also you'll want counters on your disk system to see if it's being heavily accessed under either scenario.

Point is, try and watch EVERYTHING that is going on in the machine while the code blocks are running. That will give you a better idea of why the processor is waiting.

Upvotes: 1

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

I can see the performance issue with your methodology, but I am not sure I can pinpoint the exact way to explain the cause. But essentially, you are adding the flow of the cursor to instantiation rather setting properties. If I were to take a guess, you are causing some context switching when you make a pull from a reader as part of your constructor.

If you think of a reader as a firehose cursor (it is) and think of the difference between the firehose being controlled by the user holding the hose (normal method) rather than the container being filled, you start to get a picture of the problem.

Not sure how the threading relates? But, if you have multiple clients and you are stopping the flow of the hose a bit more by moving bits over to a constructor rather than setting properties on a constructed object, and then contending for thread time across multiple requests, I can envision an even bigger issue under load.

Upvotes: 1

Abdul Hfuda
Abdul Hfuda

Reputation: 1503

Maybe its just a matter of incorporating the Bob and Clarence calls that are inherently less CPU intensive- and that part of the execution just takes some time because of I/O bottlenecks or something along those lines.

Your best bet is to run this through a profiler and take a look at the report.

Upvotes: 0

Related Questions