Inline Table-Valued Functions

  • Hi,

    When creating functions that return tables and the function definition makes use of a number of joins (outer and inner), can it be the cause of poor performance, memory related? The stored procedure that invokes these functions is being used concurrently by approximately 300 users passing different parameter values.

    Would like some information about memory utilization when using UDFs that return tables when compared to using alternatives like views or temp tables.

    Thanks in advance.

  • unfortunately - yes they can be. They're not terrible at times if the output is small - but since TVF's essentially are materialized as table variables - they can't make use of statistics, so they tend to be rather inefficient in joins.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hi,

    Join will not create any problem if u have proper indexes on tables..... And also if posible then use "with (NO LOCK)" on table at the time of selecting records from tables.....

    regards

    Digvijay Singh

  • Memory usage isn't the big problem . . . it is winding up with nested loop query plans accessing huge numbers of records that will kill performance.

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

  • Won't memory be an issue since RETURN TABLE means all that data returned is cached, since the table itself is created in memory?

  • It returns a table variable, so the table variable will be materialized to RAM if there's enough room and the table isn't too large. If it IS large, then it will likely be materialized in TempDB.

    Again - maybe one index and no statistics almost always leads to bad exec plans. (Remember - per BOL - "Table variables may only have one index created as part of a primary key declaration....Table variables can't leverage column statistics like regular tables".)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks Matt.

Viewing 7 posts - 1 through 6 (of 6 total)

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