Why are 700k database rows taking over 15 seconds to load into memory?

Question

Using C# and .NET 3.5 and with either a ADO connection or OLEDB connection, filling a DataTable or DataSet with 700k rows of 3 columns each, takes over 15 seconds.

Executing the actual select statement on the DB takes less than a second. The DB is on a different machine to the one querying it, processing the data. (Perhaps this adds time?)

The data looks like this:

public class Node
{
    public DateTime Timestamp;
    public float Value;
    public string Name;
}

Doing it with a SqlDataReader and calling reader.Read(), then manually putting the data in a new instance of the above class, adding it to a List<Node> also takes over 15 seconds.

Code looks like this:

List<Node> data = new List<Node>();
while (reader.Read())
{
   Node n = new Node();

   n.Timestamp = (DateTime)reader["Timestamp"];
   n.Value = (float)reader["Value"];
   n.NodeName = (string)reader["NodeName"];

   data.Add(n);
}

I measured this using a StopWatch class in release mode with optimization turned on in project properties.

I get that it has to iterate each record, but I would have expected any machine today to be able to iterate 700k records in a few seconds, but not more.

What could be the reasons this takes over 15 seconds? Am I unreasonable to expect that this should be much faster?

EDIT Doing SqlDataReader.Read() by itself also takes over 15 seconds.

Use the visual studio profiler on your code and see where it spents time. — Samuel, Jan 22 '14 at 11:41
The fastest way to get Data is in fact using the DataReader. So maybe it's about your Databse query and/or missing indexes... — Dannydust, Jan 22 '14 at 11:46
How long does the query take without adding the items to the list? Also: It is hard to tell without you showing the code you wrote to iterate the reader/add the items. — Thorsten Dittmar, Jan 22 '14 at 11:46
From what you say, I would guess - but can't know for sure - that network speed is the bottleneck. Have you tried doing `while(reader.Read());` to get an idea of how much time it takes to get the results, but not act on them? — Jon Hanna, Jan 22 '14 at 11:50
I have tested your code with a table that has more than 40mio. rows and used `SELECT TOP 700000` to simulate your condition, it only took: a second even in debug mode(`Time:00:00:01.7363727`). — Tim Schmelter, Jan 22 '14 at 11:53
@TonyTheLion is there any constructor for class Node ? if yes check if there is any costy code in the constructor ? Does it inherit any class with a costy constructor function ?? — sm_, Jan 22 '14 at 12:37
@SirajMansour the `Node` class is as posted. It has nothing else to it. — Tony The Lion, Jan 22 '14 at 13:17
@TonyTheLion please check my answer, it might be of help to you. — sm_, Jan 22 '14 at 13:46

score 1 · Answer 1 · answered Jan 22 '14 at 12:01

I think the problem lies in the container you're using. The List<> is being dynamically resized a lot. Try the following procedure instead:-

run query with a COUNT clause to get the number of records, only select a single column

List<Node> data = new List<Node>(count from above);

run normal query

fill List<> as above

This will prevent the list from constantly resizing.

Alternatively, to see if this is the problem, replace List<> with LinkedList<> as this doesn't have the resizing issues that List<> does.

score 0 · Answer 2 · answered Jan 22 '14 at 11:47

0

It should be the network speed between database and machine you are executing the code at.

answered Jan 22 '14 at 11:47

Dmitry Astanin

12
1

score 0 · Answer 3 · answered Jan 22 '14 at 12:10

What also happens in your loop, is that the values from your query are unboxed. It might be worth it to try the GetString, GetFloat, etc methods, since you have so many records.

List<Node> data = new List<Node>();
while (reader.Read())
{
   Node n = new Node();

   n.Timestamp = reader.GetDateTime(0); // TODO: Check column numbers
   n.Value = reader.GetFloat(1);
   n.NodeName = reader.GetString(2);

   data.Add(n);
}

No conversions are performed in these methods.

Remarks

No conversions are performed; therefore, the data retrieved must already be a string, or an exception is generated.

score 0 · Answer 4 · edited May 23 '17 at 12:11

I'm reading a lot of guesses, which could be right, but they are still guesses.

If you run it under the debugger and manually pause it a few times, and each time display the stack, you will be using the random pausing method. It will tell you exactly what's taking the time and why - no guesswork.

If you want to use a profiler, you need one that samples on wall-clock time. Otherwise you will have to choose between a) a sampler that gives you line-level inclusive percent, with no visibility into IO time, or b) an instrumenter, that only gives you function-level inclusive percent. Neither one tells you why the time is being spent, only how much. Whatever you choose, ignore the temptation to look at self time, which is misleading at best in any app that spends all its time in subfunctions, and it totally ignores IO.

score -1 · Answer 5 · edited May 23 '17 at 11:49

If it is not a code issue then suspect it has to do with your query plan then.

Make sure you are setting the right options before executing the query. and they are of the same state on .NET and MSSQL.

One interesting option that has been found to cause a performance hit before is the ARITHABOIRT being enabled on SQL and off on .NET.

Try adding SET ARITHABORT ON before your query in the command.

Refer to : Slow performance of SqlDataReader

Why are 700k database rows taking over 15 seconds to load into memory?

5 Answers5