title: Why does my primary key slow down a simple linq-to-sql query?
body: <p>I have a very simple table in SQL Server that has a compound primary key. The table is quite small with less than 10 MB of data. I am working with the table in C# using a data context auto-generated by SqlMetal. I filled the table with some initial data and was surprised when a simple select where query took over 30 seconds to complete! I imagined I should be able to read all 10 MB of data from disk and push it over our gigabit network in less than a second. I verified this was true by running the same query through Microsoft's SSMS.</p>
  
  <p>So, I started playing around with the data to figure out what was up. I found that if I removed the primary key from the table (and regenerated my SqlMetal file), the query returned my data in less than a second. Perfect! Except not, because no primary key. I added the key back, regenerated the SqlMetal file, and ran query again. Slow again! Next I removed the key from the table without regenerating the SqlMetal file. Still slow! I think all this tells me the problem lies on the client side, perhaps in the implementation of linq-to-sql or the data context generated by SqlMetal. </p>
  
  <p>So I started creating some test code and data that I could share on this site. I created a test database table with the same schema, code that filled the table with roughly the same amount of data, and of course the simple query that was taking so long. I ran it to make sure everything worked... Lo and behold, the query ran fairly fast. Is my actual data to blame? </p>
  
  <p>So I went about trying to get my test data to resemble my real data as closely as possible. My actual data has a natural key composed of a <code>bigint</code> and <code>nvarchar(64)</code>. I then add another <code>bigint</code> as a third element to the primary key for the purpose of managing versions. When a new items get added to the data set, I give it a unique <code>ID</code> (<code>bigint</code>) and give it a version ID equal to its ID. Subsequent changes to that element generate new version IDs (<code>bigint</code>). So, as I initially populated the table with all new elements, all version IDs equaled the elements' IDs. </p>
  
  <p>I replicated this in my test data and sure enough, the query slowed to a crawl. I'm trying to understand what is happening with this query. It is obviously not an issue with the query execution plan on the server because it runs fast under any scenario using SSMS. What is linq-to-sql doing with my data before it returns my query results? Why does the profile of the data (namely the primary key values) affect the processing speed on the client side? </p>
  
  <p>Here is some code to help if you want to test this:</p>
  
  <p>SQL Server table generation:</p>
  
  <pre><code>CREATE TABLE [dbo].[TestClusteredIndex]
  (
      [VID] [BIGINT] NOT NULL,
      [EID] [BIGINT] NOT NULL,
      [Filter] [NVARCHAR](64) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
      [MID] [NVARCHAR](64) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
      [SD] [DATE] NULL,
      [ED] [DATE] NULL,
      [B] [BIT] NOT NULL,
  
      CONSTRAINT [PK_TestClusteredIndex] 
          PRIMARY KEY CLUSTERED ([EID] ASC, [Filter] ASC, [VID] ASC)
  ) ON [PRIMARY]
  GO
  
  CREATE TABLE [dbo].[TestHeap](
      [VID] [bigint] NOT NULL,
      [EID] [bigint] NOT NULL,
      [Filter] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
      [MID] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
      [SD] [date] NULL,
      [ED] [date] NULL,
      [B] [bit] NOT NULL
  ) ON [PRIMARY]
  
  GO
  </code></pre>
  
  <p>Generate your own DataContext using SqlMetal. </p>
  
  <p>C# Test Code:</p>
  
  <pre><code>    public static void RunTests()
      {
          FillDB(false);
          TestSpeedWithPK(true);
          TestSpeedWithoutPK(true);
  
          FillDB(true);
          TestSpeedWithPK(true);
          TestSpeedWithoutPK(true);
      }
  
      public static void FillDB(bool problematic)
      {
          long l1 = 5000;
          long l2 = 750000;
          bool dummy = true;
          var db = new YourDataContext(@"YourConnectionString");
          Console.WriteLine("\nClearing database tables...");
          db.ExecuteCommand("DELETE FROM [TestClusteredIndex];");
          db.ExecuteCommand("DELETE FROM [TestHeap];");
  
          var rows = new List&lt;TestClusteredIndex&gt;();
          Console.WriteLine("Generating " + (problematic ? string.Empty : "non-") + "problematic data...");
  
          for (int i = 0; i &lt; 120000; i++)
          {
              var row = new TestClusteredIndex
              {
                  EID = problematic ? i : l1++,
                  VID = problematic ? i : l2++,
                  Filter = "Filter" + (char)(i % 3 + 65),
                  MID = (i / 0.5).GetHashCode().ToString(),
                  SD = null,
                  ED = null,
                  B = dummy = !dummy
              };
  
              rows.Add(row);
          }
          Console.WriteLine("Saving " + rows.Count + " rows to table with primary key...");
          db.TestClusteredIndex.InsertAllOnSubmit(rows);
          db.SubmitChanges();
          Console.WriteLine("Copying data to table with no primary key...");
          db.ExecuteCommand("INSERT INTO [TestHeap]([VID],[EID],[Filter],[MID],[SD],[ED],[B]) SELECT [VID],[EID],[Filter],[MID],[SD],[ED],[B] FROM [TestClusteredIndex];");
          Console.WriteLine("Done creating test data.\n");
      }
  
      public static void TestSpeedWithPK(bool displayQuery)
      {
          Console.Write("Running speed test (primary key: yes)... ");
          var db = new YourDataContext(@"YourConnectionString");
          var sw = new Stopwatch();
          sw.Start();
  
          var items = db.TestClusteredIndex
              .Where(x =&gt; x.Filter == "FilterA")
              .ToList();
  
          sw.Stop();
          Console.WriteLine("Queried " + items.Count + " records in " + Math.Round(sw.ElapsedMilliseconds / 1000.0, 2) + " seconds.");
      }
  
      public static void TestSpeedWithoutPK(bool displayQuery)
      {
          Console.Write("Running speed test (primary key:  no)... ");
          var db = new YourDataContext(@"YourConnectionString");
          var sw = new Stopwatch();
          sw.Start();
  
          var items = db.TestHeap
              .Where(x =&gt; x.Filter == "FilterA")
              .ToList();
  
          sw.Stop();
          Console.WriteLine("Queried " + items.Count + " records in " + Math.Round(sw.ElapsedMilliseconds / 1000.0, 2) + " seconds.");
      }
  </code></pre>
  
  <p>Here is the output when I run the test scripts:</p>
  
  <pre><code>/*
  Clearing database tables...
  Generating non-problematic data...
  Saving 120000 rows to table with primary key...
  Copying data to table with no primary key...
  Done creating test data.
  
  Running speed test (primary key: yes)... Queried 40000 records in 0.29 seconds.
  
  @p0:FilterA
  SELECT [t0].[VID], [t0].[EID], [t0].[Filter], [t0].[MID], [t0].[SD], [t0].[ED], [t0].[B]
  FROM [dbo].[TestClusteredIndex] AS [t0]
  WHERE [t0].[Filter] = @p0
  
  Running speed test (primary key:  no)... Queried 40000 records in 0.09 seconds.
  
  @p0:FilterA
  SELECT [t0].[VID], [t0].[EID], [t0].[Filter], [t0].[MID], [t0].[SD], [t0].[ED], [t0].[B]
  FROM [dbo].[TestHeap] AS [t0]
  WHERE [t0].[Filter] = @p0
  
  Clearing database tables...
  Generating problematic data...
  Saving 120000 rows to table with primary key...
  Copying data to table with no primary key...
  Done creating test data.
  
  Running speed test (primary key: yes)... Queried 40000 records in 30.46 seconds.
  
  @p0:FilterA
  SELECT [t0].[VID], [t0].[EID], [t0].[Filter], [t0].[MID], [t0].[SD], [t0].[ED], [t0].[B]
  FROM [dbo].[TestClusteredIndex] AS [t0]
  WHERE [t0].[Filter] = @p0
  
  Running speed test (primary key:  no)... Queried 40000 records in 0.07 seconds.
  
  @p0:FilterA
  SELECT [t0].[VID], [t0].[EID], [t0].[Filter], [t0].[MID], [t0].[SD], [t0].[ED], [t0].[B]
  FROM [dbo].[TestHeap] AS [t0]
  WHERE [t0].[Filter] = @p0
  */
  </code></pre>