Non use of persisted data
- by Dave Ballantyne
Working at a client site, that in itself is good to say, I ran into a set of circumstances that made me ponder, and appreciate, the optimizer engine a bit more. Working on optimizing a stored procedure, I found a piece of code similar to : select BillToAddressID, Rowguid, dbo.udfCleanGuid(rowguid) from sales.salesorderheaderwhere BillToAddressID = 985
A lovely scalar UDF was being used, in actuality it was used as part of the WHERE clause but simplified here. Normally I would use an inline table valued function here, but in this case it wasn't a good option.
So this seemed like a pretty good case to use a persisted column to improve performance.
The supporting index was already defined as
create index idxBill on sales.salesorderheader(BillToAddressID) include (rowguid)
and the function code is
Create Function udfCleanGuid(@GUID uniqueidentifier)returns varchar(255)with schemabindingasbegin Declare @RetStr varchar(255) Select @RetStr=CAST(@Guid as varchar(255)) Select @RetStr=REPLACE(@Retstr,'-','') return @RetStrend
Executing the Select statement produced a plan of :
Nothing surprising, a seek to find the data and compute scalar to execute the UDF.
Lets get optimizing and remove the UDF with a persisted column
Alter table sales.salesorderheaderadd CleanedGuid as dbo.udfCleanGuid(rowguid)PERSISTED
A subtle change to the SELECT statement…
select BillToAddressID,CleanedGuid from sales.salesorderheaderwhere BillToAddressID = 985
and our new optimized plan looks like…
Not a lot different from before! We are using persisted data on our table, where is the lookup to fetch it ? It didnt happen, it was recalculated. Looking at the properties of the relevant Compute Scalar would confirm this , but a more graphic example would be shown in the profiler SP:StatementCompleted event.
Why did the lookup happen ? Remember the index definition, it has included the original guid to avoid the lookup. The optimizer knows this column will be passed into the UDF, run through its logic and decided that to recalculate is cheaper than the lookup. That may or may not be the case in actuality, the optimizer has no idea of the real cost of a scalar udf. IMO the default cost of a scalar UDF should be seen as a lot higher than it is, since they are invariably higher.
Knowing this, how do we avoid the function call? Dropping the guid from the index is not an option, there may be other code reliant on it. We are left with only one real option, add the persisted column into the index.
drop index Sales.SalesOrderHeader.idxBillgocreate index idxBill on sales.salesorderheader(BillToAddressID) include (rowguid,cleanedguid)
Now if we repeat the statement
select BillToAddressID,CleanedGuid from sales.salesorderheaderwhere BillToAddressID = 985
We still have a compute scalar operator, but this time it wasnt used to recalculate the persisted data. This can be confirmed with profiler again.
The takeaway here is, just because you have persisted data dont automatically assumed that it is being used.
