multiple tables in dataset

Question

Below is the code that i am using to insert multiple tables inside a single dataset but is taking a lot a lot of time

// on form load 
string connectionString = "Connection_String"; // 

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Create a DataSet to hold the query results
            DataSet dataSet = new DataSet();

            // Perform the first SELECT query
            string query1 = "SELECT * FROM Table1";
            FillDataSet(connection, query1, dataSet, "Table1");

            // Perform the second SELECT query
            string query2 = "SELECT * FROM Table2";
            FillDataSet(connection, query2, dataSet, "Table2");

            // Perform the third SELECT query
            string query3 = "SELECT * FROM Table3";
            FillDataSet(connection, query3, dataSet, "Table3");

static void FillDataSet(SqlConnection connection, string query, DataSet dataSet, string tableName)
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(dataSet, tableName);
        }
    }

is there a way by which i can speed up or optimise the progress as it takes around 5 minutes to perform the above action . Please also suggest how i can insert multiple tables inside a single dataset without specifying multiple times .

Answer 1

Hi,@OmkarHcl.Welcome Microsoft Q&A.

When retrieving multiple tables into a single DataSet, the performance can be optimized in a few ways:

Batch the queries: Instead of executing each query individually, you could combine them into a single query using JOINs or UNIONs. This way, you make a single round trip to the database, reducing the overhead of multiple database calls. You can also refer to the method here.

string query = @"
    SELECT * FROM Table1;
    SELECT * FROM Table2;
    SELECT * FROM Table3;
";

using (SqlCommand command = new SqlCommand(query, connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
    adapter.Fill(dataSet);
}

Asynchronous execution:SqlDataAdapter.Fill - Asynchronous approach. You could try to refer to the solution here.

List<Task> tasks = new List<Task>();
tasks.Add(Task.Run(() => FillDataSet(connection, query1, dataSet, "Table1")));
tasks.Add(Task.Run(() => FillDataSet(connection, query2, dataSet, "Table2")));
tasks.Add(Task.Run(() => FillDataSet(connection, query3, dataSet, "Table3")));
await Task.WhenAll(tasks);

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Consider placing the three SELECT statements into a Stored Procedure than populate via code similar to below.

public static DataSet Example()
{
    string connectionString = "TODO";
    using var cn = new SqlConnection(connectionString);
    
    DataSet ds = new();

    using var cmd = new SqlCommand("StoredProcNameGoesHere",cn)
    {
        CommandType = CommandType.StoredProcedure
    };

    SqlDataAdapter da = new(cmd);
    da.Fill(ds);

    return ds;
}