title: SELECT with "datetime > string" performance issue in EF4 / SQL Server 2008
body: <p>I am using EntityFramework 4 to access a SQL Server 2008 database.</p>
  
  <p>One of the SQL queries that the EF generates is having a behavior that I cannot explain.
  The query is like this:</p>
  
  <pre><code>SELECT tableA.field1, tableA.field2, ...
  FROM tableA join tableB on tableA.field1 = tableB.field1
  WHERE
      tableA.field2 &gt; '20110825'
      and tableA.field3 in ('a', 'b', 'c,')
      and tableB.field4 = 'xxx'
  </code></pre>
  
  <p>Where tableA.field2 is <code>datetime not null</code>, and the other fields are varchars.
  tableA contains circa 1.5 million records, tableB contains circa 2 million records, and the query returns 1877 rows.</p>
  
  <p>The problem is, it returns them in 86 seconds, and that time changes dramatically when I change the '20110825' literal to older values.</p>
  
  <p>For instance if I put '20110725' the query returns 3483 rows in 35 milliseconds.</p>
  
  <p>I found out in the execution plan that the difference between the two lies in the indexes SQL Server chooses to use depending on the date used to compare.</p>
  
  <p>When it is taking time, the execution plan shows:</p>
  
  <ul>
  <li>50%: index seek on tableA.field2 (it's a clustered index on this field alone)</li>
  <li>50%: index seek on tableB.field1 (non-unique, non-clustered index on this field alone)</li>
  <li>0%: join</li>
  </ul>
  
  <p>When it is almost instantaneous, the execution plan shows:</p>
  
  <ul>
  <li>98%: index seek on tableA.field1 (non-unique, non-clustered index on this field alone)</li>
  <li>2%: index seek on tableB.field1 (non-unique, non-clustered index on this field alone)</li>
  <li>0%: join</li>
  </ul>
  
  <p>So it seems to me that the decision of the optimizer to use the clustered index on tableA.field2 is not optimal.</p>
  
  <p>Is there a flaw in the database design? In the SQL query?</p>
  
  <p>Can I force in any way the database to use the correct execution plan?</p>