CODEWITHSUNDEEP
sql-serverquery-optimization
d-man
d-man

Reputation: 58073

Optimize query by looking at query plan

optimize Sqlserver 2005 query by query plan

I have a following query which takes 1.7 seconds to pull 72 records can take more time if more items available.

When i run query plan i found expensive operation is "ReviewCompleteFlag =0 AND ReviewItemDeleteFlag = 0 AND ActivationDateTime" b/c non of them having index

ReviewCompleteFlag value can be 0 or 1
ReviewItemDeleteFlag values can be 0 or 1
itemid is foreign key

is there a way i can optimize this query ?

SELECT

      TOP 200000

    ItemId
    FROM
    ItemReview (nolock)
    WHERE ReviewCompleteFlag =0 AND ReviewItemDeleteFlag = 0 AND ActivationDateTime is null
    ORDER BY ReviewPriority DESC

Upvotes: 0

Views: 88

Answers (1)

HLGEM
HLGEM

Reputation: 96552

As booleans (even if defined as int, you state they only have two values) I would suspect that neither ReviewCompleteFlag or ReviewItemDeleteFlag would be helped by indexes. Indexes generally require more variablity in the data to be useful.

I would consider trying indexes on ActivationDateTime and ReviewPriority.

It is also possible that once you have the index on ActivationDateTime that a derived table might be faster (At least then you are applying the unindexed booleans to a smaller set and that could work to your advantage (try first of course and measure with your datbase setup and adatabase):

SELECT
  TOP 200000
ItemId
FROM
    (SELECT ItemID, ReviewCompleteFlag, ReviewItemDeleteFlag, ReviewPriority 
    FROM  ItemReview 
    WHERE ActivationDateTime is null) IR  WITH (nolock)
WHERE ReviewCompleteFlag =0 AND ReviewItemDeleteFlag = 0     ORDER BY ReviewPriority DESC

Upvotes: 2

Related Questions