Computed Columns Performance and Indexes

Question

Hi Experts,

I wanted to test the performance of index on computed column persisted vs. non-persisted computed column. I am NOT seeing any benefits one vs. other. What am I missing? Please help. 

Theoretically, if I create a index on persisted computed column and if I am searching on it, it should be better than searching on computed column that is not persisted and  not indexed. But it is not true in my case.

Here is what I have. 

CREATE TABLE [dbo].[temp_cc](
[aid] [int] IDENTITY(1,1) NOT NULL,
[nLeadID] [int] NULL,
[tPhone] [nvarchar](20) NULL,
[tPhone_CC1]  AS ([dbo].[getcharacters]([tPhone])),
[tPhone_CC2]  AS ([dbo].[getcharacters]([tPhone])) PERSISTED
) ON [PRIMARY]

I have about 1.7M records in the above table.  The function removes formatting out of phone field and returns digits only.

I have created index on the table

create index ccindex on temp_cc (tPhone_cc2,nLeadId)
create index ccindex1 on temp_cc (tPhone_cc2) include (nLeadId)

create FUNCTION dbo.GetCharacters(@myString as varchar(500))  
  
RETURNS nvarchar(500)   
with schemabinding  
AS  
  
BEGIN  
   
 Declare @validchars nvarchar(100)  
 
 set @validChars='0-9'  
 
   
 While @myString like '%[^' + @validChars + ']%'  
  Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')  
  
 Return @myString  
END

Then, I am running the following query

select nleadid from temp_cc 
where tphone_cc1 = '6195551236'

select nleadid from temp_cc 
where tphone_cc2 = '6195551236'  

When I look at execution plan, both are at 50% and logical reads are almost same. Why is SQL not using the index??? Why are they same?

Table 'temp_cc'. Scan count 1, logical reads 12254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 41777 ms,  elapsed time = 48500 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'temp_cc'. Scan count 1, logical reads 12254, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 43088 ms,  elapsed time = 69338 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Answer 1

Did you tried giving index hint to the query? if not please try to give index hint and check the performance of the query and execution plan. some times SQL Server optimizer may not select correct execution plan, if you are sure your index will increase performance of the query, you can always give index hint.

---

Thanks & Regards Prasad DVR

Answer 2

Both queries are not using indexes?

From what i see in your post,  You have created the index on one column tPhone_CC2 and your where clause in your select queries are different.

Can you share the execution plan for both queries?

---

Satheesh

Answer 3

Whats the return datatype for getcharacters? Do you have any IMPLICIT CONVERSION in the execution plan?

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

Hi Prasad,

Thanks for reply. I have tried forcing the index. It performs worse and it is showing worse because of bookmark lookup.

Answer 5

Answer 6

How many rows actually it returns?

---

Satheesh

Answer 7

You are missing a clustered index on the table.

Try the below and see : Please mention the return datatype of the function getcharacters.

Also, right click on compute sclar and let us know the description.

select nleadid from temp_cc 
where tphone_cc2 = 6195551236

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 8

How does clustered index help here?

Here is the description requested. One thing surprising is why is output pointing to tPhone_CC1. I am loosing my mind...

Answer 9

It seems the optimizer is very picky about function, possibly because of null-ability. I suspect a flaw in the optimizer.

If you force the index, you see Bookmark Lookups, which is obviously incorrect, since both indexes are covering the query.

I did manage to get it to use the index. Thus my thoughts about null-ability. Replace your current declaration of tPhone_CC2 with this one:

[tPhone_CC2]  AS ISNULL([dbo].[getcharacters]([tPhone]),'') PERSISTED

---

Gert-Jan

Answer 10

Hi All,

I dont know why SQL was behaving strangely. I removed one of the computed columns from that table and created another table with computed column to test performance. Then it was behaving as expected that is using the index.

Thank you for your suggestions