Function running many times - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function running many times

Author

Topic

lisafern
Starting Member

2 Posts

Posted - 2008-05-28 : 17:28:23

Hello,

I see the following problem regarding a SQL function that returns a value.

We have the following query

SELECT c.Id_Customer, c.Name
FROM t_Customers c
WHERE c.Id_Status = fn_GetParameter('ID_Status.Active')

The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them.

We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value.

We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools).

Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated.

Thanks in advance,

Lisandro.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 17:37:15

Could you instead use a subquery?:

WHERE ... = (SELECT dbo.fn_GetParameter(...) FROM ...)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

lisafern
Starting Member

2 Posts

Posted - 2008-05-28 : 17:44:12

Tara:

Thanks for your quick suggestion. yes, subqueries may be a good approach, I'll test it as soon as I get back to work.

But I still wonder why SQL server does not make a more efficient use of the function. The great benefit of functions is to centralize coding and make simpler to use to everyone some logic that may be complicated. Introducing subqueries in the WHERE clause makes code harder to understand, and longer to write (we have some queries involving 5-10 tables that have almost 15 different conditions solved by functions)

Anyway, thanks, and is someone knows more in detail the logic behind that behavior I´ll be glad to learn it ;-)

quote:
Originally posted by tkizer

Could you instead use a subquery?:

WHERE ... = (SELECT dbo.fn_GetParameter(...) FROM ...)

Tara Kizer

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-28 : 17:53:15

I don't know the answer to your question but I'm not too surprised. Since that function could take as an argument a column value rather than a constant I guess sql figures it needs to be re-evaluated for each row.

Another approach could be to define a table valued function and then INNER JOIN to it
ie:


create function dbo.fn_GetParameter(@s varchar(50)) 
returns table 
as
return
select case 
       when @s = 'ID_Status.Active' then 1
       else -1
       end statusID
go

SELECT c.Id_Customer, c.Name
FROM t_Customers c
inner join dbo.fn_GetParameter('ID_Status.Active') as s
              on s.statusID = c.status

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources