March 17, 2008 at 9:13 pm
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.
March 17, 2008 at 10:03 pm
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?
March 17, 2008 at 10:35 pm
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
March 19, 2008 at 3:53 pm
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
March 20, 2008 at 9:24 pm
Won't memory be an issue since RETURN TABLE means all that data returned is cached, since the table itself is created in memory?
March 21, 2008 at 12:43 am
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?
March 25, 2008 at 12:55 pm
Thanks Matt.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply