June 27, 2013 at 10:40 pm
Hi,
We have a View which calls a function to get the data. Following is the code of the View:
CREATE View View_FacultyTimeTable
As
Select Distinct B.ClassId,B.ClassName,B.Section,A.Subject,A.Faculty,A.Wing,B.IntClass,'' As VirtualClass From SchoolDefinePeriod As A,SchoolClass As B
Where A.ClassId=B.ClassId
Union
Select Distinct B.ClassId,B.ClassName,B.Section,A.Subject,A.Faculty,A.Wing,B.IntClass,A.VirtualClass From SchoolDefinePeriod As A,SchoolClass As B,
FN_SchoolVirtualClass()
As C Where A.VirtualClass=C.VirtualClass And B.ClassId=C.ClassID
Is it a bad practice to call a function inside a View??....How many times does this function get compiled??....Only Once(when the View is created) or every time the View is used in a Query?......
Thanks in Advance for your help guys. :-):-)
June 27, 2013 at 11:37 pm
Scalar functions are often bad for performance everywhere, view or otherwise.
As for compiled... Like procedures it gets compiled and optimised any time it runs and there's no plan in cache for it.
p.s. Distincts and a union. Are you sure that's necessary? Do you really have duplicate data in each subquery and data overlapping between the two? Also, joins in the join clause are easier to read than in the WHERE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2013 at 12:18 am
The function in the view is a table valued function. This code is actually very old. It runs on one of our SQL Server 2000 servers and yes the Distinct and the Union is necessary.
The project that uses this data is a very old product and the people who initially developed the project have left the company and the people who are currently responsible for this project are averse to change and haven't bothered update the project to bring it to current coding standards.
There are a few procedures which were taking some time to execute(around 8-10 secs per proc). This issue was escalated to me and I checked the working of the procedure which seemed to be fine. The only part that could cause it to take more than a couple of seconds to execute was the VIEW.
When I checked the View, I found out the function being called.
I think I need to avoid the function and find some other way to get the data into the view.
Thanx alot for your time and advise Gail. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply