Why am I getting cardinality issues?
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.
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.
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 🙂
Hope that helps.