Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLMark
Starting Member
18 Posts |
 Posted - 2009-09-24 : 09:34:38
|
| Hi all,I have a simple SQL Server 2005 performance question.In my db I have created a Table-Valued User-Defined Functions (about 100 rows of code that fishing data from tables...) and I call it inside my application every 10 seconds.Function execution is quite fast (about 2, 3 seconds.) but mostly, its return value is the same since no data has been changed in the tables.Does SQL Server leave in a sort of "cache memory" the previous value avoiding executing the function again or should I implement a similar optimization? Is this the right way? Any suggestion?Thanks.Mark |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-09-24 : 14:05:03
|
| The query plan is cached, but not the results. If it were a single table, you could PIN that in memory, but I do not believe the same works for a TVF.But my question would be why are you executing this function every 10 seconds. What are you doing with the output? Why not just execute it on-demand when you need the output?--------------------------------------------Brand yourself at EmeraldCityDomains.com |
 |
|
|
SQLMark
Starting Member
18 Posts |
Posted - 2009-09-25 : 03:27:12
|
| HI AjarnMark,yes ... your question is right: the better way will be to execute the TVF only when is needed.But the problem is that I really don't know when is needed; in other words, I'm not able to know when data has been changed in tables.Image my database that contains tables with informations.This info must be processed by the TVF (invoked by a Windows service) in order to show the function's return value on a TV monitor.Info in tables are changed by a lot of users, and it is not said that one modification must have impact to the function result (this because informations are related to its monitor and must respect some rules like the day in calendar, the hour ...).So, you can understand that the only way to know if informations to show has been changed is to launch function every n minutes.Schematically: result = F(x) !! changes in data tables !! (Now, how to know if result is always the same executing F(x) ?)Hope I've been a little clear than before.Thanks again,Mark |
|
|
|
|
|
|
|
|
SQL Server 2005 performance question