Using Scalar Function Instead of Joins on Lookup Tables

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 2000 Forums

SQL Server Development (2000)

Using Scalar Function Instead of Joins on Lookup Tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-15 : 07:20:05

Jeff writes "I'm interested if using user-defined functions to return values
from lookup tables has been a better optimization instead of using a join on the table.
You can assume that these example tables have RI applied.

Tables:
Ex: Employee(EmployeeID Int
,FirstName Char(10)
,LastName Char(10))

WorkSchedule(EmployeeID Int
,DateWorked DateTime
,Hours Int)

Scenario 1:
Select EmployeeID
,DateWorked
,Hours
,LastName
,FirstName
From EmployeeTable a
Inner Join WorkSchedule b
On a.EmployeeID = b.EmployeeID

Scenario 2:
Create Function fn_GetFirstName(@EmployeeID)
Return Char(10)
Begin
Declare @FirstName Char(10)
Select @FirstName = FirstName
From Employee
Where EmployeeID = @EmployeeID

Return @FirstName
End

Create Function fn_GetLastName(@EmployeeID)
Return Char(10)
Begin
Declare @LastName Char(10)
Select @LastName = FirstName
From Employee
Where EmployeeID = @EmployeeID

Return @LastName
End

Select EmployeeID
,DateWorked
,Hours
,dbo.fn_GetLastName(EmployeeID)
,dbo.fn_GetFirstName(EmployeeID)
From WorkSchedule

Any ideas? suggestions?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-15 : 07:48:08

I'm not sure, in this case, that the UDF will offer performance improvements. You should test both methods (and let us know what you found).

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-15 : 09:02:29

I would expect a JOIN to be faster ... but definitely test it out and let us know.

A JOIN seems is a lot more direct -- get two 'streams' of data, line them up by a common field, and then return the data matching them up (that's not very precise, but the basic idea).

A UDF would require that each row would invoke a function, and then that function would in turn access another table to return 1 row, and then return a value from that row back to the caller of the function. This would be done for every row, even if 1000 calls in a row result in the same value being returned. (but then again, maybe not -- UDF's must be deterministic so maybe SQL is smart enough not to call the function over and over if the arguments are the same ... who knows !?)

Just my guess... but definitely let us know the results. But I think the whole point of a RDMS is that you DON'T use functions like that, but rather joins ....

- Jeff

Edited by - jsmith8858 on 05/15/2003 09:03:39

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-15 : 20:08:46

Jeff (Not you jsmith8858),

Don't even think about doing that!! Besides creating an administrative nightmare, you are actually trying to write a custom join operator that works only for a single table. One of the major strengths of a RDBM's is that any of the operators work on any table. If you have seen any of the Object DBMS you will understand the draw back of having to write custom operators for each table.

If it is faster to use a UDF (and I would be shattered if it was) then the speed difference will be trivial.

Create a view, that is what they are for....

DavidM

"SQL-3 is an abomination.."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-16 : 04:09:07

SET STATISTICS TIME ON and SET STATISTICS IO ON may well claim that it runs faster or with less IO, but they're lying.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources