title: Slow performance on LINQ query against SQL server
body: <p>I want to find geographic names that starts with characters entered in a search box. Some geographic names have alternative names in other languages. These alternative names are stored in a separate table.</p>
  
  <pre><code>GN_Name 1 - 0:N GN_AlternateName
  </code></pre>
  
  <p>(PK)GN_Name.GeoNameId == (FK)GN_AlternateName.GeoNameId</p>
  
  <p>I want to search the name in GN_AlternateName.AlternateName first and if that doesn't exist, use the corresponding GN_Name.Name.</p>
  
  <p>I wrote following LINQ query:</p>
  
  <pre><code>return (from name in db.GN_Name
          where name.CountryCode == "se"
          join alt in db.GN_AlternateName 
          on name.GeoNameId equals alt.GeoNameId into outer
          from alt in outer.DefaultIfEmpty()
          where ((alt.IsoLanguage == "sv" &amp;&amp; 
                  alt.AlternateName.StartsWith(query)) || 
                  name.Name.StartsWith(query))
          select new GeoNameModel { 
              Language = alt.IsoLanguage, 
              Name = (alt == null ? name.Name : alt.AlternateName),
              FeatureClass = name.FeatureClass, 
              FeatureCode = name.FeatureCode, 
              GeoNameId = name.GeoNameId, 
              UniqueName = name.UniqueName,
              UniqueCount = name.UniqueCount}).Take(HB.AutoCompleteCount);
  </code></pre>
  
  <p>That translates into the following SQL:</p>
  
  <pre><code>exec sp_executesql N'SELECT 
  [Limit1].[GeoNameId] AS [GeoNameId], 
  [Limit1].[IsoLanguage] AS [IsoLanguage], 
  [Limit1].[C1] AS [C1], 
  [Limit1].[FeatureClass] AS [FeatureClass], 
  [Limit1].[FeatureCode] AS [FeatureCode], 
  [Limit1].[UniqueName] AS [UniqueName], 
  [Limit1].[UniqueCount] AS [UniqueCount]
  FROM ( SELECT TOP (5) 
      [Extent1].[GeoNameId] AS [GeoNameId], 
      [Extent1].[FeatureClass] AS [FeatureClass], 
      [Extent1].[FeatureCode] AS [FeatureCode], 
      [Extent1].[UniqueName] AS [UniqueName], 
      [Extent1].[UniqueCount] AS [UniqueCount], 
      CASE WHEN ([Extent2].[AlternateNameId] IS NULL) THEN [Extent1].[Name] ELSE [Extent2].[AlternateName] END AS [C1], 
      [Extent2].[IsoLanguage] AS [IsoLanguage]
      FROM  [dbo].[GN_Name] AS [Extent1]
      LEFT OUTER JOIN [dbo].[GN_AlternateName] AS [Extent2] ON [Extent1].[GeoNameId] = [Extent2].[GeoNameId]
      WHERE (''se'' = [Extent1].[CountryCode]) AND (((''sv'' = [Extent2].[IsoLanguage]) AND ([Extent2].[AlternateName] LIKE @p__linq__0 ESCAPE N''~'')) OR ([Extent1].[Name] LIKE @p__linq__1 ESCAPE N''~''))
  )  AS [Limit1]',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'ja%',@p__linq__1=N'ja%'
  </code></pre>
  
  <p>I can't really see whats wrong with it, but it takes around 5 seconds to complete. </p>
  
  <p>Should i add some index? Maybe set up an indexed view? 
  My SQL server knowledge is limited and i would love to get back to some real coding ;)</p>
  
  <p>Any suggestions warmly appreciated!</p>
  
  <p><strong>UPDATE</strong>
  I'm using SQL server 2008. Following the instructions of taylonr i got the following results.
  <img src="https://i.stack.imgur.com/DxuR5.png" alt="Client statistics">
  <img src="https://i.stack.imgur.com/zhE4c.png" alt="Execution plan">
  <img src="https://i.stack.imgur.com/MM8Lm.png" alt="Execution plan details"></p>
  
  <p>There are 3 "parts" that make up 100% of the total. I, however, don't have a clue on how to use these statistics.</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>SSMS Execution Plan recommended the following index:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX IX_GN_Name_CountryCode
  ON [dbo].[GN_Name] ([CountryCode])
  INCLUDE ([GeoNameId],[Name],[FeatureClass],[FeatureCode],[UniqueName],[UniqueCount])
  </code></pre>
  
  <p>I added it and the query now runs much better!</p>
  
  <p><strong>UPDATE 3</strong>
  taylonr suggests using only one LIKE clause. I'm not sure how to accomplish this. Anyone up for the challenge?</p>