Clever Geek Handbook

SQL Server computed column slows down performance when using simple select statement

Background:

Previously, my company used a custom function to html some data in a where clause of a stored procedure. See 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've simplified this for the sake of clarity.

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

So we consulted and decide on solutions that include a computed column in the table, which is accessed appearance SomeView

. The calculated column was written to the table definition as follows:

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

I then ran a process that updated the table and automatically populated that calculated 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 this stored procedure, the average execution 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 performance?

As a side note, we are currently using SQL Server 2000 and plan to migrate to 2008 R2 shortly, but all code should work in SQL Server 2000.

+3


source to share


2 answers


Adding a calculated creates a virtual column that is still calculated at run time for each selected row. What you need is a computed persist column that is computed at insert time and physically stored in the table:



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


source


Q: MS SQL Computed Column slows down performance ...

A: horse hockey;)

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

Q: Can you say "full table scan"?



I am not saying that your function is not expensive. But you really need to make a more selective "where" clause before you point your fingers ...

IMHO ...

SUGGESTION:

  • Request data (get all matching "Lnames" first)

  • Run your function as a result (only selected "Lnames" - which I guess not every row in the entire view or table)

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

+3


source






More articles:


All Articles