Query with many columns and inline functions

  • I have a legacy chunk of code that has about 90 columns returning, with an inline scaler function that does a lookup to another table on about 70 of them.

    Is it faster to remove the inline function and replace it with a direct join to the table the function queries?

    I know its poorly designed, but I am trying to make the best of it and tune it as much as possible.

  • The , perhaps over simple, response is yes , removing scalar functions and joining will improve perfomance.



    Clear Sky SQL
    My Blog[/url]

  • I should have mentioned that all 70 of these columns look up a value from the *same table*.

    I tried replacing the inline calls with self-joins (yep for all 70 of them) and that actually made things worse.

  • Some of that will also depend on what else the function is doing. Is it just a simple lookup or is it performing calculations? I would recommend looking at the logical reads before changing the query and after changing the query.

    Can you post your code?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Some of that will also depend on what else the function is doing. Is it just a simple lookup or is it performing calculations? I would recommend looking at the logical reads before changing the query and after changing the query.

    Can you post your code?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • The function is of the form:

    create FUNCTION [dbo].[fn_GetDescription]

    (

    @pID INT

    )

    RETURNS nVarchar(100)

    AS

    BEGIN

    -- Declare the return variable here

    DECLARE @Descrip nVarchar(1000)

    -- Add the T-SQL statements to compute the return value here

    SELECT @Descrip = Descrip from tblDescriptions where ID = @pID;

    -- Return the result of the function

    RETURN ISNULL(@Descrip,NULL)

    END

    The report code was

    SELECT ID, [dbo].[fn_GetDescription](OrderType), [dbo].[fn_GetDescription](OrderStatus), [dbo].[fn_GetDescription](OrderProductStatus)....etc etc.

    The descriptions table holds lookup descriptions for a lot of order and product status type columns.

    After testing stuff out, the function call seems to perform better than a lot of joins straight to the Descriptions table.

  • Take a look here[/url] for help on posting performance problems. Help us to help you



    Clear Sky SQL
    My Blog[/url]

  • Is it looking up a value in the same row??

  • It looks to me like they've created a universal 'Descriptions' table that gets linked to every other table in the system. Would this be an accurate statement?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • John Rowan (7/8/2009)


    It looks to me like they've created a universal 'Descriptions' table that gets linked to every other table in the system. Would this be an accurate statement?

    Yup exactly like as specified http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/



    Clear Sky SQL
    My Blog[/url]

  • Yep, that's what we got, along with a bunch of big 'ol wide flat file tables.

    Its a mess, trying to make it do what it can until we can gut it and redo it.

    Turns out for some reason, calling the function is faster than self-joining to the 'descriptions' table 70 times.

    Not what I expected...but i'll go with it for now.

  • Are your statistics up to date ?. Just as a thought I would guess what is happening is that an incorrect query plan is being generated for the version with joins.



    Clear Sky SQL
    My Blog[/url]

  • I have a similar issue. 'Reference Values' table comprising (amongst its 27 fields)

    RFVAL_REFNO (ID),

    RFVDM_CODE (Reference Type),

    Description,

    Main)Code, Start_DTTM, End_DTTM, etc.

    8012 rows, 830 Reference Types.

    The first thing that I did when I found this (apart from creating a Primary Key!!!) was to create specific views, and use these views in my joins:

    CREATE VIEW [dbo].[vw_ref_ethnic_group]

    AS

    SELECT RFVAL_REFNO

    , DESCRIPTION

    , MAIN_CODE

    , RFTYP_CODE

    , START_DTTM

    , END_DTTM

    FROM dbo.REFERENCE_VALUES

    WHERE (RFVDM_CODE = 'ETHGR')

    I'm trusting SQL Server to optimize, despite using a view and hope that the code looks more intelligible

  • Hi Ian,

    I would of taken in the 'other way' and had a view to union smaller tables( for compatability only until time permitted to do all the joins correctly). Obviously a unique 'compatibility' id will probably be needed on the Colours and Gender tables

    Take a look at what happens to the query plans if you specify a type.

    Create Table Colours

    (

    ColourId integer primary key,

    ColourCode varchar(20),

    )

    go

    Create Table Gender

    (

    GenderId integer primary key,

    GenderCode varchar(20),

    )

    go

    insert into Colours values(1,'Red')

    insert into Colours values(2,'Blue')

    insert into Gender values(1,'Male')

    insert into Gender values(2,'Female')

    go

    Create View MasterView

    as

    Select colourId as Id,

    ColourCode as Code,

    'Colour' As type

    from colours

    union

    Select GenderId as Id,

    GenderCode as Code,

    'Gender' as type

    from Gender

    select * from MasterView where type = 'Colour'

    select * from MasterView where type = 'Gender' and Id = 1

    select * from MasterView where Id = 1



    Clear Sky SQL
    My Blog[/url]

  • I would suggest a similar approach to Dave's last post. If your Descriptions table has some sort of identifier column that allows you to group Descriptions into types, I would create a Description table for each type and move the descriptions into these normalized tables. Use these to join to inside your query and create a view called Descriptions that UNIONs each of the smaller Descriptions tables together so you are not breaking other parts of the application that happen to be using the universal Descriptions table. It sounds to me like you've got other normalization problems to deal with as well so using interim views may become your friend as you phase your schema changes into your database.

    Let us know how it goes. With 70 joins, I'm curious to see if breaking the universal table into smaller, normalized tables will help.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply