title: Parametrized Linq to SQL query causes performance issues
body: <p>I have a linq query that takes 11 minutes to execute against MSSQL server 2008. I used MSSQL Profiler to find the query taking so long to execute, and I ran it alone towards my database. </p>
  
  <p>I also removed all parameters and added the values directly and ran the query. It took less then 1 second to execute!</p>
  
  <p>I have googled and found that using parameters can really impact the performance because <a href="https://stackoverflow.com/questions/17033615/t-sql-select-from-view-much-slower-with-variable">the plan is compiled before the value of the where clause is known</a>.</p>
  
  <p><strong>Since Linq to SQL always run parametrized SQL, what can I do to improve performance in this case?</strong></p>
  
  <p>I haven't found anything I can improve on columns regarding indexes. The first table in the Inner Join statement has 192 014 rows, and the SQL without parameters takes less than a second to execute. Screenshots of execution plans attached.</p>
  
  <p><strong>Edits are below the screenshots.</strong></p>
  
  <p><strong>This is the Linq query:</strong></p>
  
  <pre><code>var criteria = CreateBaseCriteria();
  
  var wordsGroup = from word in QueryExecutor.GetSearchWords()
                   join searchEntry in QueryExecutor.GetReportData(criteria) on (word.SearchID + 100000000) equals searchEntry.EventId
                    group searchEntry by word.SearchWord into wg
                    select new SearchAggregate
                    {
                        Value = wg.Key,
                        FirstTime = wg.Min(l =&gt; l.EventTime),
                        LastTime = wg.Max(l =&gt; l.EventTime),
                        AverageHits = wg.Average(l =&gt; l.NumberOfHits.HasValue ? l.NumberOfHits.Value : 0),
                        Count = wg.Count()
                    };
  
  return wordsGroup.OrderByDescending(w =&gt; w.Count).Take(maxRows);
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/9pEY1.png" alt="Execution plan of query with parameters (linq to sql)">
  <img src="https://i.stack.imgur.com/uHcdA.png" alt="Execution plan of query with known values"></p>
  
  <p>Edit: The screenshots did go a little small in here. There are only 5 parameters in the parametrized SQL.</p>
  
  <p><strong>Edit 2:</strong> It is the Inner Join statement with parameter @p0 which causes the execution plan to change. When I only removed @p0 variable with the value itself, it runs in less then a second. If this value is constant in all cases (I have to investigate that) can I do anything so that this value doesn't get used like a parameter?</p>