2

I have the following query:

SELECT
    [iri].[RateItemKey],
    [iri].[RateItemType]
FROM
    [prism72].[dbo].[ispRateItem] AS [iri]
WHERE
    [iri].[Status] = 1
    AND (
        [iri].[RateItemType] IN (3,2)
    )

The ispRateItem table has the following indexes:

CREATE NONCLUSTERED INDEX [COVIX_ispRateItem_RateItemType_Status] ON [dbo].[ispRateItem] 
(
    [Status] ASC,
    [RateItemType] ASC
)
INCLUDE ( [RateItemKey]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [CLIX_ispRateItem] ON [dbo].[ispRateItem] 
(
    [RateItemKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ispRateItem] ADD  CONSTRAINT [RateItem_PK] PRIMARY KEY NONCLUSTERED 
(
    [RateItemKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

I've updated the statistics using:

UPDATE STATISTICS [ispRateItem] WITH FULLSCAN, ALL;

However I index the table I get the same cardinality issue: Est Rows 1565, Actual Rows 4315. I've even tried removing all indexes (resulting in a table scan).

What could be causing this?

Please note that I'm a beginner to query tuning.

Thanks.

Alan

Improve this question
6
  • How are your indexes looking? Do you have an index on both RateItemType and Status or distinct indexes on each? – Marian Oct 30 '14 at 10:43
  • Hi Marian, I've added the indexes into my question. – Alan T Oct 30 '14 at 10:50
  • Can you post somewhere (pastebin/dropbox) the actual execution plan XML? – Marian Oct 30 '14 at 11:10
  • Hi Marian, I've resolved the issue. See my response to James answer below. – Alan T Oct 30 '14 at 11:37
  • 2
    See a related problem, caused by implicit conversion: Query optimizer recommends adding index instead of using existing index – ypercubeᵀᴹ Oct 30 '14 at 12:22
2

I think your problem is the fact your WHERE clause has multiple predicates on the same table. This article by Paul White explains the issue.

It may be possible to get around this issue by having a computed column that does what your current WHERE clause does. You could then use this column on it's own but this would need a bit of testing to make sure it works OK.

The resolution:

James suggestion got me looking into multiple predicate issues. So I tested using single predicates and noticed that integers were being stored as varchar for the RateItemType field.

The problem was resolved by making the following change:

[iri].[RateItemType] IN (3,2)

to

[iri].[RateItemType] IN ('3','2')
Improve this answer
8
  • Hi James, That helped! It got me looking at running the query with only a single where clause. It turned out to be the fact that the RateItemType field is VARCHAR(2), even though it only holds integers. When I added single quotes around the numbers in the IN clause the cardinality problem went away. Thanks for your help. – Alan T Oct 30 '14 at 11:32
  • 1
    With all due respect, I don't think @James's answer is a solution here (the article yes, it's brilliant, the proposed solution I don't think so because it's not feasible to do computed columns every time estimate/actuals don't match). And also it doesn't match what your own fix was. By seeing the execution plan we could've seen that your query doesn't use the composite index but probably does a table scan with implicit conversion on that condition (just a guess now). – Marian Oct 30 '14 at 11:54
  • 1
    I agree with @Marian. The article is fantastic but talks about a query with 2 range conditions on 2 columns (such conditions cannot be satisfied effectively by a multi-column index.) This question has 1 equality and 1 range (IN) condition, so one multi-column index (the one the OP has already) can be used most effectively. So, the problem lies elsewhere. – ypercubeᵀᴹ Oct 30 '14 at 12:30
  • 1
    @AlanT fair enough. The problem with this is that future readers may not read the comments but in a similar problem, read the accepted answer that says "your problem is the fact your WHERE clause has multiple predicates on the same table" and get confused because that is not the main issue. And neither was a computed column how you solved it. – ypercubeᵀᴹ Oct 30 '14 at 13:12
  • 1
    @AlanT A quick reader might assume the solution is a computed column, and waste a lot of effort trying to solve the problem that way. If James doesn't want to do it, you could edit the answer to include information about what actually solved the problem. – Aaron Bertrand Oct 30 '14 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.