Is there a faster/smarter way, to fill a datatable than DataTable.Load(DataReader)? C#

Question

I am doing some research, to get a better understanding for SQL and working with DataTables. So I was trying to work on good performance reading data from a MS SQL database and load it into a datagridview.

I've created a SQL function, which I am calling from my tool and load the results into a datatable. If I execute this function within SSMS it takes 11-12 seconds to load the results (almost 1,5 million entries), but if I am executing this function by using the tool, I coded, it will take more than 30 seconds (just for doing DataTable.Load(SqlDataReader))

What I've done so far is:

    private DataTable GetDataFromDB(string userId, string docId, DateTimeOffset date)
    {            
        string cmd = String.Format("select * from dbo.GetData(@userId, @docId, @dueDate);");

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand command = new SqlCommand(cmd, conn);

            if (String.IsNullOrEmpty(userId))
                command.Parameters.AddWithValue("@userId", DBNull.Value);
            else
                command.Parameters.AddWithValue("@userId", userId);

            if (String.IsNullOrEmpty(docId))
                command.Parameters.AddWithValue("@docId", DBNull.Value);
            else
                command.Parameters.AddWithValue("@docId", docId);
            command.Parameters.AddWithValue("@dueDate", dueDate);

            SqlDataReader reader = command.ExecuteReader();

            stopWatch.Reset();
            stopWatch.Start();

            table.BeginLoadData();
            table.Load(reader, LoadOption.Upsert);
            table.EndLoadData();
            stopWatch.Stop();
            reader.Close();
            reader.Dispose();
            conn.Close();
        }

        return table;
    }

I've already done some google research and this is the best I could come up with. So far it is working well, but I am curios if it is possible to get the results faster. Any ideas? Also I have a problem with the memory. As soon as I start the call, the tool will allocate up to 900MB RAM, until it has all the entries. I've already managed it, to free the memory everytime I call the function above, but I think, 900MB are quite a lot and another problem is, that it won't release all the RAM, which was needed. So for example: When I start the tool, it needs roundabout 7MB of RAM, if I call the method above the first time, it will need 900MB. If I call it the next time, it will release most of the memory, but will still need 930MB. The third time 960MB and so on. So, with every call it allocates more memory, which will cause a "System out of memory"-Exception, if this method get called often.

Thank you a lot!

Are you talking about the https://datatables.net/ jQuery plugin which includes an ASP.NET server side package? Or do you mean a different datatable? — Silthus, Jun 06 '16 at 13:26
A DataGridView control suggest that OP is working with a desktop app not a web app — Steve, Jun 06 '16 at 13:28
oh sorry, didn't know, that there is also a plugin, with the same name. I am talking about System.Data.DataTable. I hope, I did understand your question correctly... — Echelon, Jun 06 '16 at 13:28
`DataTable` is horrendously inefficient because it needs to be as general as possible -- every value is stored as an `Object`, every row is stored as an array of `Object`s. In general, the smarter way to use `DataTable` is to not use it at all. Initialize your objects directly from `SqlDataReader` -- this is more code and not as convenient, but it saves on memory and speed something fierce. In the specific case of data grids, you'll want to take a look at making them virtual so not all the data has to be present all the time. — Jeroen Mostert, Jun 06 '16 at 13:34
have you taken a look at the `DataTable.Fill()` method.. also instead of doing a `Select * from Query`, where you are only working off of 3 columns, why don't you change the Query to Select only those 3 columns an add a more defined Filtering statement meaning your `Where Clause`? — MethodMan, Jun 06 '16 at 14:00
@ MethodManI don't have a DataTable.Fill method. And the query is not returning 3 columns. I am calling the SQL function, I've created and give it the 3 parameters, you can see in the query. It returns 9 columns. @Jeroen Mostert Thanks, I will take a look at that. — Echelon, Jun 06 '16 at 14:15
for me using **DataTable.Load(SqlDataReader)** is the fastest way — Ramgy Borja, Aug 04 '17 at 02:01

score 1 · Accepted Answer · answered Jun 08 '16 at 07:22

1

Performance will vary based on what and how much data you are loading. Below link will give you the solution with performance report. Try something like this with your approach and compare the performance.

Hope this will help you.

Fastest way to populate datatable

answered Jun 08 '16 at 07:22

Keppy

471
1
7
23

The link is gold! Thanks a lot! – Echelon Jun 08 '16 at 11:36

score 0 · Answer 2 · answered Aug 04 '17 at 02:51

for me using DataTable.Load(SqlDataReader) is the fastest way

        DataTable dt = new DataTable();
        using (var con = new SqlConnection { ConnectionString = "ConnectionString" })
        {
            using (var command = new SqlCommand { Connection = con })
            {
                con.Open();
                command.CommandText = @"SELECT statement.....";
                command.Parameters.AddWithValue("@param", "Param");
                //load the into DataTable
                dt.Load(command.ExecuteReader(), LoadOption.Upsert);
            }// this will dispose command

        }// this will dispose and close connection

Is there a faster/smarter way, to fill a datatable than DataTable.Load(DataReader)? C#

2 Answers2