Indexed Computed Column Not Used to Perform Seek

Question

Indexed Computed Column Not Used to Perform Seek

Viewing 6 posts - 16 through 20 (of 20 total)

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

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

To give some more information, a functionally equivalent computed column that's calculated from a scalar function produces a seek as expected. Test case updated below to include both:

USE tempdb

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE name='Test_Computed_Index' AND type='U') <DROP> TABLE dbo.Test_Computed_Index

IF EXISTS (SELECT 1 FROM sys.objects WHERE name='fn_concat' AND type='FN') DROP FUNCTION fn_concat

GO

CREATE FUNCTION dbo.fn_concat (@system_ID int, @Reference int, @season varchar(3))

RETURNS Varchar(10) WITH SCHEMABINDING

AS

BEGIN

RETURN CAST(CASE WHEN @System_ID = 1

THEN CAST(@Reference AS VARCHAR(7))

+ '/'

+ @season

WHEN @System_ID = 2

THEN CAST(@Reference AS VARCHAR(10))

ELSE CAST(@Reference AS VARCHAR(10))

END AS VARCHAR(10))

END

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 CAST(CASE WHEN System_ID = 1

THEN CAST(Reference AS VARCHAR(7))

+ '/' + Season

WHEN System_ID = 2

THEN CAST(Reference AS VARCHAR(10))

ELSE CAST(Reference AS VARCHAR(10))

END AS VARCHAR(10)) PERSISTED ,

Computed_Concatenated_Field_From_Function AS dbo.fn_concat(system_ID,

Reference,

Season)

)

CREATE INDEX IDX_Computed_Concatenated_Field ON dbo.Test_Computed_Index(Computed_Concatenated_Field)

CREATE INDEX IDX_Computed_Concatenated_Field_Function ON dbo.Test_Computed_Index(Computed_Concatenated_Field_From_Function)

GO

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

--produces scan in 2008 SP1, seek in 2005 SP2

SELECT Test_ID FROM dbo.Test_Computed_Index

WHERE Computed_Concatenated_Field = '11/A01'

--produces seek in 2008 SP1 and 2005 SP2

SELECT Test_ID FROM dbo.Test_Computed_Index

WHERE Computed_Concatenated_Field_From_Function = '11/A01'

Does anyone have a SQL 2008 SP2 instance available they could test this on, so I know it's not already fixed (if it is a bug)?

P.S. Sorry for the brackets around the word <DROP> - please remove this when testing the code. Bizarrely, my corporate firewall detects it as a SQL Injection attempt and kills the connection if I remove the brackets 😀

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

Thanks Paul, yep that fixes it.

I'm aware PERSISTED isn't required if it's being indexed - it was one of the things I added in trying to troubleshoot what the problem might be 😛

Ok, so there's some sort of optimisation that evaluates adjacent case expressions simultaneously as they are the same calculation and this changes the definition behind the scenes? Makes sense - thanks for the explanation

LutzM SSC Guru Points: 107049 More actions · Answer 3

Wouldn't it be also an option just to remove the redundant CASE expression?

Something like

CASE

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

ELSE CAST(Reference AS VARCHAR(10))

END

I'm just wondering why there would be the need to add another expression leading to the same result as the ELSE part does... All I can think of is readability. Are there any other reasons?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

HowardW (3/29/2011)
Does anyone have a SQL 2008 SP2 instance available they could test this on, so I know it's not already fixed (if it is a bug)?

I was using a 2008 SP2 earlier. Same behaviour.

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

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

HowardW (3/29/2011)
...so I know it's not already fixed (if it is a bug)?

It's a regression, but not a bug IMO. It seems there has been an enhancement that collapses duplicate expressions - so the behaviour seen here is just an unfortunate side-effect (the literal computed column definition can't be matched to the optimizer's metadata, so an index seek is not available). No doubt there are cases where this extra optimization produces better plans, so the regression is unfortunate but easily avoided.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

LutzM (3/29/2011)
Wouldn't it be also an option just to remove the redundant CASE expression?
Something like
CASE
WHEN System_ID = 1 THEN CAST(Reference AS VARCHAR(10)) + '/' + CAST(Season AS VARCHAR(10))
ELSE CAST(Reference AS VARCHAR(10))
END
I'm just wondering why there would be the need to add another expression leading to the same result as the ELSE part does... All I can think of is readability. Are there any other reasons?

Yes, it's definitely redundant in the test example. The real code was slightly different, there were more than three case conditions and it wasn't the second to last + else that were identical. The original case was something like this:

CASE WHEN <BOOLEAN EXPRESSION1> THEN 'Do this' WHEN <BOOLEAN EXPRESSION2>THEN 'Do This' .....

which I've now re-written as:

CASE WHEN <BOOLEAN EXPRESSION1> or <BOOLEAN EXPRESSION2> THEN 'Do This'

.....

Thanks everyone for the help - I've learned loads today