May 9, 2012 at 9:58 am
This is a quasi-programming question as that is the "why" I'm doing what I'm doing ... but the roots of what I need to know are database-dependent.
I've come up with a standard for our programming group that when they define a class in C# they also need to create a View in the database that matches that class exactly. I do this so that when anyone gets to a point in their program where they need "ClassX" then they know that in order to populate an instance of that class they need to pull every column from the View named "ClassX." This reduces the "hey, I tried to use your class but it says I'm missing ColumnA ... where are you pulling that from" conversations back and forth.
Additionally, if you have a Stored Procedure (or, in most cases, several SPs) then each of those must return all the columns from the View for the object that it's expecting to return.
Of course the problem is that any time you make a change to the class and thus have to make a change to the View then all those SPs need to be updated to also pull the new column(s) (even if you're using the evil *, a refresh of the SP is required). That could be a ton of extra work that'd could be overlooked and eventually cause problems if missed.
While playing around with Table-based Functions, I realized that changes done to them are immediately reflected in the SPs which would save a bunch of tedious work and reduce the risk of missed changes. However, I'm not sure if there is going to be a performance hit by using that Function as opposed to Views as well as any other possible pitfalls that I've yet to consider.
In summation, what problems/deficiencies might I be introducing by changing these Views over to Table-based Functions as the Back End Source for my class constructors?
Thanks in advance.
May 9, 2012 at 10:06 am
Putts (5/9/2012)
In summation, what problems/deficiencies might I be introducing by changing these Views over to Table-based Functions as the Back End Source for my class constructors?
Performance.
I recall Grant telling me about a system where the developers used layer upon layer of user defined functions (multi-statement UDFs). The system fell over with about 2 users.
In-line table-valued UDFs are OK, multi-statement are not.
That said, the entire design sounds odd to me. Sounds like you're half building your own ORM. If so, why?
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
May 9, 2012 at 10:52 am
There's some questions and concerns our programming team has had about ORMs and have never really gotten answers for and until we can see an example of how it'd work in our environment we are foregoing that particular technology.
If we didn't have any of the functions calling any of the other functions, would the performance still take a hit?
May 9, 2012 at 11:07 am
Putts (5/9/2012)
If we didn't have any of the functions calling any of the other functions, would the performance still take a hit?
If they are multi-statement table valued functions, quite likely yes.
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
May 9, 2012 at 11:08 am
To expand on that, here's a simplified example of what we'd be doing:
(and this entire design could be the problem in itself with what we're trying to do ... still trying to hammer down a design standard for our apps going forward)
Tables:
Employees
EyeColor
HairColor
Department
Company
Functions
getEmployees - would return Employees joined with EyeColor and HairColor
getDepartment - returns just Department data
getCompany - returns just Company data
getEyeColor - returns just EyeColor (would only ever be used for drop downs, etc)
getHairColor - returns just HairColor (would only ever be used for drop downs, etc)
Then let's say that in multiple places of our application we may have to find blue-eyed, brunettes who work for SqlServerCentral (and we define that as being the "ideal Employee") so we write a function called "isIdealEmployee(@EmployeeID int) returns bit."
Then, from there, we have the following 2 SPs:
getListOfIdealEmployeesByDepartment (@DeptID int)
getIdealEmployees (@EmpID int = NULL)
Basically, one where we want to find all Ideal Employees by Department and another one where we can get back all Ideal Employees or just one (based on if it's null or not).
The source of each of those would be something like:
SELECT *
FROM
dbo.getEmployees E
WHERE dbo.isIdealEmployee(E.EmployeeID) = 1
(additional where for each SP here)
I'm not expecting to make the Functions a multi-layered structure since I work really hard right now to make sour our Views don't get nested either as I've had enough performance nightmares from that scenario already in my career - we basically frown upon any view referencing another view.
May 9, 2012 at 11:09 am
I might be getting lost on what Multi-Statement UDFs are ... I'm not doing anything in the UDFs outside of either a simple RETURN SELECT or IF(EXISTS(SELECT)) statement in any one particular function.
I assume that is not a Multi-Statement UDF, right?
Anything that is more specific and detailed than that would be done in the SP .... which is the way it's done right now - it just does it against Views currently as opposed to table-value functions.
May 9, 2012 at 11:10 am
Putts (5/9/2012)
SELECT *FROM
dbo.getEmployees E
WHERE dbo.isIdealEmployee(E.EmployeeID) = 1
Function in where clause means that's likely to table scan every time, regardless of indexes.
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
May 9, 2012 at 11:12 am
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
May 9, 2012 at 11:13 am
Okay, that's probably the answer I'm looking for then.
So if you had a similar situation that I have (a complicated join is needed to determine if SituationX exists), would you have that same join/where combination duplicated in multiple SPs? Or is there some other technique I should be considering?
Also, based on that StackOverflow posting ... I'm definitely not using Multi-statement UDFs in this scenario.
May 9, 2012 at 11:18 am
I've probably overcomplicated this question greatly.
In its simplest form:
If I have X Views and take the SELECTs from those Views and put them in table-value UDFs instead, will I suffer a performance hit?
May 9, 2012 at 11:25 am
My question is why are you looking at moving from views to table valued functions.
What is the business case for the move.
May 9, 2012 at 11:39 am
Currently, our Data Access relies heavily on SPs - we do not call Tables or Views directly from code. If we have a reason to query the database then we create a specific SP to reflect that need. Then all data requests for that data go through that one SP and if a change is needed then it's done once in the SP and we don't have to hunt down all code references looking for that (sure, you could say "well why not force all such calls to just go through one programming method" but we've found that people don't stick to that very well).
What we end up with then is several SPs that reference, E.G., ViewX. No matter if you reference all the fields explicitly from ViewX in your SP or just simply do a SELECT * FROM ViewX, you will be forced to refresh those SPs any time you make a change to ViewX. That introduces potential problems if someone misses an SP or two that reference that View.
Table-valued Functions, however, don't have such a limitation. Once you make the change in the Function the SP immediately reflects that change.
So, if you have the following:
Table: Employee
Function: getEmployees (Returns all data from table Employee)
SPs:
getEmployeesByDepartmentCode
getEmployeesHiredThisYear
getEmployeesByLastNameInitial
... and all those SPs do a "SELECT * FROM dbo.getEmployees" then you can make changes to the Function and not have to touch the SPs at all to get them to pass through those changes. That's a great improvement from having a view called EmployeeView and basing all those SPs on that. But things are usually not that simple so I'm wondering if I'm missing something.
May 9, 2012 at 11:47 am
Well, I'd have to touch stored procedures as I don't like using SELECT * in my code.
Here is what I would be concerned about, what if a developer, while making changes to the iTVF changes the order of the columns? The code calling the stored procedure is expecting the columns in a specific order.
May 9, 2012 at 12:02 pm
Lynn Pettis (5/9/2012)
Well, I'd have to touch stored procedures as I don't like using SELECT * in my code.Here is what I would be concerned about, what if a developer, while making changes to the iTVF changes the order of the columns? The code calling the stored procedure is expecting the columns in a specific order.
I typically hate the * as well due to the unnecessary overhead and how difficult it can make supporting (since you have to dig further down to find if ColumnA is being returned) but if using * has a valid purpose (in this case it could prevent us from overlooking references to the function that need to be updated when the function changes) then I can justify putting aside the hatred in lieu of an "easy-to-support" and "less-error-prone" system 🙂
As far as the second concern, our developers are in charge of both sides of the wall - we are our own DB Designers (scary, I know). We have a DBA but his only job is security and server stability/maintenance. We create our own database structure and also write the code to administer the data within it. It will only take a programmer a few times of doing something like that before he learned that he was just causing himself more work. 😎
May 9, 2012 at 12:05 pm
No matter if you reference all the fields explicitly from ViewX in your SP or just simply do a SELECT * FROM ViewX, you will be forced to refresh those SPs any time you make a change to ViewX.
I think the problem is that you are requiring all data to be queried from views. Query directly from the SP to the table and you avoid this issue. For me, you did not make a good argument to have all classes reference a view.
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply