Which is better performance for using datatables.

Question

c# WinForm project using visual studio 2019.

I am using a Sql Server database with the WinForm app.

From a performance perspective, which is the better approach?

1. load a datatable from records in a datatable table. Filter the datatable as required to use specific records.
2. Discard the datatable and load with specific records from the database table ?

The table can have MANY records (600 - 700 thousand).

Thanks.

Answer 1

Hello @Darryl Hoar

From a performance perspective, it's generally more efficient to load specific records from the database table rather than loading a datatable with all records and then filtering it. Here are some reasons:

1. Efficiency: SQL Server is designed to handle data filtering efficiently. It's faster to load less data.
2. Memory Consumption: Loading all records into memory can lead to high memory usage, especially with large tables. This can cause your application to run out of memory.
3. Network Traffic: Loading the whole table requires transferring all the data via the network, which can be slower and consume more resources than querying specific records.

However, there could be exceptions. If you're running the same query repeatedly, it might make sense to cache the data locally and then filter it. But even then, you should only do so if performance tests indicate significant savings.

Remember to consider indexing on the columns involved in the condition to speed up the lookup even more.

Here's a simple example of how you might load specific records from the database:

private DataTable GetDataFromDB(string userId, string docId, DateTimeOffset date)
{
    string cmd = "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);
        command.Parameters.AddWithValue("@userId", string.IsNullOrEmpty(userId) ? DBNull.Value : (object)userId);
        command.Parameters.AddWithValue("@docId", string.IsNullOrEmpty(docId) ? DBNull.Value : (object)docId);
        command.Parameters.AddWithValue("@dueDate", date);
        SqlDataReader reader = command.ExecuteReader();
        DataTable table = new DataTable();
        table.Load(reader);
        reader.Close();
        conn.Close();
        return table;
    }
}

This function loads specific records from the database based on the parameters provided.I hope this answers your question. If so, please tag this as answered.

Answer 2

it depends on the life and usage of the dateable.

if loaded once from the database, and multiple filter operation are performed than this may be more performant (though high use of client memory). if only one filter operation is performed per database fetch, then a where clause is much more performant..

Answer 3

It depends on your use case. If you are loading all 600k-700k records in one request to SQL server and store it to datatable, do you have any further actions? in general, you won't display all the records to the user in UI. If you are using grid to display the records, you could consider loading the 6 pages of the records and then loading another 6 pages again when the user is trying to look at the 7th pages and so on.
Or you could also try to collect the data with filter first.

Answer 4

Hi @Darryl Hoar , Welcome to Microsoft Q&A,

Performance considerations are very important when you have a large number of records. When working with a WinForms application and a Sql Server database, you have two main approaches to choose from:

1. Load the entire data table into DataTable and filter:

Benefit:

Faster subsequent filtering in memory.

Once loaded, the data can be processed offline in the application.

Harm:

Initial load times can be long, especially for large data sets. Uses more memory because the entire dataset is loaded into the application.

2. Load specific records directly from the database:

Benefit:

The initial load time is shorter because only the required records are fetched. Memory usage is lower because only required data is loaded.

Harm:

Multiple database queries are required for different filtering conditions. Filtering in memory can be slower than DataTable.

Recommendation: Given the large number of records, it is often more efficient to load specific records directly from the database. However, specific performance may depend on specific circumstances. If memory and load time are major concerns, you might prefer the second approach.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

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.