Sql server 2005 seems to take forever with certain standard where clauses

Question

Here's my table schema

[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 

Here is query 1:

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 Query 2:

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))

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.

I need query two to work with all the conditions. Any ideas?

Thanks,

~P

Edit: seems like the second query hangs if the objectID doesn't exist, but if it does it loads no problem.

Edit 2: 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).

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...

Edit 3: 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.

  |--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'))

Edit 4: 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?

Answer 1

do you have indexes on actionTypeID and response_required? Compare the execution plans between the 2 queries

To see the text version of the execution plan run the following before the query

SET SHOWPLAN_TEXT ON
GO

to turn it off later run

SET SHOWPLAN_TEXT OFF
GO

Answer 2

Is there any sort of indexes on ActionTypeID / response_required.

Answer 3

Turn on execution plan display and check if management studio is suggesting something. Create index on those columns.

If this doesn't help, create CREATE TABLE statement and post it here to check data types and indexes.

Answer 4

Add indexes on any fields you want to use for criteria in the WHERE clause, sorting, or relationships with other tables.

Also, try it without the top(1). You can sort if you just want the first result, but it can hide errors in your query if that's what's limiting your results to 1 row.

Answer 5

Turns out one or more of the rows were corrupted, so when the the db scanned to those rows it froze / looped, or something. Thanks all for the help.