SQL Server Performance Forum – Threads Archive

Performance of TableValuedFunctions

Hi,
I have a table valued Functions which return records in 110 milliseconds while if i run the query with the same input parameters as the table valued function it return in 46 milliseconds. I need to know why there is so much difference. I wanted to know what should I do in the table valued function to return the dataset in 46 milliseconds. Since I am using a lot of Table valued function for security access and it used everywhere, am I looking for a consistent performance. Any Help or information would be appreciated. Regards,
santosh Verma

Please post your code. Looks like a case of parameter sniffing. See if this applies:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611 —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

Did you clear cache, checkpoint, etc. between each run? That could have an impact on the execution times.

http://www.sql-server-performance.com/sj_detect_fragmentation.asp fyi on the table fragmentation.

quote:There are 2 parts in Table-valued functions (TVF) execution firstly, the attributes applied to the method must fully declare the structure of the table that is returned. Second, there are two methods involved. The first returns an enumerable object instead of the actual result of the function. The second method is passed the enumerated objects to populate the fields of each row. Each value retrieved via the enumerator should correspond with one row of the resultset.

http://blogs.msdn.com/sqlclr/archive/2005/05/05/415034.aspx fyi.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>