Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Playing with Linq2Sql and various performance tests

In my last post (here) I raised the question as to how to create the DTO/DataContext to be used when using Linq2Sql.

I had some free time so I thought I would do some speed tests to see if there was any speed differences between the various ways to use Linq2Sql.  Boy was I shocked by what I found.

Here are the speed results I received when I ran the 4 tests (please note, I ran these tests many times and the numbers always came out about the same)

  • Test 1 - 13022 average elapsed milliseconds (10k iterations)
  • Test 2 - 11321 average elapsed milliseconds (10k iterations)
  • Test 3 - 42838 average elapsed milliseconds (10k iterations)
  • Test 4 - 78042 average elapsed milliseconds (10k iterations)

Below is the code and tests I used to generate these results.

Test 1 - Used the code generated via the IDE
I am not going to show the actual generated code as it is long and auto generated.  You can generate the same code on your end.

Here is the code that I used to test this

        [Test]
        public void SimpleQuery_GeneratedCode()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (Int32 x = 0; x <= 10000; x++)
            {
                SportsDemoDataContext db = new SportsDemoDataContext(new SqlConnection(connectionString));
                IEnumerable sports = from s in db.Sports
                                            select s;

                foreach (Sport sport in sports)
                {
                    string name = sport.Name;
                }
            }

            sw.Stop();
            Debug.WriteLine(sw.ElapsedMilliseconds);
        }

Test 2 - use DTO code that matches the generated code as closely as
possible as well as a custom DataContext that closely matches the generated one.

Below is the DataContext that I created by hand, this pretty much is the same Context generated by the IDE.

    public class Sport2DataContext : DataContext
    {
        private static MappingSource mappingSource = new AttributeMappingSource();

        public Sport2DataContext(IDbConnection connection) : 
				base(connection, mappingSource)
		{
		}

        public Table Sports
        {
            get {return GetTable();}
        }
    }

Below is the DTO that I created by hand.  Please notice that the properties are standard properties (ones with private member variables) and that the attributes in use match the generated ones EXACTLY.

    [Table(Name = "Sport")]
    public class Sport2
    {
        private Int32 _sportID;
        private string _name;
        private string _description;
        private bool _active;

        [Column(Storage = "_sportID", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
        public virtual Int32 SportID
        {
            get { return _sportID; }
            set { _sportID = value; }
        }

        [Column(Storage = "_name", DbType = "VarChar(25) NOT NULL", CanBeNull = false)]
        public virtual string Name
        {
            get { return _name; }
            set { _name = value;}
        }

        [Column(Storage = "_description", DbType = "VarChar(100)")]
        public virtual string Description
        {
            get { return _description; }
            set { _description = value; }
        }

        [Column(Storage = "_active", DbType = "Bit NOT NULL")]
        public bool Active
        {
            get { return _active; }
            set { _active = value; }
        }
    }

Here is the code that I used to test this

        [Test]
        public void SimpleQuery_WithCustomDataContext()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (Int32 x = 0; x <= 10000; x++)
            {
                Sport2DataContext db = new Sport2DataContext(new SqlConnection(connectionString));

                IEnumerable<Sport2> sports = from s in db.Sports
                                             select s;

                foreach (Sport2 sport in sports)
                {
                    string name = sport.Name;
                }
            }

            sw.Stop();
            Debug.WriteLine(sw.ElapsedMilliseconds);
        }

Test 3 - Used code that I created
Below is the DTO that I created by hand.  Please notice that the properties are automatic properties and I am ONLY using the [Column] attribute.

 
    [Table(Name="Sport")]
    public class Sport
    {
        [Column]
        public virtual Int32 SportID { get; set; }

        [Column]
        public virtual string Name { get; set; }

        [Column]
        public virtual string Description { get; set; }

        [Column]
        public virtual bool Active { get; set; }
    }

Here is the code that I used to test this

        [Test]
        public void SimpleQuery_Without()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (Int32 x = 0; x <= 10000; x++)
            {
                DataContext db = new DataContext(new SqlConnection(connectionString));

                IEnumerable<Sport> sports = from s in db.Gettable<Sport>()
                                                select s;

                foreach (Sport sport in sports)
                {
                    string name = sport.Name;
                }
            }

            sw.Stop();
            Debug.WriteLine(sw.ElapsedMilliseconds);
        }

Test 4 - Used DTO code that matches the generated code as closely as possible, but used generic DataContext

Below is the DTO that I created by hand.  Please notice that the properties are standard properties (ones with private member variables) and that the attributes in use match the generated ones EXACTLY.

    [Table(Name = "Sport")]
    public class Sport2
    {
        private Int32 _sportID;
        private string _name;
        private string _description;
        private bool _active;

        [Column(Storage = "_sportID", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
        public virtual Int32 SportID
        {
            get { return _sportID; }
            set { _sportID = value; }
        }

        [Column(Storage = "_name", DbType = "VarChar(25) NOT NULL", CanBeNull = false)]
        public virtual string Name
        {
            get { return _name; }
            set { _name = value;}
        }

        [Column(Storage = "_description", DbType = "VarChar(100)")]
        public virtual string Description
        {
            get { return _description; }
            set { _description = value; }
        }

        [Column(Storage = "_active", DbType = "Bit NOT NULL")]
        public bool Active
        {
            get { return _active; }
            set { _active = value; }
        }
    }

Here is the code that I used to test this

        [Test]
        public void SimpleQuery_ClonedGeneratedCode()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            for (Int32 x = 0; x <= 10000; x++)
            {
                DataContext db = new DataContext(new SqlConnection(connectionString));
                IEnumerable<Sport2> sports = from s in db.GetTable<Sport2>()
                                             select s;

                foreach (Sport2 sport in sports)
                {
                    string name = sport.Name;
                }
            }

            sw.Stop();
            Debug.WriteLine(sw.ElapsedMilliseconds);
        }


What shocked me the most after running these tests was that the mimicked code using the generic DataContext was so much slower.  I figured it to be a little slower, but not that much.

I was NOT surprised to see that test 2 (custom DataContext) was slightly faster then test 1 (pure IDE generated code).  I figured that since I was raising events in the property setter I would be able to shave a bit of time off the execution.

So, what have these test2 proven?  That there is no real difference in speed between the IDE generated code and hand created code IF you use a typed DataContext.  Using a generic DataContext is MUCH slower, but I don't really understand why yet.

Till next time,


Posted 03-14-2008 9:08 AM by Derik Whittaker
Filed under:

[Advertisement]

Comments

Dew Drop - March 15, 2008 | Alvin Ashcraft's Morning Dew wrote Dew Drop - March 15, 2008 | Alvin Ashcraft's Morning Dew
on 03-15-2008 11:08 PM

Pingback from  Dew Drop - March 15, 2008 | Alvin Ashcraft's Morning Dew

Code Monkey Labs wrote Weekly Web Nuggets #3
on 02-22-2009 10:52 PM

No witty opening comment this week...sorry to disappoint. :) General Improve .Net Performance Effectively &amp; Efficiently : Microsoft's ACE team, who are are responsible for application performance, security, and privacy, has a great post about improving

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)