SqlDataReader performance list <string []> or List <object []>

Question

I experimented with the ability to read data from an SQL server as quickly as possible, and I found an interesting discovery. If I read the data in List<object[]> instead of List<string[]> , performance will more than double.

I suspect this is because you do not need to call the ToString() method in the fields, but I always thought that using objects negatively affects performance.

Is there a reason not to use a list of arrays of objects instead of string arrays?

EDIT: I thought it was the size of the data store. Will there be more storage in object arrays than as strings?

Here is my test code:

 private void executeSqlObject() { List<object[]> list = new List<object[]>(); using (SqlConnection cnn = new SqlConnection(_cnnString)) { cnn.Open(); SqlCommand cmd = new SqlCommand("select * from test_table", cnn); SqlDataReader reader = cmd.ExecuteReader(); int fieldCount = reader.FieldCount; while (reader.Read()) { object[] row = new object[fieldCount]; for (int i = 0; i < fieldCount; i++) { row[i] = reader[i]; } list.Add(row); } } } private void executeSqlString() { List<string[]> list = new List<string[]>(); using (SqlConnection cnn = new SqlConnection(_cnnString)) { cnn.Open(); SqlCommand cmd = new SqlCommand("select * from test_table", cnn); SqlDataReader reader = cmd.ExecuteReader(); int fieldCount = reader.FieldCount; while (reader.Read()) { string[] row = new string[fieldCount]; for (int i = 0; i < fieldCount; i++) { row[i] = reader[i].ToString(); } list.Add(row); } } } private void runTests() { Stopwatch watch = new Stopwatch(); for (int i = 0; i < 10; i++) { watch.Start(); executeSqlObject(); Debug.WriteLine("Object Time: " + watch.ElapsedMilliseconds.ToString()); watch.Reset(); } for (int i = 0; i < 10; i++) { watch.Start(); executeSqlString(); Debug.WriteLine("String Time: " + watch.ElapsedMilliseconds.ToString()); watch.Reset(); } } 

And the results:

 Object Time: 879 Object Time: 812 Object Time: 825 Object Time: 882 Object Time: 880 Object Time: 905 Object Time: 815 Object Time: 799 Object Time: 823 Object Time: 817 Average: 844 String Time: 1819 String Time: 1790 String Time: 1787 String Time: 1856 String Time: 1795 String Time: 1731 String Time: 1792 String Time: 1799 String Time: 1762 String Time: 1869 Average: 1800 

Answer 1

object adds extra overhead if you call extra boxing. And even then this effect is quite minimal. In your case, reader[i] always returns object . You already have an object , regardless of whether it is a reference to a string or int, etc. course call .ToString() adds overhead; in most cases (int, DateTime, etc.) this includes both the formatting code and the allocation of one (or more) additional lines. When you change the string you change the data (the worse, for example, IMO, for example, you can no longer do the correct sorting by date, for example) and add service data. The key case here is that all columns are already rows - in this case, you simply add a few calls to virtual methods (but without additional real work).

For information, if you are after raw performance, I highly recommend looking at micro-ORMs such as dapper. They are highly optimized, but avoid the weight of the "full" ORM. For example, in dapper:

 var myData = connection.Query<TypedObject>("select * from test_table").ToList(); 

I expect it to perform very comparable, providing you with strongly typed object data.

Answer 2

Is there a reason not to use a list of arrays of objects instead of string arrays?

It will depend on what you wanted to do with the received values ​​after you got them into arrays, if you are happy to treat each value as an object, then the list of objects is fine, but if you want to treat them as strings, and then at some point you will have to convert / discard the object back to a string so that you bear the cost somewhere.

As Corey said, if you read the value as a string from SqlDataReader, you should test using the GetString (int) method, rather than calling ToString () for the value and use this as a criterion.

Alternatively, instead of using arrays, you can read values ​​into a DataSet, which can be easier to work with after that.

The end of the day, which best depends on how you want to use the results after retrieving from the database.