Skip to main content
D
dash13cbg13
Participant
October 12, 2009
Question

slow sql 2005 UDF query

  • October 12, 2009
  • 1 reply
  • 1044 views

we are using CF 8 and SQL 2005.

We have a SQL UDF that returns table results which takes 6 or 7 seconds when called through CF. When we take the query that CF ran and run it from SQL Management Studio the UDF returns the results in < 1 second.

The really weird thing is when we recompile the UDF CF can get results in < 1 second. But after a few minutes it reverts back to the 6 or 7 seconds.

Anybody have any thoughts on what we can do about this? Thanks in advance.

This topic has been closed for replies.

1 reply

ilssac
ilssac
Inspiring
October 12, 2009

Other places data bottle necks can occure between a database table and a web page.

1) Network between database server and applicaiton server.

2) Network between applicaiton server and web server (these are not always the same machine).

3) Netwrk between web server and client server, including any proxies or waystops in between.

4) Is there always enough memory for the task on the database server,

5) On the application server,

6) On the web serve

7) On the client server

8) Does your code cause exesive use of memory or dataclashes in the driver, or server threads or on the database?

But since we have no idea what this UDF does, or how it was written or how it has been used, I really can not advise much.

D
dash13cbg13Author
Participant
October 12, 2009

So nothing comes to mind that a recompile of a UDF would cause the UDF to speed up, but later will slow down.

ilssac
ilssac
Inspiring
October 12, 2009

Well, yeah, as I alluded to if we could see the code we could advise.

But if you insist on the generic then that is the usual sympton of code that ties up threads and|or memroy.  So the first run of the code if likety fast, then as the code is run more and more times its preformance deteriates.

But that is really hard to diagnose without seeing anything.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices