Table-Value Functions in SQL 2005

17 Feb 2006 6:01 PM

Paul Rausch

I have a multi-statement table function that works great in SQL 2000, but in
SQL 2005 it will work great for awhile then after you call that function a
bunch of times it goes from taking 2 seconds to run to 60 seconds to run
with the same exact paramenters. This only happens through ADO.NET
ExecuteReader method, if I trace the call and run it manually it takes 2
seconds? But if I re-run the function script(ALTER FUNCTION) even with no
changes it runs fast again for awhile then it will eventually go back to
taking 60 seconds. I'm kind of stumped, only thing I can think of is some
sort of caching of the function call themselves?

19 Feb 2006 4:00 PM

Erland Sommarskog

Paul Rausch (prau***@inetium.com) writes:

> I have a multi-statement table function that works great in SQL 2000,
> but in SQL 2005 it will work great for awhile then after you call that
> function a bunch of times it goes from taking 2 seconds to run to 60
> seconds to run with the same exact paramenters. This only happens
> through ADO.NET ExecuteReader method, if I trace the call and run it
> manually it takes 2 seconds? But if I re-run the function script(ALTER
> FUNCTION) even with no changes it runs fast again for awhile then it
> will eventually go back to taking 60 seconds. I'm kind of stumped, only
> thing I can think of is some sort of caching of the function call
> themselves?

Very difficult to tell what is going from a distance. Here are two things
I would try though:

1) When running from QA/Mgmt Studio, first issue SET ARITHBORT OFF, to
   have the same set of SET options as when you have from ADO .Net. If
   my theory is right, you know get the slow performance.

2) Examine whether the query plan is the same when you have 2-second
   performance and when you have 60-seconds performance.

It could help if you posted the code to the UDF.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx