Kevin Steffer Outloud – web, business and opinions

Linq, Dataset, Recordset performance result

I’ve for a while now been wondering why I often run into bad performance when using LINQ to access my SQL Server database. So I decided to hunt down the beast and see if I come up with something.

In this first test I haven’t found the beast but I still found some interesting results that I want to share. The result confirms my experience of not using a Dataset to hold data, simply because in most cases it’ll be to time consuming an approach using the SqlDataAdapter Fill method to load a Dataset and here you can see why.

 

 

My test is simply to read out 1,000, 10,000 and 100,000 rows from a simple table and then measure how long it takes to the data out in the three different ways: With LINQ, as a Dataset and as a Recordset from the SQL Server.

Conclusion

The Recordset is twice as fast for small database executions

When working with small amount LINQ isn’t as fast as the Recordset, but in my case it’s now noticeable in a single execution. But when you have to run a lot of small jobs against the database you should keep in mind that the Recordset will do the job twice as fast.

Dataset should be used where many of the same data and used in variety.

When you have to count the number of rows fetched, pray to that you didn’t use the Dataset method. What tares the method down is that it has to load all data with all the properties into the Dataset, just so you can call the Count property on the Rows inside a table on the Dataset object. It just takes way too long time.

With 10,000 rows both LINQ and Recordset is twice as fast as the Dataset

When I increase the rows in the table with factor of 10 to 10,000 rows that test times are rising, which is fair enough because the database sends 10 times more data, but the times are somehow surprising me anyway. The LINQ method rises with a factor 4.5, the Dataset method with a factor of 10 and the Recordset method climes with a factor of 6.5.

With 100,000 rows LINQ begins to catch up with the Recordset

When I further increase the rows in the table with a factor 10 to 100,000 rows I would expect about the same factors, but now they rises even further. The LINQ method rises with a factor 8.7, the Dataset with a factor 9 and the Recordset with a factor 9.5.

Download the Visual Studio 2008 Project

Test system

Hardware: Laptop HP Elitebook, 3GB RAM, 2,53GHz Intel Core Duo, 32bit
Software: Windows 7 32bit, SQL Server Express 2009, Visual Studio 2008 Professional Developer Web Server

Test scenario

I have separated my test into 4 scenarios and each scenario is run 10 times with the same method to get an average value of the time consumption for my conclusion.

LINQ measure method

private void DoLinqTest()
{
    DateTime dtLinqStart = DateTime.Now;
    using ( DB.dbDataContext db = new DB.dbDataContext() )
    {
        // Execution code
    }
    DateTime dtLinqEnd = DateTime.Now;
    lblLinqTime.Text = ( dtLinqEnd - dtLinqStart ).ToString();
}

Dataset measure method

private void DoDataSetTest()
{
    DateTime dtDsStart = DateTime.Now;
    using ( SqlConnection dbCon = new SqlConnection( ConfigurationManager.ConnectionStrings[ "dbConnectionString" ].ConnectionString ) )
    {
        using ( SqlDataAdapter sqlAdap = new SqlDataAdapter( "SELECT * FROM item", dbCon ) )
        {
            DataSet ds = new DataSet();
            sqlAdap.Fill( ds );

            // Executing code
        }
    }
    DateTime dtDsEnd = DateTime.Now;
    lblDsTime.Text = ( dtDsEnd - dtDsStart ).ToString();
}

Recordset measure method

private void DoRecordSetTest()
{
    DateTime dtRsStart = DateTime.Now;
    using ( SqlConnection dbCon = new SqlConnection( ConfigurationManager.ConnectionStrings[ "dbConnectionString" ].ConnectionString ) )
    {
        dbCon.Open();
        using ( SqlCommand sqlCmd = new SqlCommand( "SELECT * FROM item", dbCon ) )
        {
            using ( SqlDataReader rs = sqlCmd.ExecuteReader() )
            {
                // Executing code
            }
        }
    }

    DateTime dtRsEnd = DateTime.Now;
    lblRsTime.Text = ( dtRsEnd - dtRsStart ).ToString();
}

1. Scenario

A test which seen in Testresult Sheet: 1000 List to get out a 1000 database rows as objects of type:

public class item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public DateTime Date { get; set; }

    public item()
    {
    }

}

Executing LINQ code:

List<item> objItems = ( from itm in db.items
                        select new item
                        {
                          Id = itm.id,
                          Name = itm.name,
                          Description = itm.description,
                          Date = itm.date.Value
                        } ).ToList();

Executing Dataset code:

List<item> items = new List<item>();
DataRow[] drs = ds.Tables[ 0 ].Select();
for ( int i = 0; i < drs.Length; i++ )
{
    object[] dbFields = drs[ i ].ItemArray;
    item itm = new item();
    itm.Id = ( int )dbFields[ 0 ];
    itm.Name = dbFields[ 1 ].ToString();
    itm.Description = dbFields[ 2 ].ToString();
    itm.Date = ( DateTime )dbFields[ 3 ];
    items.Add( itm );
}

Executing Recordset code:

List<item> items = new List<item>();
while ( rs.Read() )
{
  item itm = new item();
  itm.Id = rs.GetInt32( 0 );
  itm.Name = rs.GetString( 1 );
  itm.Description = rs.GetString( 2 );
  itm.Date = rs.GetDateTime( 3 );
  items.Add( itm );
}

2. Scenario

A test as seen in Testresult sheet: 1000 Count to get out the amount of rows fetched on a database table with a 1,000 rows.

Executing LINQ code:

int count = ( from itm in db.items
              select new item
              {
                Id = itm.id,
                Name = itm.name,
                Description = itm.description,
                Date = itm.date.Value
              } ).Count();

Executing Dataset code:

int count = ds.Tables[ 0 ].Rows.Count;

Executing Recordset code:

int count = 0;
while ( rs.Read() )
{
    count++;
}

3. Scenario

A test as seen in Testresult sheet: 10000 List with the same Executing code like the 1. Scenario, but this time with having 10,000 database rows to get out.

4. Scenario

A test as seen in Testresult sheet: 100000 List with the same Executing code like the 1. Scenario, but this time with having 100,000 database rows to get out.

Testresult

Download the Visual Studio 2008 Project