Linq to SQL Speed Test Interesting Results

ryansjedi — Mon, 28 Apr 2008 15:56:00 GMT

So in researching another series I am writing I got a wild desire to do some Linq to SQL performance testing. So I whipped up a database table and populated it with data and went about writing some code. What you'll see in the code is 4 tests one with a fairly standard Linq query and a loop through the results (just to make sure the lazy loading has been done), a Linq query using a sproc (again looping through the results), a fairly standard T-SQL query DataReader which loops through the results and creates a List of objects (to simulate the collections that Linq returns) and a stored procedure using the same query (again looping through the results to simulate a result from Linq). The idea with the 'standard' methods of T-SQL and the Stored Procedure was to return the same slick collections of 'objects' that Linq produces, so to that end I made a small POCO that resembles our result set.

What's interesting is that for larger record sets Linq to Sql slows way down and DataReader speeds up. For smaller record sets the opposite is true.

However I think something shifty is going on because the Linq methods 'feel' faster...meaning I get to the command prompt faster and with the Data Reader there is a pause after it displays the elapsed time.

My database table is fairly simple:

CREATE TABLE [dbo].[Contact](

[contactid] [int] IDENTITY(1,1) NOT NULL,

[contactfirstname] [varchar](50) NOT NULL,

[contactlastname] [varchar](50) NOT NULL,

[contactdate] [datetime] NOT NULL

) ON [PRIMARY]

My Poco class looks like:

    public class MyContact
    {
        private int _contactid;
        public int ContactID { get; set; }

private string _contactfirstname;
public string ContactFirstName { get; set; }

private string _contactlastname;
public string ContactLastName { get; set; }

private DateTime _contactdate;
public DateTime ContactDate { get; set; }

        public MyContact(int contactid, string contactfirstname, string contactlastname, DateTime contactdate)
        {
            _contactid = contactid;
            _contactfirstname = contactfirstname;
            _contactlastname = contactlastname;
            _contactdate = contactdate;
        }

}

And here is my actual code:

   class Program
    {
        public static string connstring = "Data Source=localhost;Initial Catalog=TestDB;
     Persist Security Info=True;User ID=perftest;Password=12345";

        private static void UseLinq(int i)
        {
            using (ContactsDataContext ctx = new ContactsDataContext())
            {
                var cont = from p in ctx.Contacts
                           select p;
                foreach (Contact c in cont)
                {
                    i = i + 1;
                    //Console.WriteLine(i.ToString());
                }
            }
        }
        private static void UseSPLinq(int i)
        {
            using (ContactsDataContext ctx = new ContactsDataContext())
            {
                var cont = from p in ctx.GetContacts()
                           select p;
                foreach (var mycontact in cont)
                {
                    i = i+1;

                    //Console.WriteLine(i.ToString());
                }
            }
        }
        private static void TSQLDataReader(int i)
        {
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                conn.Open();

                SqlCommand myCommandObject = new SqlCommand();
                myCommandObject.CommandType = CommandType.Text;
                myCommandObject.CommandText = "select contactid, contactfirstname,
      contactlastname, contactdate from contact";
                myCommandObject.Connection = conn;

                using (SqlDataReader MyDataReader = myCommandObject.ExecuteReader())
                {
                    IList<MyContact> myList = new List<MyContact>();
                    while (MyDataReader.Read())
                    {
                        MyContact mytmpContact = new MyContact(
      (int)MyDataReader[0],
      MyDataReader[1].ToString(),
      MyDataReader[2].ToString(),
      (DateTime)MyDataReader[3]
      );
                        myList.Add(mytmpContact);
                    }
                    foreach (MyContact mc in myList)
                    {
                        i = i + 1;
                        //Console.WriteLine(i.ToString());
                    }

                }
                conn.Close();
                myCommandObject.Dispose() ;
            }
        }
        private static void SPDataReader(int i)
        {
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                conn.Open();

                SqlCommand myCommandObject = new SqlCommand();
                myCommandObject.CommandType = CommandType.StoredProcedure;
                myCommandObject.CommandText = "GetContacts";
                myCommandObject.Connection = conn;

                conn.Close();
                myCommandObject.Dispose();
            }
        }
        static void Main(string[] args)
        {
            int i = 0;
            Stopwatch mystopwatch = new Stopwatch();
            mystopwatch.Start();

            if (args[0] == "StraightLinq")
            {
                UseLinq(i);
            }

            if (args[0] == "SPLinq")
            {
                UseSPLinq(i);
            }

            if (args[0] == "T-SQL_DataReader")
            {
                TSQLDataReader(i);

            }

            if (args[0] == "SP_DataReader")
            {
                SPDataReader(i);

}

            mystopwatch.Stop();
            TimeSpan ts = mystopwatch.Elapsed;
            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
            ts.Hours, ts.Minutes, ts.Seconds,
            ts.Milliseconds);
            Console.WriteLine(elapsedTime, "RunTime");

        }
    }

Here's my results for 50,000 records:

	Avg (5 Runs)	Speed Difference
StraightLinq	0.5554	2.986022
SPLinq	0.4242	2.280645
T-SQL_DataReader	0.1886	1.013978
SP_DataReader	0.186	1

Here's my results for 1,000 records:

	Avg (5 Runs)	Speed Difference
StraightLinq	0.4984	1.372247
SPLinq	0.3632	1
T-SQL_DataReader	0.954	2.626652
SP_DataReader	0.948	2.610132

Are these results similar to what you are finding or is there an issue with my testing methodolgy? Drop a comment if you have suggestions on how to improve or correct this test.

Edit: Ugh these layouts are bad for wide data...sorry about that.

Edit: The extra for each loop in the T-Sql and SP versions of the test were the culprit to the higher values in the 1000 record test. With those loops removed the times were about .17

Ryan Smith : LINQ

Linq to SQL Speed Test Interesting Results