Why am I getting cardinality issues?

Alan 2014-10-30 10:22:58

Hi

I'm trying to tune a large query. I'm looking into cardinality issues. To help with this I broke out the search of a single table into a separate query. However I index the table there are still cardinality issues. I've attached the query plan.

What am I doing wrong?

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

Thanks.

Alan

Alan 2014-10-30 11:36:18
Hi

It turned out that the cardinality issue was caused by integers being stored in a varchar field. The RateItemType field only holds integers. However, its type is VARCHAR(2). Once I put single quotes around the numbers in the IN part of the where clause the problem went away.

Mart 2014-11-13 11:41:33
Hey Alan, I know you've got this as answered but I thought a bit more of a reason around why may be useful to others.

The plan shows implicit conversions for both items within the IN part of the where clause. It's converting the [prism72].[dbo].[ispRateItem].[RateItemType] to an INT for comparison with the numbers (Integers) in the IN statement, this is shown in the predicate of the operator:

CONVERT_IMPLICIT(int,[prism72].[dbo].[ispRateItem].[RateItemType] as [iri].[RateItemType],0)=(2) OR CONVERT_IMPLICIT(int,[prism72].[dbo].[ispRateItem].[RateItemType] as [iri].[RateItemType],0)=(3)

Putting the quotes around the numbers in the where clause allow it to be treated as a string, just as the the varchar(2), they can then be directly compared.

A point to note, varchar(2) would be better as char(2). The varchar and nvarchar datatypes have a hidden 2 bytes that are used to show how long the data within the field is, therefore using varchar(2) takes up 4 bytes, using char(2) would only take 2 bytes 🙂

http://msdn.microsoft.com/en-us/library/ms176089.aspx

Hope that helps.