0

Possible Duplicate:
Why does the Execution Plan include a user-defined function call for a computed column that is persisted?

I've added a persisted computed column to a table. 

ALTER TABLE guest
  ADD FullName AS
  dbo.complicatedFunction(ISNULL(FirstName, N'') + ISNULL(LastName, '')) PERSISTED
GO

I also added a non-clustered index on that field.

When I do a select from that table and include the column into a where statement it takes very long to execute.

I tried replacing the persisted column with the expression directly and it takes the same amount of time to execute, which led me to believe that FullName field is being recalculated for every row when I try to select something from that table.

How can I avoid that recalculation and get the query to use the index?

Execution plan

Community
  • 1
  • 1
Fedor Hajdu
  • 4,657
  • 3
  • 32
  • 50
  • How are you using the column in your WHERE statement? Is there a LIKE? – MartW Jun 22 '12 at 11:22
  • @CodeByMoonlight It's the same if I use LIKE, CHARINDEX(Split.s, FullName)> 0, or even FullName IS NOT NULL. Any of those will take the same amount of time to run. – Fedor Hajdu Jun 22 '12 at 11:24
  • Try make a view with the column instead of a function – Jester Jun 22 '12 at 11:24
  • 3
    [Related/Duplicate](http://stackoverflow.com/q/5998217/73226) – Martin Smith Jun 22 '12 at 11:31
  • 1
    Please post the execution plans. – Martin Smith Jun 22 '12 at 11:38
  • I learn something new here every day :) – MartW Jun 22 '12 at 11:44
  • @MartinSmith I've posted plans. But it is a duplicate question and it's answered very well on the link you posted. – Fedor Hajdu Jun 22 '12 at 11:51
  • The plans you have posted look to me as though @CodeByMoonlight's suspicion may well have been correct though! Can you right click the clustered index scan in the execution plan. View the properties and look at the output columns and see if `FullName` is being output? if so it is using the persisted value and the issue then becomes one of it not using your NCI because it isn't covering and selectivity issues in the `=` case (or maybe because an implicit datatype conversion makes the query unsargable) – Martin Smith Jun 22 '12 at 11:56
  • @MartinSmith FullName is not in the Output List in those queries. It is added at the Compute Scalar step. – Fedor Hajdu Jun 22 '12 at 12:04
  • @MartinSmith The question you linked to solved my problem (I removed function call and moved the code of the function into the definition of the computed column and it works great now). Thank you very much. – Fedor Hajdu Jun 22 '12 at 12:25
  • i think you find here the answer [http://stackoverflow.com/questions/5998217/why-does-the-execution-plan-include-a-user-defined-function-call-for-a-computed][1] [1]: http://stackoverflow.com/questions/5998217/why-does-the-execution-plan-include-a-user-defined-function-call-for-a-computed –  Jul 01 '12 at 21:01

2 Answers2

0

The reason is that the query optimizer does not do a very good job at costing user-defined functions. It decides, in some cases, that it would be cheaper to completely re-evaluate the function for each row, rather than incur the disk reads that might be necessary otherwise.

Why does the Execution Plan include a user-defined function call for a computed column that is persisted?

Community
  • 1
  • 1
0

Martin Smith posted a link to a great explanation on why this is happening, and after reading through it, I replaced a call to the UDF with it's body and it solved my problem.

Fedor Hajdu
  • 4,657
  • 3
  • 32
  • 50