SQL Server computed column is slowing down performance on a simple select statement

Question

Background:

Previously, my company was using a user-defined function to html encode some data in a where clause of a stored procedure. Example below:

DECLARE @LName --HTML encoded last name as input parameter from user

SELECT * 
  FROM (SELECT LName
          FROM SomeView xtra
         WHERE (( @LName <> '' 
              AND dbo.EncodingFunction(dbo.DecodingFunction(xtra.LName)) = @LName) 
           OR @Lname=''))

I simplified this for clarity sake.

The problem is, when the stored procedure with this query was called 45 times in quick succession, the average performance on a table with 62,000 records was about 85 seconds. When I removed the UDF, the performance improved to just over 1 second to run the sproc 45 times.

So, we consulted and decided on a solution that included a computed column in the table accessed by the view, SomeView. The computed column was written into the table definition like this:

[LNameComputedColumn] AS (dbo.EncodingFunction(dbo.DecodingFunction([LName])))

I then ran a process that updated the table and automatically populated that computed column for all 62,000 records. Then I changed the stored procedure query to the following:

DECLARE @LName --HTML encoded last name as input parameter from user

SELECT * FROM
      (SELECT LNameComputedColumn
      FROM SomeView xtra
      WHERE  (( @LName <> '' AND xtra.LNameComputedColumn=@LName) OR @Lname='')

When I ran that stored procedure, the average run time for 45 executions increased to about 90 seconds. My change actually made the problem worse!

What am I doing wrong? Is there a way to improve the performance?

As a side note, we are currently using SQL Server 2000 and are planning to upgrade to 2008 R2 very soon, but all code must work in SQL Server 2000.

Answer 1

Adding a computed creates a virtual column, still computed at runtime for every row selected. What you want is a computed persisted column, which is computed at insert time and stored physically in the table:

[LNameComputedColumn] 
   AS (dbo.EncodingFunction(dbo.DecodingFunction([LName]))) PERSISTED