Indexed Computed Column Not Used to Perform Seek

Question

Indexed Computed Column Not Used to Perform Seek

HowardW

One Orange Chip

Points: 29109
More actions
March 29, 2011 at 5:22 am

#234750

Hi,
To set the scene, I saw that an end user query in an reporting application was using a case statement to restrict results which was preventing an index seek being used.
I though this would be a good candidate for a indexed computed column to enable a seek to be performed. However, after creating the index and the plan not being changed (the optimiser can use the indexed column even if it's not specified in the select) I changed the query to reference the computed column directly, assuming this must work, but found that the index is still not used.
I set up the below test scenario which in my environment (SQL 2008 SP1) consistently produces a scan rather than a seek:
USE tempdb
GO
CREATE TABLE Test_Computed_Index
(
Test_ID INT PRIMARY KEY CLUSTERED
IDENTITY(1, 1) ,
Reference INT NOT NULL ,
Season VARCHAR(3) NOT NULL ,
System_ID INT ,
Computed_Concatenated_Field AS CASE WHEN System_ID = 1
THEN CAST(Reference AS VARCHAR(10))
+ '/'
+ CAST(Season AS VARCHAR(10))
WHEN System_ID = 2
THEN CAST(Reference AS VARCHAR(10))
ELSE CAST(Reference AS VARCHAR(10))
END PERSISTED
)
CREATE INDEX IDX_Computed_Concatenated_Field ON dbo.Test_Computed_Index(Computed_Concatenated_Field)
INSERT INTO dbo.Test_Computed_Index
( Reference, Season, System_ID )
SELECT 1, 'A01',1 UNION ALL
SELECT 2,'A02',2 UNION ALL
SELECT 3, 'A01',1 UNION ALL
SELECT 4,'A02',2 UNION ALL
SELECT 5, 'A01',1 UNION ALL
SELECT 6,'A02',2 UNION ALL
SELECT 7, 'A01',1 UNION ALL
SELECT 8,'A02',2 UNION ALL
SELECT 9, 'A01',1 UNION ALL
SELECT 10,'A02',2 UNION ALL
SELECT 11, 'A01',1 UNION ALL
SELECT 12,'A02',2 UNION ALL
SELECT 13, 'A01',1 UNION ALL
SELECT 14,'A02',2
--SHOW ACTUAL EXECUTION PLAN FOR THE FINAL SELECT
SELECT Test_ID FROM dbo.Test_Computed_Index
WHERE Computed_Concatenated_Field='11/A01'
Interestingly, when digging into the plan, even though the computed column is specified by name and is persisted/indexed, the predicate is replaced with the case statement used to create the column and a scan is performed rather than a seek.
Has anyone come across this before or know a workaround? Is there something fundamental wrong with the setup, or are computed columns just very limited in terms of being used?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Post the plan please?

Index scan or clustered index scan? If the latter, indicative that the predicate is not sufficiently selective and the index is not covering.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Hi Gail,

Clustered Index Scan

Please find the plan attached

Thanks,

Howard

Lowell SSC Guru Points: 323518 More actions · Answer 3

Howard as an FYI for your testing, when i ran your code on SQL2005 SP2, it consistently did an index seek, but on either of my SQL2008 machines, it opted for the clustered index scan instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Odd. What happens if you force the index (index hint)?

What editions of SQL?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 5

Lowell (3/29/2011)
Howard as an FYI for your testing, when i ran your code on SQL2005 SP2, it consistently did an index seek, but on either of my SQL2008 machines, it opted for the clustered index scan instead.

Thanks Lowell, that's interesting. Now I'm even more intrigued!

FYI, the real table is 2 million rows and the filter restricts to a single row (and I've updated statistics) - if I remove the case from the computed column so it's always a concatenation, a seek is used every time.

Gail - adding WITH (INDEX(IDX_Computed_Concatenated_Field)) does an index scan of the computed index, but still not a seek, so it's almost as though it doesn't think a seek is safe (all collation settings are as default)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Odd, when the index is forced, it does key lookups.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 7

GilaMonster (3/29/2011)
What editions of SQL?

Standard Edition - Other question answered above

HowardW One Orange Chip Points: 29109 More actions · Answer 8

GilaMonster (3/29/2011)
Odd, when the index is forced, it seeks, but does key lookups.
Try including SystemID and Reference in that index. They should not be necessary, but somehow it appears SQL thinks they are.

OK, in my environment, forcing the index definitely still produces a scan (then a key lookup) not a seek.

Adding System_ID and Reference as Included Columns to the index does not change the plan for me...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

HowardW (3/29/2011)
GilaMonster (3/29/2011)
Odd, when the index is forced, it seeks, but does key lookups.
Try including SystemID and Reference in that index. They should not be necessary, but somehow it appears SQL thinks they are.
OK, in my environment, forcing the index definitely still produces a scan (then a key lookup) not a seek.

my mistake, it scans here too.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 10

When adding the following hint:

WITH (INDEX(IDX_Computed_Concatenated_Field), FORCESEEK)

I also get the error:

Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Starting to look like a bug, especially since it works as expected on SQL 2005. Let me get some additional opinions....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HowardW One Orange Chip Points: 29109 More actions · Answer 12

Lowell (3/29/2011)
Howard as an FYI for your testing, when i ran your code on SQL2005 SP2, it consistently did an index seek, but on either of my SQL2008 machines, it opted for the clustered index scan instead.

I've just had a look on a SQL 2005 as well and it looks like the big difference is the predicate is not replaced with the definition of the computed column so the seek is performed. I've attached the plan for this as well for comparison

HowardW One Orange Chip Points: 29109 More actions · Answer 13

GilaMonster (3/29/2011)
Starting to look like a bug, especially since it works as expected on SQL 2005. Let me get some additional opinions....

Thanks Gail

Paul White SSC Guru Points: 150467 More actions · Answer 14

This looks like a regression, but there is an easy workaround. The issue arises adjacent CASE expressions exactly match. In your case, the ELSE clause of the CASE exactly matches the last CASE expression.

The simple rewrite is to reorder the CASE expressions. You could also CAST one result to VARCHAR(11) instead of VARCHAR(10), for exmaple. Any logical difference is enough to avoid the bug.

CREATE TABLE dbo.Test_Computed_Index

(

Test_ID INT PRIMARY KEY CLUSTERED IDENTITY(1, 1),

Reference INT NOT NULL,

Season VARCHAR(3) NOT NULL,

System_ID INT NOT NULL,

Computed_Concatenated_Field AS

CASE

WHEN System_ID = 2 THEN CAST(Reference AS VARCHAR(10))

WHEN System_ID = 1 THEN CAST(Reference AS VARCHAR(10)) + '/' + CAST(Season AS VARCHAR(10))

ELSE CAST(Reference AS VARCHAR(10))

END

)

;

In the above code, I simply put case 2 above case 1. I also removed the PERSISTED attribute since that is not required to build an index on the column (there are no imprecise values). Removing the PERSISTED attribute is not part of the workaround.

The cause seems to be due to a simplification rule that results in an expression that no longer matches the metadata definition. This is similar to an ISNULL bug I saw a while ago. The take away is to avoid using identical expression results in CASE statements. It's a little redundant anyway.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi