title: Why does this speed up my SQL query?
body: <p>I learned a trick a while back from a DBA friend to speed up certain SQL queries.  I remember him mentioning that it had something to do with how SQL Server compiles the query, and that the query path is forced to use the indexed value.</p>
  
  <p>Here is my original query (takes 20 seconds):</p>
  
  <pre><code>select Part.Id as PartId, Location.Id as LocationId
   FROM Part, PartEvent PartEventOuter, District, Location 
  WHERE 
      PartEventOuter.EventType = '600'   AND PartEventOuter.AddressId = Location.AddressId  
      AND Part.DistrictId = District.Id   AND Part.PartTypeId = 15   
      AND District.SubRegionId = 11   AND PartEventOuter.PartId = Part.Id  
      AND PartEventOuter.EventDateTime &lt;= '4/28/2009 4:30pm'   
      AND NOT EXISTS (
              SELECT PartEventInner.EventDateTime  
              FROM PartEvent PartEventInner
              WHERE PartEventInner.PartId = PartEventOuter.PartId
                  AND PartEventInner.EventDateTime &gt; PartEventOuter.EventDateTime 
                  AND PartEventInner.EventDateTime  &lt;= '4/30/2009 4:00pm')
  </code></pre>
  
  <p>Here is the "optimized" query (less than 1 second):</p>
  
  <pre><code>select Part.Id as PartId, Location.Id as LocationId
   FROM Part, PartEvent PartEventOuter, District, Location 
  WHERE 
      PartEventOuter.EventType = '600'   AND PartEventOuter.AddressId = Location.AddressId  
      AND Part.DistrictId = District.Id   AND Part.PartTypeId = 15   
      AND District.SubRegionId = 11   AND PartEventOuter.PartId = Part.Id  
      AND PartEventOuter.EventDateTime &lt;= '4/28/2009 4:30pm'   
      AND NOT EXISTS (
              SELECT PartEventInner.EventDateTime  
              FROM PartEvent PartEventInner
              WHERE PartEventInner.PartId = PartEventOuter.PartId
                  **AND EventType = EventType**
                  AND PartEventInner.EventDateTime &gt; PartEventOuter.EventDateTime 
                  AND PartEventInner.EventDateTime  &lt;= '4/30/2009 4:00pm')
  </code></pre>
  
  <p>Can anyone explain in detail why this runs so much faster?  I'm just trying to get a better understanding of this.</p>