List<T> Grabbing Data for Speed

By John Belthoff / In Web / Posted Oct 17, 2009

Speeding up your Data Gathering

In my previous article I began writing about the virtues of the Generic List - List<T>. In this article I will demonstrate the efficiency of using the Generic List. Keep in mind that this is a precursor to the more elegant features of the List<T> but mandatory in understanding its elegance.

Let's take a look at a classic example of populating DataSet and then compare it to populating data into a Generic List. I will warn you, at first it might look like we have to write additional code and we will but only in the beginning. Ok let's create some data, make a stored procedure and then populate out DataSet.

Create a Table and stuff some data into it, then create a stored proc for retrieval

-- Create Our Table
CREATE TABLE [dbo].[MyCustomers](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_MyCustomers_Guid]  DEFAULT (newid()),
    [FName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [LName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_MyCustomers] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Stuff our Table with some data
Declare @Count int
Set @Count = 0
While @Count < 50000
    Begin

        Insert Into dbo.MyCustomers (FName, LName)
        Values (Cast(NewID() as varchar(50)), Cast(NewID() as varchar(50)))

        Set @Count = @Count + 1

    End
GO

-- Create a Stroed Proc to retrive our data
Create Procedure dbo.GetMyCustomerList
As
Set NoCount On
Select
        ID
        ,Guid
        ,FName
        ,LName
From dbo.MyCustomers
Set NoCount Off
GO

-- Quick Test to see if everything went right
Select Top 100 *
From dbo.MyCustomers
Order By ID desc

Now if everything went right you should be seeing the bottom 100 rows of our data starting at row fifty thousand and counting down. I will assume you are ok.

The SqlDataAdapter Approach

In order to get our data onto a web page we need to crate a dataset and then fill it with data from our database and then manipulate it using the Asp.Net server side tools like DataSet, DataTable, DataRow etc... We do this by using the System.SqlClient, and System.Data namespaces. After populating our data we can begin to manipulate it. However for our purposes we are only going to grab the data and count how many rows we have. We will do this again populating a list to see if there is any performance gain.

Populate our Dataset and Count the Rows

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class x_test_data_set1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Trace.Warn("Fill", "Started");
        DataSet ds = Customers();
        Trace.Warn("Fill", "Ended");
        Response.Write(ds.Tables[0].Rows.Count.ToString());
    }

    private DataSet Customers()
    {
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBLocal"].ConnectionString);
        SqlCommand objCmd = new SqlCommand("dbo.GetMyCustomerList", objConn);
        objCmd.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
        SqlDataAdapter objAdpt = new SqlDataAdapter();
        objAdpt.SelectCommand = objCmd;
        objAdpt.Fill(ds);
        return ds;
    }
}

Turn the trace on for the .aspx page and running the code above produces our timing of execution. Mine looks like the following. 810 milliseconds to perform that database retrieval. (Remember, we are retrieving 50k rows).

Asp Trace from DataAdapter.Fill

Ok, now let's perform the same thing but use a Generic List to hold our data as opposed to a DataSet, In order to do this we first need to create a class that will hold our data. This will give us a strongly typed data wrapper which makes things a whole lot easier on the other side as you will soon see. After we create our class, we use and SqlDataReader to get our data, create an instance of our class and then add it to our Generic List. Let's get started. We create our class inside the App_Code foler.

Create a Data Wrapper Class

using System;
/// <summary>
/// Holds Data Related to a Customer Object
/// </summary>
public class Customer
{
    private Int64 _id;
    /// <summary>
    /// ID (System.Int64)
    /// </summary>
    public Int64 ID
    {
        get { return this._id; }
        set { this._id = value; }
    }

    private Guid _guid;
    /// <summary>
    /// ID (System.Guid)
    /// </summary>
    public Guid Guid
    {
        get { return this._guid; }
        set { this._guid = value; }
    }

    private String _fname;
    /// <summary>
    /// FName (System.String)
    /// </summary>
    public String FName
    {
        get { return this._fname; }
        set { this._fname = value; }
    }

    private String _lname;
    /// <summary>
    /// LName (System.String)
    /// </summary>
    public String LName
    {
        get { return this._lname; }
        set { this._lname = value; }
    }
}

OK We have our base class to hold our data, let's populate the same data into a Generic List and take a look at the performance results.

Generic List Code Behind

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class x_test_data_set2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Trace.Warn("Reader", "Started");
        List<Customer> l = GetCustomers();
        Trace.Warn("Reader", "Ended");
        Response.Write(l.Count.ToString());
    }

    private List<Customer> GetCustomers()
    {
        
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBLocal"].ConnectionString);
        SqlCommand objCmd = new SqlCommand("dbo.GetMyCustomerList", objConn);
        objCmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader objRdr;
        List<Customer> l = new List<Customer>();
        Customer c;
        
        objConn.Open();
        objRdr = objCmd.ExecuteReader();
        while (objRdr.Read())
        {
            c = new Customer();
            c.ID = Convert.ToInt64(objRdr["ID"]);
            c.Guid =  (Guid)objRdr["Guid"];
            c.FName = objRdr["FName"].ToString();
            c.LName = objRdr["LName"].ToString();
            l.Add(c);
        }
        objRdr.Close();
        objConn.Close();

        return l;

    }
}

Now we run our page and look at the results. Still returning the same 50k rows, but look at the time it took to do this! We went from 810 milliseconds down to 330 milliseconds! And that's not all, read on...

Asp Trace from Generic List Approach

OK, we have over twice the computing performance and if you haven't realized yet working with a DataSet and trying to find information inside of it, at least for me, is a time consuming task. When working with the Generic List and because we created our class Intellisense does our work for us. Instead of remembering column names and positions and everything else that you often have to look up references for the syntax you now have everything at you finger tips.

Consider the following scenarios. Lets find the value of the field "Guid" at index position 48709 and display that on a web page. If you are using a dataset, you would type something like this:
ds.Tables[0].Rows[48709]["Guid"].ToString()

And even that requires you to remember the Database field name "Guid"! However, with the Generic List we can type the following:
l[48709].Guid.ToString()

The beauty of this is that after you type the closing bracket and put a period, you are now offered a list of properties to choose from. Now that is very convenient indeed!

Anyway, you should now have firm grasp on my conceptual thinking. In the next article we will be looking at finding, manipulating and playing with our server side disconnected record sets.

Happy Coding!

Aspx:

<%@ Page Language="C#" AutoEventWireup="true" trace="true" CodeFile="data-set.aspx.cs" Inherits="x_test_data_set" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
  <title>Untitled Page</title>
</head>
<body>
  <form id="form1" runat="server">
    <div>
    
    </div>
  </form>
</body>
</html>

C#:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class x_test_data_set : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Get Our Data Using a DataSet
        Trace.Warn("SqlDataAdapter", "Started");
        DataSet ds = Customers();
        Trace.Warn("SqlDataAdapter", "Ended");
        Response.Write("DataSet Total Rows: " + ds.Tables[0].Rows.Count.ToString());
        Response.Write("<br />");
        Response.Write("Value at Index Position 48709: " + ds.Tables[0].Rows[48709]["Guid"].ToString());
        
        Response.Write("<br />");
        Response.Write("<br />");

        // Get Our Data Using a Generic List
        Trace.Warn("SqlDataReader", "Started");
        List<Customer> l = GetCustomers();
        Trace.Warn("SqlDataReader", "Ended");
        Response.Write("Generic List Total Rows: " + l.Count.ToString());
        Response.Write("<br />");
        Response.Write("Value at Index Position 48709: " + l[48709].Guid.ToString());
    }

    private DataSet Customers()
    {
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBLocal"].ConnectionString);
        SqlCommand objCmd = new SqlCommand("dbo.GetMyCustomerList", objConn);

        // Mark the Command as a SPROC
        objCmd.CommandType = CommandType.StoredProcedure;

        // Create a new Dataset
        DataSet ds = new DataSet();

        // Create a SqlDataAdapter and fill the DataSet
        SqlDataAdapter objAdpt = new SqlDataAdapter();
        objAdpt.SelectCommand = objCmd;
        objAdpt.Fill(ds);
        objAdpt.Dispose();
        objConn.Close();

        return ds;

    }

    private List<Customer> GetCustomers()
    {
        // Create Instance of Connection and Command Object
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBLocal"].ConnectionString);
        SqlCommand objCmd = new SqlCommand("dbo.GetMyCustomerList", objConn);

        // Mark the Command as a SPROC
        objCmd.CommandType = CommandType.StoredProcedure;

        // Create a SqlDataReader
        SqlDataReader objRdr;

        // Create a new Generic List of Articles
        List<Customer> l = new List<Customer>();
        Customer c;

        // Fill our Generic List
        objConn.Open();
        objRdr = objCmd.ExecuteReader();
        while (objRdr.Read())
        {
            c = new Customer();
            c.ID = Convert.ToInt64(objRdr["ID"]);
            c.Guid = (Guid)objRdr["Guid"];
            c.FName = objRdr["FName"].ToString();
            c.LName = objRdr["LName"].ToString();
            l.Add(c);
        }
        objRdr.Close();
        objConn.Close();

        return l;

    }
}