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.

• sql
• sql-server
• stored-procedures
• user-defined-functions
• calculated-columns

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

Q: MS SQL Computed Column is slowing down performance...

A: Horse hockey ;)

 ... where @LName <> '' ...

Q: Can you say "full table scan"?

I'm not saying your function isn't expensive. But you've really got to make a more selective "where" clause before you point fingers...

IMHO...

SUGGESTION:

1. Query the data (get all relevant "Lname's" first)

2. Run your function on the result (only the selected "Lnames" - which, I presume, aren't every row in the entire view or table)

3. Do both operations (query-with-filter, then post-process) in your stored procedure