SQL Server Performance

Scalar/Table Function Performance Tuning

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Nick Young, Jun 29, 2011.

  1. Nick Young New Member

    Several weeks ago I set out to create a process that checks records for errors. The errors are predefined comparisons between the records and control data. There are currently 14 different error checks that are performed but more can be added at any time.

    The records that are checked typically meet a predefined set of requirements and number somewhere in the tens of thousands. My first thought was to do this using embedded stored procedures. The top level stored procedure preps the data to then be compared to the control data. The second stored procedure calculates all errors for all of the data and writes the errors to various error tables. Once this is complete, the parent stored procedure takes over again and moves data from specific tables to other tables based on the errors that were written.

    This process works well and takes a little over one minute to run. The problem is that it is not reusable at all. I would like to have the process changed so I can pass in a record Id and a table Id and have the process check for errors and move the data as appropriate.

    So that leads me to iteration 2. In this version I created a scalar function for each error type. I then created a parent table function that puts all of the values returned by the scalar functions into a table. I then have stored procedures that call the table function and write/move data based on what is returned.

    This works really well as I can pass a record Id and table Id to the stored procedure and it just handles it. This is very reusable and works great. However, it takes a good 30 minutes to run on about 20,000 records. This isn't at all feasible because no user is going to wait that long for anything.

    I understand the difference between the two processes in that the first option is looking at all of the data at one time whereas the second option has to look at one record at a time, iterating through line by line.

    So my question is, what should I do? Is there something anyone can suggest on how I can create a scalable, reusable solution without sacrificing performance?
  2. FrankKalis Moderator

    Welcome to the forum!

    Scalar function are notorious for killing performance. But I think we would have to see some code to recommend anything useful instead of just citing common places. :)
  3. Nick Young New Member

    Here is an excerpt from the table-valued function. You pass in a record id and a table id and it creates a table for that record's errors.

    Code:
    INSERT  INTO @Results
            ( Record_Id ,
              Error_Id
    
            )
            SELECT  Record_Id ,
                    1 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_01_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    2 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_02_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    3 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_03_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    7 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_07_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    9 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_09_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    10 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_10_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    11 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_11_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    12 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_12_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    13 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_13_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
            UNION
            SELECT  Record_Id ,
                    14 AS Error_Id
            FROM    Records_Table as rt
            WHERE  dbo.udf_Get_Error_Code_14_By_Record(Record_Id, 1) = 1
                    AND Record_Id = @Record_Id
  4. satya Moderator

    As referred Scalar functions are good to use them in limited space and when you get them used in frequent queries then it will cause performance to degrade exponentially. This depends on the number of rows in the scalar function is executed against and also the network connectivity between client 7 server.
    You can also find the server load using PERFMON and server side trace to look at the CPU and READS and DURATION columns. Increase you test size to include tests that take longer than a second, two seconds, five seconds. Also see the blog
    http://sqlblog.com/blogs/alexander_...8/05/23/reuse-your-code-with-cross-apply.aspx related to best use TVF and inline table functions for better performance andcontrol.
  5. Nick Young New Member

    So it sounds like I'd be better off converting these scalar functions to table functions and measuring the output at that point. I'll give that a try and see what happens.
  6. satya Moderator

    Yes you can, but make sure to follow the execution plan for the changed queries to see the best usage of indexes...for optimum performance.

Share This Page