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 |
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
 Posted - 2006-10-08 : 18:59:39
|
| Hi all, Our database contains a global settings table that has one record with about 10 columns in it, one of which is the current processing date. Many of our queries need to dig data from the transaction tables based on the current processing date. We have a simple UDF that's called GetProcessingDate that just gets this date and we can use this to filter in our queries. My question is this: does SQL Server execute this function once and then use the result as a static value for the execution of the query? The function has no input parameters as you might expect. The other option is to always link to the settings table but this can get messy, particularly for some applications where we just want to return the processing date in the results set (ie not used in a filter). I've run an execution plan over both options and the UDF looks cleaner but I have no way of knowing how many times it will be hit.Cheers,Tim |
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2006-10-08 : 22:30:32
|
| If you are only using the function for one record, with no inputs, I would just create a variable and assign the date using a select instead of a udf. |
 |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-10-08 : 23:02:21
|
| Yes, but that doesn't help when you want to include it in a view. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-09 : 06:02:25
|
| >> does SQL Server execute this function once and then use the result as a static value for the execution of the query?Depends on the query. it'll probably be ok but look at the query plan to check.Why not use a view to get the values instead of a function.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-10-09 : 20:01:32
|
Be careful with functions.In some circumstances they can cause serious performance issues.In many case it's not a problem, and the query plan looks good, with a nice "Compute Scalar",but in effect, the "Compute Scalar" has to be done for every row of the input set.And the UDF seem to muck up the Query Optimizer. It seems not to be able to optimize very efficiently with UDF's,just because it does not know what's inside the UDF. (see IsDeterministic property)./* does not get more global and deterministic than this */create function dbo.fnGlobal()returns int with schemabinding as begin return( select 1 )endgo-- and for comparison a global tablecreate table tGlobal(g int primary key(g) check(g=1))insert tGlobal(g) values(1)-- now compare plans/* Compare execution plans, VERY NASTY SORT DISTINCT in my plan for the DISTINCT query*/select name,dbo.fnGlobal() from sysobjects group by nameselect distinct name,dbo.fnGlobal() from sysobjects/* Completely different plans from the UDF plans*/select name,g from sysobjects cross join tGlobal group by name,gselect distinct name,g from sysobjects cross join tGlobal rockmoose |
|
|
|
|
|
|
|
|
To UDF or not to UDF?