Hi,
Let's look at what happens when we use function in Where clause.
There are 2 places where we can use a function, 1st when we want to fetch some value in select clause and 2nd when we want to compare a value using a function in where clause.
Usage of both ways will affect the performance of the query.
Let's see a simple example of function.
Create a function that return Employee LastName, Firstname for passed Employee ID.
CREATE FUNCTION GetEmpName
(
-- Add the parameters for the function here
@EmployeeId int
)
RETURNS NVARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @empname NVARCHAR(100)
SELECT @empname = LastName +', ' + FirstName FROM Employees WHERE EmployeeID=@EmployeeId
RETURN @empname
END
GO
Let's see how this works:
SELECT dbo.GetEmpName(9) AS Employee_Name
Dodsworth, Anne
SELECT distinct dbo.GetEmpName(a.EmployeeID), b.TerritoryDescription
FROM dbo.EmployeeTerritories a
INNER JOIN dbo.Territories b
ON a.TerritoryID=b.TerritoryID
WHERE dbo.GetEmpName(a.EmployeeID)='Davolio, Nancy'
Result
Employee TerritoryDescription
Davolio, Nancy Neward
Davolio, Nancy Wilton
Let's look at the profiler to see how the query is executing:
As we can see that the profiler has executed the function so many times, which actually slow down the query performance.
Let's change our function a little:
CREATE FUNCTION dbo.GetEmpNameCrossJoin
(
-- Add the parameters for the function here
@EmpId int
)
RETURNS Table
AS
Return(
SELECT LastName + ', ' + FirstName EName FROM dbo.Employees)
GO
--Now execute this using Cross APPLY
SELECT distinct I.EName, b.TerritoryDescription
FROM dbo.EmployeeTerritories a
INNER JOIN dbo.Territories b
ON a.TerritoryID=b.TerritoryID
CROSS APPLY dbo.GetEmpNameCrossJoin(a.EmployeeID) I
We have changed the function to return table instead of a scalar value and the function call is done using CROSS APPLY.
Now there is only 1 row in profiler.
This way we can improve the performance of the query.
Happy SQL Coding.
