1
\$\begingroup\$
  1. Required to know the performance overhead associated with DataSet and DataTable.

  2. When it comes choosing which data structure might be good and performance oriented

    • what if single Result Set is returned from Stored Procedure
    • what if more than one Result Set is returned from Stored Procedure

Question may arise choosing whether which one should we need to go for?

Here is the coding snippet from Business Access Layer:

// Method 1: Using DataTable
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataTable dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    
    if (dtProctorUserDetails != null && dtProctorUserDetails.Rows.Count > 0)
    {
        for (int i = 0; i < dtProctorUserDetails.Rows.Count; i++)
        {
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("UserID") && dtProctorUserDetails.Rows[i]["UserID"] != DBNull.Value)
                proctorUserDetails.UserID = Convert.ToString(dtProctorUserDetails.Rows[i]["UserID"]);
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("LoginName") && dtProctorUserDetails.Rows[i]["LoginName"] != DBNull.Value)
                proctorUserDetails.LoginName = Convert.ToString(dtProctorUserDetails.Rows[i]["LoginName"]);
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("FirstName") && dtProctorUserDetails.Rows[i]["FirstName"] != DBNull.Value)
                proctorUserDetails.FirstName = Convert.ToString(dtProctorUserDetails.Rows[i]["FirstName"]);
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("LastName") && dtProctorUserDetails.Rows[i]["LastName"] != DBNull.Value)
                proctorUserDetails.LastName = Convert.ToString(dtProctorUserDetails.Rows[i]["LastName"]);
    
            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

// Method 2: Using DataSet
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataSet dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    
    if (dsProctorUserDetails != null && dsProctorUserDetails.Tables.Count > 0 && dsProctorUserDetails.Tables[0].Rows.Count > 0)
    {
        for (int i = 0; i < dsProctorUserDetails.Tables[0].Rows.Count; i++)
        {
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (dsProctorUserDetails.Tables[0].Columns.Contains("UserID") && dsProctorUserDetails.Tables[0].Rows[i]["UserID"] != DBNull.Value)
                proctorUserDetails.UserID = AESCrypto.Encrypt(Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["UserID"]));
            if (dsProctorUserDetails.Tables[0].Columns.Contains("LoginName") && dsProctorUserDetails.Tables[0].Rows[i]["LoginName"] != DBNull.Value)
                proctorUserDetails.LoginName = Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["LoginName"]);
            if (dsProctorUserDetails.Tables[0].Columns.Contains("FirstName") && dsProctorUserDetails.Tables[0].Rows[i]["FirstName"] != DBNull.Value)
                proctorUserDetails.FirstName = Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["FirstName"]);
            if (dsProctorUserDetails.Tables[0].Columns.Contains("LastName") && dsProctorUserDetails.Tables[0].Rows[i]["LastName"] != DBNull.Value)
                proctorUserDetails.LastName = Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["LastName"]);
    
            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

// To call SP to whichever prototype/return type
public static <String/DataSet/DataTable> GetProctorsDetails(LoginUser sessionDetails)
{
    List<object> parameter = new List<object>();
    parameter.Add(SqlHelper.BuildSqlParameter("OrganizationID", SqlDbType.BigInt, sizeof(Int64), sessionDetails.OrganizationID));
    parameters.Add(SqlHelper.BuildSqlParameter("Status", SqlDbType.VarChar, 5, "Status", string.Empty, ParameterDirection.Output));
    
    using (DataTable dt = SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, "UspGetUserOrgDetails", parameters.ToArray(), false))
    {
        statusCode = Convert.ToString(SqlHelper.GetParameterValue(parameters[1]));
    }
    
    using (DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "UspGetUserOrgDetails", parameters.ToArray(), false))
    {
        statusCode = Convert.ToString(SqlHelper.GetParameterValue(parameters[1]));
    }
    return <statusCode/ds/dt>;
}

How should be our choice of selection? Especially for .NET Framework or Core or some other programming language

\$\endgroup\$
7
  • 2
    \$\begingroup\$ DataSet is a collection of DataTable. Whenever you need to process multiple different DataTables for one database, you can use DataSet to take advantage of its capabilities like DataRelation, Data Integrity and Data Serialization. But if you just need only work on one table to process its data (like using DataRow) then DataTable would be enough. \$\endgroup\$
    – iSR5
    Sep 28 '20 at 12:42
  • \$\begingroup\$ I assume that the DataSet example should be returning dsProctorUserDetails not an ArrayList? For that matter neither of your example methods is actually returning a value at all. Can you please fix these examples so that they should compile. \$\endgroup\$ Sep 29 '20 at 5:08
  • \$\begingroup\$ Updated the semantic syntax!... The above shown was a partial coding snippets. There were other packages inherited to this class with try, catch, finally. As it would grow huge.. @ChrisSchaller \$\endgroup\$ Sep 29 '20 at 5:43
  • 1
    \$\begingroup\$ Don't write your own ORM, use Dapper -- dapper-tutorial.net . \$\endgroup\$
    – BCdotWEB
    Sep 29 '20 at 8:08
  • 3
    \$\begingroup\$ Please do not update the code in your question after receiving answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. \$\endgroup\$
    – Mast
    Sep 29 '20 at 12:28
5
\$\begingroup\$

As pointed out by @iSR5's comment, A DataSet is basically a wrapper around a collection or DataTable objects. It has some other helpful utility methods, but from a performance point of view the only performance issue with your logic is that in the DataSet solution you need to access the table to read from through the indexer that has assumed that there is a table at index 0.

  • So there is not really a performance argument to choose one over the other.
  • Given that both solutions for your Business layer GetProctorsDetails do not return either of the DataSet or DataTable objects, your ONLY consideration is actually the implementation of PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    • Your code examples already demonstrate that you can manage both types of results from this method.

Focusing on the DAL Implementation

Specifically for SqlHelper there is no measurable difference between ExecuteDataTable and ExecuteDataSet, in fact if you have a look at the source code, most implementations or ExecuteDataTable use a DataSet internally:

var ds = new DataSet();
using (var dataAdapter = new SqlDataAdapter(command))
{
    dataAdapter.Fill(ds);
}
return ds.Tables[0];

DO NOT make the assumption that using DataSet is therefore more performant!

The only reason you should consider a DataSet over a DataTable is because you want to return multiple tables of records. If you are only going to return a single table of results, DataTable is the better choice because it simplifies the code, but also conveys the correct expectation to callers that there is only a single table of records in this result set.

Back to the Business layer mapping

The code smell to watch out for is when you have lots of code that is accessing an item in an array at index zero, in your second code example the following indexer is evaluated 5 times for every record:

dsProctorUserDetails.Tables[0]

It's not a big deal at runtime, but the use of DataSet in this scenario has added an unnecessary level of complexity and uncertainty into this code.

If we refactor your 1st two examples to reduce the repetitive column validations, (do you really expect or want to support the condition of only some if these columns being present in the result set?) you will see there is even less difference in the usage between DataTable and DataSet:

// Method 1: Using DataTable
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataTable dtProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);

    if (dtProctorUserDetails != null && dtProctorUserDetails.Rows.Count > 0)
    {
        var userIdOrd = dtProctorUserDetails.Columns.Contains("UserID") ? dtProctorUserDetails.Columns["UserID"].Ordinal : -1;
        var loginOrd = dtProctorUserDetails.Columns.Contains("LoginName") ? dtProctorUserDetails.Columns["LoginName"].Ordinal : -1;
        var firstNameOrd = dtProctorUserDetails.Columns.Contains("FirstName") ? dtProctorUserDetails.Columns["FirstName"].Ordinal : -1;
        var lastNameOrd = dtProctorUserDetails.Columns.Contains("LastName") ? dtProctorUserDetails.Columns["LastName"].Ordinal : -1;
        for (int i = 0; i < dtProctorUserDetails.Rows.Count; i++)
        {
            var row = dtProctorUserDetails.Rows[i];
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (userIdOrd > 0 && !row.IsNull(userIdOrd))
                proctorUserDetails.UserID = AESCrypto.Encrypt(Convert.ToString(row[userIdOrd]));
            if (loginOrd > 0 && !row.IsNull(loginOrd))
                proctorUserDetails.LoginName = Convert.ToString(row[loginOrd]);
            if (firstNameOrd > 0 && !row.IsNull(firstNameOrd))
                proctorUserDetails.FirstName = Convert.ToString(row[firstNameOrd]);
            if (lastNameOrd > 0 && !row.IsNull(lastNameOrd))
                proctorUserDetails.LastName = Convert.ToString(row[lastNameOrd]);

            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

For performance reasons, I have moved the column exists check out of the row indexer (all rows in the table will have the same columns), by storing and using the column ordinal we can squeeze out some additional CPU ticks by not having to compare strings.

See if you can spot the main difference between Method 1 and Method 2:

// Method 2: Using DataSet
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataSet dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    if (dsProctorUserDetails != null && dsProctorUserDetails.Tables.Count > 0 && dsProctorUserDetails.Tables[0].Rows.Count > 0)
    {
        DataTable dtProctorUserDetails = dsProctorUserDetails.Tables[0];

        var userIdOrd = dtProctorUserDetails.Columns.Contains("UserID") ? dtProctorUserDetails.Columns["UserID"].Ordinal : -1;
        var loginOrd = dtProctorUserDetails.Columns.Contains("LoginName") ? dtProctorUserDetails.Columns["LoginName"].Ordinal : -1;
        var firstNameOrd = dtProctorUserDetails.Columns.Contains("FirstName") ? dtProctorUserDetails.Columns["FirstName"].Ordinal : -1;
        var lastNameOrd = dtProctorUserDetails.Columns.Contains("LastName") ? dtProctorUserDetails.Columns["LastName"].Ordinal : -1;
        for (int i = 0; i < dtProctorUserDetails.Rows.Count; i++)
        {
            var row = dtProctorUserDetails.Rows[i];
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (userIdOrd > 0 && !row.IsNull(userIdOrd))
                proctorUserDetails.UserID = AESCrypto.Encrypt(Convert.ToString(row[userIdOrd]));
            if (loginOrd > 0 && !row.IsNull(loginOrd))
                proctorUserDetails.LoginName = Convert.ToString(row[loginOrd]);
            if (firstNameOrd > 0 && !row.IsNull(firstNameOrd))
                proctorUserDetails.FirstName = Convert.ToString(row[firstNameOrd]);
            if (lastNameOrd > 0 && !row.IsNull(lastNameOrd))
                proctorUserDetails.LastName = Convert.ToString(row[lastNameOrd]);

            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

If you missed it, in the second example we get the table at index 0 from the dataset, from then on the processing is identical:

DataSet dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
DataTable dtProctorUserDetails = dsProctorUserDetails.Tables[0];

Remember my point above:

DO NOT make the assumption that using DataSet is therefore more performant!

There is zero net difference between 1 and 2 in the refactored versions, in 1 the .Tables[0] is executed once inside the SqlHelper routine, in 2 it is executed in our code.


Additional Considerations

In General, using the DataTable solution is likely to have less long term maintenance issues especially when you want to support a stored procedure that might return multiple tables of result sets.

  • When there are multiple tables in the response, only the calling context (the context that provides the SQL) is likely to know which one of the tables in the result set is the table that this method needs to interact with, unless you can guarantee that the proctor details table that you want to inspect is always the first table in the DataSet.
    • So DataTable is better than trying to blindly support DataSet, to properly support DataSet you should be iterating the Tables within it to identify which table matches the columns that you are expecting to read from.

How should be our choice of selection? Especially for .NET Framework or Core or some other programming languge

From a performance and maintenance perspective I would not advocate using DataTable or DataSet in a new project or any .Net Core projects moving forward. Instead I would recommend moving towards a typed data access layer using an ORM framework or tool (Entity Framework/nHibernate). To keep your DAL and DTO classes decoupled you can use Interfaces instead of DataSets/DataTables. There are also tools like AutoMapper that replace your first two code examples to map between your DAL and business layer or DTOs.

In legacy or simple .Net FX applications DataSet or DataTable can work, even moving forward, however there have been other significant advances in the IDE tooling and syntax and language runtimes that mean it is often easier and less error prone to work in typed data models, so avoiding DataTable and DataSet altogether.

The issue with DataTable and DataSets, from a performance perspective, is that code like this must search for and pre-validate every property on demand. When you move to a typed interface the property resolution and type validation is performed in an efficient manner when the data is loaded from the database, or when it is deserialized.

  • Note, as you flagged performance as a driving factor, implementing an ORM and or a DTO mapper like AutoMapper itself is not likely to be more performant, in fact it can be perceived to be slower in some cases, especially for loading data records, however your code becomes more reliable saving you development time and they can award you performance and maintenance gains throughout the rest of your application to offset or trade off any losses you might be able to measure at data load.
\$\endgroup\$
2
  • 2
    \$\begingroup\$ If the latest edits have invalidated (part of) your answer, please notify me and we'll rollback the edits on the question. We take answer invalidation seriously here. \$\endgroup\$
    – Mast
    Sep 29 '20 at 12:28
  • 1
    \$\begingroup\$ thanks @Mast the edits don't really change a lot, just confirmed what I thought was going on, I'll tldr my answer i guess \$\endgroup\$ Sep 29 '20 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.