Geek Answers Handbook

Is there a faster / smarter way to populate data than DataTable.Load (DataReader)? WITH#

I am doing some research to better understand SQL and work with DataTables. Therefore, I tried to work with good data to read performance from an MS SQL database and load it into a datagridview.

I created an SQL function that I call from my tool and load the results into a datatable. If I perform this function in SSMS, it takes about 11-12 seconds (almost 1.5 million records) to load the results, but if I perform this function with the tool I encoded, it will take more than 30 seconds (just to execute the DataTable .Load (SqlDataReader))

What i have done so far:

    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;
    }

Google, , . , - , . ? . , 900 , . , , , , 900 , , RAM, . , : , 7 , , 900 . , , 930 . 960 . , , "System out of memory" -Exception, .

!

+4
2

, . . - .

, .

+1

DataTable.Load(SqlDataReader) -

        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
0

More articles:


All Articles