title: Sql server 2005 seems to take forever with certain standard where clauses
body: <p>Here's my table schema</p>
  
  <pre><code>[dbo].[Action_History](
      [ActionID] [int] IDENTITY(1,1) NOT NULL,
      [objectID] [int] NOT NULL,
      [object_mask] [varchar](max) NULL,
      [description] [varchar](max) NOT NULL,
      [action_by] [nchar](7) NOT NULL,
      [action_date] [datetime] NOT NULL,
      [response_required] [bit] NOT NULL,
      [responded_date] [datetime] NULL,
      [responded_by] [nchar](7) NULL,
      [recurring] [bit] NULL CONSTRAINT [DF_Action_History_recurring]  DEFAULT ((0)),
      [actionTypeID] [int] NULL,
      [target_user] [nchar](7) NULL,
      [target_role] [varchar](25) NULL,
      [object_type] [varchar](30) NULL, CONSTRAINT [PK_Action_History] PRIMARY KEY CLUSTERED 
  </code></pre>
  
  <p>Here is query 1:</p>
  
  <pre><code>SELECT    
      Top(1) 
      ActionID, objectID, object_mask, 
      [description], action_by, action_date, response_required, 
      responded_date, responded_by, recurring, actionTypeID, 
      target_user, target_role, object_type
  FROM
      Action_History
  WHERE     
      ((objectID = 201006)
      AND (responded_date is null)    
      AND (object_type = 'MyType'))
  </code></pre>
  
  <p>and Query 2:</p>
  
  <pre><code>SELECT    
      Top(1) 
      ActionID, objectID, object_mask, 
      [description], action_by, action_date, response_required, 
      responded_date, responded_by, recurring, actionTypeID, 
      target_user, target_role, object_type
  FROM
      Action_History
  WHERE     
      ((objectID = 201006)
      AND (responded_date is null)    
      AND (object_type = 'Mytype')
      AND (actionTypeID = 55) 
      AND (response_required = 1))
  </code></pre>
  
  <p>Query 1 will load in 0 seconds, however, query 2 fails to ever return results. Either of those last 2 and conditionals in the where clause will cause the query to hang. Also, If I just have the 2-5 conditionals (no objectID), it seems to run just as fast.</p>
  
  <p>I need query two to work with all the conditions. Any ideas?</p>
  
  <p>Thanks,</p>
  
  <p>~P</p>
  
  <p><strong>Edit:</strong> seems like the second query hangs if the objectID doesn't exist, but if it does it loads no problem.</p>
  
  <p><strong>Edit 2:</strong> I have a single index on actionID, which I know isn't terribly helpful in this case. I also don't have the ability at the moment to create any other index's (caulk it up to over protective db security).</p>
  
  <p>I'm pretty terrible at db stuff - but when I cursor over the Clustered Index scan from "Displayed Estimated Execution Plan" for the two different queries all I see is a slightly different predicate which looks identical to my my predicate - probably not the correct execution plan...</p>
  
  <p><strong>Edit 3:</strong> Execution Plan - they look similar except the 2 where conditionals are missing. Further it seems like my second query runs in no time flat if there is a result. If there is no result it runs forever (There is a record with identical values except the objectid is 201002 not 201006). Also we're talking less than 4K records.</p>
  
  <pre><code>  |--Top(TOP EXPRESSION:((1)))
         |--Clustered Index Scan(OBJECT:([db].[dbo].[Action_History].[PK_Action_History]), 
                                  WHERE:([db].[dbo].[Action_History].[objectID]=(201002) AND 
                                         [db].[dbo].[Action_History].[responded_date] IS NULL AND 
                                         [db].[dbo].[Action_History].[actionTypeID]=(55) AND 
                                         [db].[dbo].[Action_History].[response_required]=(1) AND 
                                         [db].[dbo].[Action_History].[object_type]='MyType'))
  </code></pre>
  
  <p><strong>Edit 4:</strong> Looks like running my second query on our second database (with 51K record) runs just fine. I have NO idea the difference between the two databases, any thoughts on how I could figure that out?</p>