February 23, 2006 at 3:07 pm
UserDefinedFunction Issues in View -- SQl 2000
Hi,
I have a view that refers to table tbl_Job and has left outer
Join to tbl_Employee to get name of user from their ID.
In tbl_Job there are 7 different Ids used and for each
one I need name from Employee table.
I am using View with something like this
Create View vw_Job
As
Select
j.Department,
e1.User_Name AS InitiedBy, e21.User_Name AS WrittenBy,
e3.User_Name AS CheckedBy, e4.User_Name AS VerifiedBy,
e5.User_Name AS PresentedBy, e6.User_Name AS ApprovedBy,
e7.User_Name AS RemovedBy
From .Tbl_Job j LEFT OUTER JOIN
dbo.Tbl_Employees e1 ON j.InitiatedBy_ID = e1.Emp_ID LEFT OUTER JOIN
dbo.Tbl_Employees e2 ON j.WrittenBy_ID = e2. Emp_ID LEFT OUTER JOIN
dbo.Tbl_Employees e3 ON j.CheckedBy_ID = e3. Emp_ID LEFT OUTER JOIN
dbo.Tbl_Employees e4 ON j.Verifiedby_ID = e4. Emp_ID LEFT OUTER JOIN
dbo.Tbl_Employees e5 ON j.PresentedBy_ID = e5. Emp_ID LEFT OUTER JOIN
dbo.Tbl_Employees e6 ON j.ApprovedBy_ID = e6. Emp_ID LEFT OUTER JOIN
dbo.Tbl_Employees e7 ON j.RemovedBy_ID = e7. Emp_ID
Recently we are tuning all Views and tried to replace this 7 joins to tbl_employees
By UDF like this ,
CREATE FUNCTION dbo.EmployeeName
(@ID as smallint)
RETURNS varchar(50)
AS
BEGIN
DECLARE @UserName as varchar(50)
SELECT @UserName = user_name
FROM Tbl_Employees
WITH (NOLOCK)
WHERE Emp_id = @ID
RETURN @UserName
END
Now Created new View replacing join and instead using UDF to get name.
Something like
Create View vw_Job_Udf
As
Select
Department,
dbo.EmployeeName(InitiatedBy_ID)AS InitiatedBy,
dbo.EmployeeName(WrittenBy_ID)AS WrittenBy,
dbo.EmployeeName(CheckedBy_ID)AS CheckedBy,
dbo.EmployeeName(VerifiedBy_ID)AS VerifiedBy,
dbo.EmployeeName(PresentedBy_ID)AS PresentedBy,
dbo.EmployeeName(ApprovedBy_ID)AS ApprovedBy,
dbo.EmployeeName(RemovedBy_ID)AS RemovedBy,
From tbl_Job
When I run both this view with execution plan to compare the performance
View with UDF out runs the previous version which was very much expected.
When there is no condition specified.
Problem comes when I specify the condition like
Where InitiatedBy = ‘ABCD’
Which is the requirement,
Then the version with Join is far ahead in time it takes compared to one with UDF.
That is for pulling 10,000 + rows Join version takes 10-15 sec and UDF takes always more than 60 sec. [80-120 Sec]
Can anyone shed light on this behavior, 7 join of same table is bad but UDF solution turned out to be worst.
I will highly appreciate ideas from all of you here.
Thanks in advance.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
February 24, 2006 at 7:04 am
First off you should be able to simplfy your function like so.
CREATE FUNCTION dbo.EmployeeName (@ID as smallint)
RETURNS varchar(50)
AS
BEGIN
RETURN (SELECT [user_name]
FROM Tbl_Employees
WITH (NOLOCK)
WHERE Emp_id = @ID)
END
Now as for the reason, you are assuming the query is still the same to the original, ergo it should perform exactly the same in comparison.
However run with the execution plan option turned on and you should see the difference.
What I believe you will find is this.
With the Function in place the dataset has to first be completely created and then filtered for your condition against the view.
Without the function and using the left joins the query most likely is seeing it can filter against the related table during the join (which will then be treated like an inner join by the query engine) and thus only the rows where a match to that table require being completed to the dataset because the condition was filtered against that join earlier in the process.
The query engine cannot use the function to look ahead ny in it's plan to determine that there is a better time to filter than after all the joins have occurred whereas without the function the query engine can.
February 24, 2006 at 7:29 am
Thank you Antares,
Here is some more detail after executing
Vw with one condition. It took more than 2 min. in comparision to join with 15 sec.
execution plan output from Select Vw using udf
1.
Clusterd Index Scan tbl_job cost 99 %
Row Count 250003
Row Size 850
I0 12.0
cpu .28
2 :
Filter Cost 1 %
Row Count 12225
Row Size 850
3.
Compute Scalar 0 %
Row Count 12025
Row Size 225
4
Select 0 %
P.S. Your line of thinking looks right here.
With the Function in place the data
set has to first be completely created and then filtered for your condition against the view.
thanks once again,
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply