SQLTeam.com | Weblogs | Forums

Table valued function performance issue


#1

Hi

I have the data as below in the table

Key Parentid VALUE
1 0 US
2 1 USA
3 2 Albama
4 3 Atlanta
5 2 Texas
6 3 Dallas

I using the table valued function to display the KEYS, as below

when Input value of Key is 6

Then displaying the output as below

Region Country State Location
1 2 5 6

And if the input value 3

then displaying the output as below

Region Country State Location
1 2 3 NULL

I am using this function in the view to fetch the values, which degrades the performance of the view. Inside the view we are calling function more than 200000 times which will be increase if more data comes in.

Is there any other smarter way to increase the performance of this function. Please advise.

Thanks in advance.


#2

Please post the relevant code.

I avoid table functions like the plague.


#3

Hi,

Please find the table structure and code below

KEY DOMAINID PARENTID VALUE
1 0 1 Region
2 0 1 Country
3 0 1 State_Province
4 0 1 Localities
5 1 0 US
6 2 5 USA
7 3 6 Albama
8 4 7 Atlanta
9 3 6 Texas
10 4 7 Dallas

CREATE FUNCTION SCHEMA.FIELD_SPLIT
(
@key INT
)
RETURNS @Split_tab TABLE
(
Region INT,
country INT,
State INT,
Location INT
)
AS
BEGIN

DECLARE    @location    INT    = NULL
DECLARE    @state        INT    = NULL
DECLARE    @country     INT    = NULL
DECLARE    @region     INT    = NULL

IF @key IS NOT NULL
BEGIN

-- If metadata values exists upto locality
IF EXISTS ( SELECT    'X' 
            FROM    SCHEMA.JURISDICTION REF1
            JOIN    SCHEMA.JURISDICTION REF2
            ON        REF1.DOMAINID    =    REF2.KEY
            WHERE    REF1.KEY        =    @key
            AND        REF2.VALUE    =    'LOCALITIES' )
BEGIN
    SELECT    @location        =    @key

    SELECT    @state        =    PARENTID
    FROM    SCHEMA.JURISDICTION 
    WHERE    KEY    =    @key

    SELECT    @country        =    PARENTID
    FROM    SCHEMA.JURISDICTION 
    WHERE    KEY    =    @state

    SELECT    @region        =    PARENTID
    FROM    SCHEMA.JURISDICTION 
    WHERE    KEY    =    @country

END

-- If metadata values exists upto State
ELSE IF EXISTS ( SELECT    'X' 
                    FROM    SCHEMA.JURISDICTION  REF1
                    JOIN    SCHEMA.JURISDICTION  REF2
                    ON        REF1.DOMAINID    =    REF2.KEY
                    WHERE    REF1.KEY        =    @key
                    AND    REF2.VALUE    =    'STATE_PROVINCE' )
    BEGIN
        SELECT    @location        =    NULL

        SELECT    @state        =    @key
        
        SELECT    @country        =    PARENTID
        FROM    SCHEMA.JURISDICTION 
        WHERE    KEY    =    @state

        SELECT    @region        =    PARENTID
        FROM    SCHEMA.JURISDICTION 
        WHERE    KEY    =    @country

    END

-- If metadata values exists upto Country
ELSE IF EXISTS ( SELECT    'X' 
                    FROM    SCHEMA.JURISDICTION  REF1
                    JOIN    SCHEMA.JURISDICTION  REF2
                    ON        REF1.DOMAINID    =    REF2.KEY
                    WHERE    REF1.KEY        =    @key
                    AND    REF2.VALUE    =    'COUNTRY' )
    BEGIN
        SELECT    @location        =    NULL

        SELECT    @state        =    NULL
        
        SELECT    @country        =    @key

        SELECT    @region        =    PARENTID
        FROM    SCHEMA.JURISDICTION 
        WHERE    KEY    =    @country

    END

-- If metadata values exists upto Country
ELSE IF    EXISTS ( SELECT    'X' 
                    FROM    SCHEMA.JURISDICTION  REF1
                    JOIN    SCHEMA.JURISDICTION  REF2
                    ON        REF1.DOMAINID    =    REF2.KEY
                    WHERE    REF1.KEY        =    @key
                    AND    REF2.VALUE    =    'REGIONS' )
    BEGIN
        SELECT    @location        =    NULL

        SELECT    @state        =    NULL
        
        SELECT    @country        =    NULL

        SELECT    @region        =    @key

    END
        
    INSERT INTO @Split_tab
        SELECT    @region     ,    @country      ,    @state    ,  @location
                            
END        
RETURN

END
GO

Kindly Advise how to Performance tuning this code or any other suggestions.


#4

Ooooo.... you shouldn't avoid them. There are two types of table valued functions. One is an "mTVF" (Multi-statement Table Value Function, like what the OP wrote), which you should absolutely avoid like the plague. The other is an "iTVF" (Inline Table Value Function) which works very much like a CTE in that it becomes a part the execution plan as if its functionality was actually written into the code... and used as an "iSF" (Inline Scalar Function) to replace typical scalar UDFs, it can be an absolute god-send of performance, readability, and simplicity of code.

Please see the following article for an introduction to the latter fact above. If you've not seen this type of thing before, you'll be amazed at the performance.
http://www.sqlservercentral.com/articles/T-SQL/91724/


#5

Ok, first of all, when you want help, posting data as just plain ol' text doesn't help us help you . You need to make the data "readily consumable" so that we can provide you with tested code. Sure, it only takes a couple of minutes to change your text into actual test data but a lot of us answer a lot of posts on a lot of forums and anything you can do to make it easier will mean that your post gets better attention faster.

Here's one of many ways you could have posted the data to make our lives a bit easier especially for the somewhat detailed question you asked. You might also want to break out a map and find out where Dallas is before a whole lot of Texans come looking for ya. :wink:

--===== Do this in a nice, safe place that everyone has
    USE tempdb
;
--===== If the Test Table already exists,
     -- drop it to make reruns in SSMS easier.
     IF OBJECT_ID('dbo.Locations','U') IS NOT NULL
        DROP TABLE dbo.Locations
;
--===== Create and populate the test table on-the-fly.
 SELECT *
   INTO dbo.Locations
   FROM (
 SELECT  1,0,1,'Region'         UNION ALL
 SELECT  2,0,1,'Country'        UNION ALL
 SELECT  3,0,1,'State_Province' UNION ALL
 SELECT  4,0,1,'Localities'     UNION ALL
 SELECT  5,1,0,'US'             UNION ALL
 SELECT  6,2,5,'USA'            UNION ALL
 SELECT  7,3,6,'Albama'         UNION ALL
 SELECT  8,4,7,'Atlanta'        UNION ALL
 SELECT  9,3,6,'Texas'          UNION ALL
 SELECT 10,4,7,'Dallas' --BWAA-HAAA!!! Wait 'til the folks in Dallas find out you put them in 'Bama!!! ;-)
) testdata ([Key],DomainID, ParentID, Value)
;

Next, comes the function. Notice that it does NOT have a table definition and it does NOT have a BEGIN in it. Those are the traits of a high performance iTVF instead of like the mTVF you wrote. Also note the use of an rCTE (Recursive CTE) in this case. Those are frequently slower than a WHILE loop but we had to use one to keep from using an mTVF. If you really want this to be fast, then we should add the Bowers of "Nested Sets" to your table and use those for the hierarchical traversal instead of recalculating over and over that which will only change once in a Blue Moon.

Here's the function. It's got usage examples in the header.

 CREATE FUNCTION dbo.GetLocations
/******************************************************************************
 Purpose:
 Given a [Key] from the "Locations" table, return the [Key] for the Region,
 Country, State_Province, and Locality. If the entry point into the hierarchy
 is above the Locality level, return nulls below the entry point.

 Usage Examples:

--===== Single value example.
DECLARE @pKeyToFind INT;
 SELECT @pKeyToFind = 10;

 SELECT KeyToFind = @pKeyToFind, * FROM dbo.GetLocations(@pKeyToFind)
;

--===== Table example
 SELECT KeyToFind = st.SomeKey
        ,ca.*
   FROM dbo.SomeTable st
  CROSS APPLY dbo.GetLocations(st.SomeKey) ca
;

 Rev 00 - Jeff Moden - 22 Dec 2015
        - Reference: 
http://forums.sqlteam.com/t/table-valued-function-performance-issue/4552
******************************************************************************/
--===== Define the IO for this itVF function.
        (@pKeyToFind INT)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN WITH
cteHierarchyUpline AS
(--==== Get key row at the bottom of the upline
 SELECT [Key], DomainID, ParentID, Value
   FROM dbo.Locations
  WHERE [KEY] = @pKeyToFind
  UNION ALL -- And now traverse the upline
 SELECT tbl.[Key], tbl.DomainID, tbl.ParentID, tbl.Value
   FROM cteHierarchyUpline cte
   JOIN dbo.Locations tbl
     ON tbl.[Key] = cte.ParentID
)--==== This CROSSTAB creates the output you want
 SELECT  Region         = MAX(CASE WHEN DomainID = 1 THEN [Key] ELSE NULL END)
        ,Country        = MAX(CASE WHEN DomainID = 2 THEN [Key] ELSE NULL END)
        ,State_Province = MAX(CASE WHEN DomainID = 3 THEN [Key] ELSE NULL END)
        ,Locality       = MAX(CASE WHEN DomainID = 4 THEN [Key] ELSE NULL END)
   FROM cteHierarchyUpline
;
GO

The other thing to notice is that, if your hierarchy is structured correctly, there will always be only one node per level in the upline search, which makes this perfect for a CROSSTAB (the MAX/CASE stuff), which is usually faster than PIVOT.

If you have any additional questions on this, please send beer. I already have enough pretzels. :wink:


#6

Did that work for you or not?


#7

Hi Jeff,

Yes. It works for me, but I couldn't notice the performance improvement in my environment after using this function.


#8

How did you try to measure the Before'n'After?


#9

By executing the view which calls this function with the new and old function. For your information, I am very new to performance tuning.


#10

Understood. Unless you used SQL Profiler, Extended Events, or (in this case, would be ok) SET STATISTICS, you haven't really measured anything. I guess my question would be does it seem to be fast enough for you? If not, we might have to look into the view (didn't really understand that you were looking at a view to start with).


#11

I have the data as below in the table<<

Why did you fail to post DDL for this? Are you rude? Or ignorant?

The term key is metadata; it is how something is used, not what it is by its nature. What you are doing is building a chain of non-relational fake pointers. Your mindset is still in 1966.

Please Google "nested sets model" and see how a geographical hierarchy is properly built in SQL.

I using the table valued function to display the KEYS, as below <<

Every thing you are doing is wrong. Your entire approach is wrong.

Even worse, UDF coding cannot be optimized, and cannot be ported.

I am using this function in the view to fetch the values, which degrades the performance of the view. <<

Yep!

Use a nested sets model instead. you will gain 1-3 orders of magnitude in performance..


#12

That's a load of hooie. You should Google "Inline Table Valued Functions". As to whether or not they're portable or not, most of what's useful isn't portable anyway so use what you've got.


#13

And, yes, I absolutely do agree. Nested sets would be the way to go if you maintained the Adjacency List and let the Nested Sets happen.