How can we optimize a query having TABLE VALUED function in SELECT?

  • Hi All,

    This query is running slow due to a TABLE VALUED function in select clause. Definition of query and function is pasted below. Table structure, data definition not available. Anyone want to try this query.

    =============

    QUERY

    =============

    SELECT top 250000

    x1.wo_ref AS [z13031322175522447287ca90],

    sum(

    (

    select x.approved_ts_amount

    from dbo.woccsr_ts_amt(20,x3.wo_id,x3.cc_id, x3.gl_id )

    as x

    )

    ) AS [z13031322175522447289ca90]

    FROM

    dbo.wo AS x1 WITH (NOLOCK)

    INNER JOIN dbo.wocc AS x3 WITH (NOLOCK) ON x1.wo_id = x3.wo_id

    WHERE

    status != 17 and

    x1.b_code in ('GOOG')

    GROUP BY

    x1.wo_ref

    =============

    FUNCTION

    =============

    CREATE function [dbo].[woccsr_ts_amt] (@wo_id varchar(24),

    @cc_id varchar(24),

    @gl_id varchar(24))

    returns TABLE

    as

    RETURN

    select

    sum((case when nr.status in (1, 3, 44, 52) then

    idr.detail_amount + idr.crdb_adj_amount

    else

    0

    end)) as approved_ts_amount

    from

    dbo.pay_detail (nolock) as idr

    inner join dbo.ts (nolock) nr on nr.ts_id = idr.incurred_id

    where

    nr.wo_id =@wo_id

    and idr.cc_id = @cc_id

    and idr.gl_id = @gl_id

    Thanks.

  • T.Ashish (4/2/2013)


    Hi All,

    This query is running slow due to a TABLE VALUED function in select clause. Definition of query and function is pasted below. Table structure, data definition not available. Anyone want to try this query.

    Without something to work with this could be absolutely anything. There are at least few dozen or more possibilities. Many of those answers come from the ddl.

    I would warn you VERY strongly to stop using NOLOCK like you are. It is bad enough in specific queries but in a function that appears to be dealing with money you are asking for serious problems. You need to understand what this hint does and the dangers it can bring.

    Here just a few articles that explain this hint.

    http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

    http://blogs.msdn.com/b/sqlcat/archive/2007/02/01/previously-committed-rows-might-be-missed-if-nolock-hint-is-used.aspx%5B/url%5D

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

    http://sqlmag.com/sql-server/quaere-verum-clustered-index-scans-part-iii

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Your function takes three parameters, yet in your query, you pass it four. Do you not get an error when you run the query?

    We may not be able to help you with performance without DDL and sample data, but if you could post an execution plan, we may be able to spot something obvious.

    John

  • Can you post the actual execution plan for this please, as a .sqlplan attachment:

    SELECT TOP 250000

    [z13031322175522447287ca90] = x1.wo_ref,

    [z13031322175522447289ca90] = SUM(x.approved_ts_amount)

    FROM dbo.wo x1

    INNER JOIN dbo.wocc x3

    ON x1.wo_id = x3.wo_id

    CROSS APPLY (

    SELECT approved_ts_amount = SUM(idr.detail_amount + idr.crdb_adj_amount)

    FROM dbo.pay_detail idr

    INNER JOIN dbo.ts nr

    ON nr.ts_id = idr.incurred_id

    AND nr.[status] IN (1, 3, 44, 52) -- quicker

    WHERE nr.wo_id = x3.wo_id -- outer reference

    AND idr.cc_id = x3.cc_id -- outer reference

    AND idr.gl_id = x3.gl_id -- outer reference

    ) x

    WHERE [status] != 17 -- which table does this column belong to?

    AND x1.b_code IN ('GOOG')

    GROUP BY x1.wo_ref

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Something like this:

    SELECT top 250000

    x1.wo_ref AS [z13031322175522447287ca90],

    sum(wta.approved_ts_amount) AS [z13031322175522447289ca90]

    FROM

    dbo.wo AS x1

    INNER JOIN dbo.wocc AS x3

    ON x1.wo_id = x3.wo_id

    CROSS APPLY dbo.woccsr_ts_amt(20, x3.wo_id,x3.cc_id, x3.gl_id) wta(approved_ts_amount)

    WHERE

    status != 17 and

    x1.b_code in ('GOOG')

    GROUP BY

    x1.wo_ref

  • And this rewrite of the function should work like the one Chris provided in-line in his code.

    CREATE function [dbo].[woccsr_ts_amt] (

    @wo_id varchar(24),

    @cc_id varchar(24),

    @gl_id varchar(24))

    returns TABLE

    as

    RETURN

    select

    sum(idr.detail_amount + idr.crdb_adj_amount) as approved_ts_amount

    from

    dbo.pay_detail as idr

    inner join dbo.ts nr

    on nr.ts_id = idr.incurred_id

    where

    nr.wo_id = @wo_id

    and idr.cc_id = @cc_id

    and idr.gl_id = @gl_id

    and nr.status in (1, 3, 44, 52);

  • refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (4/3/2013)


    refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.

    Actually, the TVF being used is an in-line TVF.

  • Lynn Pettis (4/3/2013)


    TheSQLGuru (4/3/2013)


    refactor your TVF to be an INLINE TVF and use that. Also push that SELECT ...(SELECT...) out like previous poster did.

    Actually, the TVF being used is an in-line TVF.

    That was a test Lynn - you PASSED!! :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (4/3/2013)


    refactor your TVF to be an INLINE TVF.

    I think it is an iTVF

    Also push that SELECT ...(SELECT...) out like previous poster did.

    I don't think that matters much, if at all.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 10 posts - 1 through 9 (of 9 total)

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