May 9, 2012 at 1:25 pm
Lynn Pettis (5/9/2012)
Why do this:Table --> View (or iTVF) --> stored procedure --> App
When you can do this:
Table --> stored procedure --> App
You haven't justified the need for the extra layer of indirection.
Because then your App is tied directly to the make-up of each individual SP. This is not a problem if you rely on an ORM but we do not. So, what we do currently is this....
1. Create Table
2. Create View
3. Right App to assume all data coming back is, basically, SELECT * FROM VIEW
4. Create SPs and make sure that they always return all columns from the View
If you leave the View out then there could be fields that are not directly stored in the Table but are necessary for creation of your objects in code and if they are not returned from your SP then your code will fail.
May 9, 2012 at 1:27 pm
SQLKnowItAll (5/9/2012)
Maybe you missed the post in which Gail said that using a table-valued UDF causes a table scan every time.
I have never said such a thing because it would be a blatant untruth.
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 1:29 pm
Putts (5/9/2012)
Lynn Pettis (5/9/2012)
Why do this:Table --> View (or iTVF) --> stored procedure --> App
When you can do this:
Table --> stored procedure --> App
You haven't justified the need for the extra layer of indirection.
Because then your App is tied directly to the make-up of each individual SP. This is not a problem if you rely on an ORM but we do not. So, what we do currently is this....
1. Create Table
2. Create View
3. Right App to assume all data coming back is, basically, SELECT * FROM VIEW
4. Create SPs and make sure that they always return all columns from the View
If you leave the View out then there could be fields that are not directly stored in the Table but are necessary for creation of your objects in code and if they are not returned from your SP then your code will fail.
So, if you have a table with 100+ columns, even if the app only needs 10 of those columns for a particular function you return all 100+ to the app? And you do this regardless of the number of rows of data being returned as well (1 row or 1000 rows)?
Okay, I will leave now as I can see there being performance issues with this application.
May 9, 2012 at 1:33 pm
Going to jump in here:
From an architects point of view, I would always create my classes based on the logical design, not views which are abstract. What the OP describes, where a view returns all of the fields of say 2 or more tables is inherently less than optimal. In the case of a one to many relationship, data from the parent will be duplicated for every child row. I would design my classes on my base objects, the get/set become self evident and clearly defined. This makes changing to an ORM MUCH simpler.
From a DBA point of view: UDF's regardless of type are non sargable and should be avoided when used in a JOIN condition or WHERE clause. In addition, any query in a UDF will not have a cached query plan. UDF's are not like SP's. Performance becomes exponentially worse as the recordcount increases.
Here is my suggestion: Before trying to make SQL Server go through all these gyrations, explore different ORM's, if only to understand what you should or shouldnt do.
Then choose one, because re-inventing the wheel is crazy talk.
May 9, 2012 at 1:33 pm
Putts (5/9/2012)
Because then your App is tied directly to the make-up of each individual SP.
Yes, and what's wrong with that?
1. Create Table
2. Create View
3. Right App to assume all data coming back is, basically, SELECT * FROM VIEW
4. Create SPs and make sure that they always return all columns from the View
Create table
Write procedure and make sure that it returns the correct columns
Write app based on the output of the procedure.
I don't see an advantage in another level of indirection in the middle. Add a column for a particular part of the app then you still have to change the app, table and the view. It's just changing the thing that's changed from proc to view.
Don't want to make the change manually, use a refactoring tool to do it.
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 1:33 pm
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
I think the "real" problem here, that you are avoiding, is that the database schema keeps changing. If it was designed properly to begin with, you would only have a minimal amount of columns being added and would not have any of these problems. I've worked on just shy of 500 projects with .NET developers and cannot think of 1 case where what your doing would have been a good solution to anything.... then I need to work where you work. Cuz where I work we build the applications according to the specs and then, 2 months later, the specs change and we have to adapt.
At this point you guys are apparently just trying to make me feel inferior about the environment in which I work and are not actually addressing the question. Is there any negatives to replacing Views with single-line UDFs?
Nothing about my statement was meant to demean your work environment or your processes. I just don't think you should be doing what you are doing because I see no benefit to it.
A stored procedure returns the columns needed for the app, independent of the table structure. So, if you have to add a column to a class you already know which stored procedure is used. You don't need to search for anything. If a column is added to a table, you need to change 1 SP and 1 class. I presume the class change was really what set off this change, so in actuality you are changing the class first. Then you have a table with data and the stored proc for only that class right?
We all know the case of the "neverending project" where things keep changing. You are trying to reduce the amount of steps to make those changes, but you are going to cause more problems by doing that.
Jared
CE - Microsoft
May 9, 2012 at 1:34 pm
GilaMonster (5/9/2012)
SQLKnowItAll (5/9/2012)
Maybe you missed the post in which Gail said that using a table-valued UDF causes a table scan every time.I have never said such a thing because it would be a blatant untruth.
Sorry, I should have been much clearer. "Function in where clause means that's likely to table scan every time, regardless of indexes." My bad...
Jared
CE - Microsoft
May 9, 2012 at 1:35 pm
Allow me to rephrase my question to get off the particulars.
We want to create a virtualization within our database to reflect the makeup of a class in code ... what is better for this? A View or a Table-valued Function?
Lynn Pettis (5/9/2012)
So, if you have a table with 100+ columns, even if the app only needs 10 of those columns for a particular function you return all 100+ to the app? And you do this regardless of the number of rows of data being returned as well (1 row or 1000 rows)?
Okay, I will leave now as I can see there being performance issues with this application.
Lynn, I've already discussed that concern in a previous thread here. I have fought hard and long with other developers that would make mega-views and return 50some columns just because they wanted to make sure they had the 12 they needed to create an instance of a class. What I have come up with is creating the class in code and then creating a "bare minimum virtualization" of that class in the database. It's the best of both worlds that I could come up with that both reduced the load on the database and also made development move along as quickly as possible.
Prior to doing what we do now, people would basically guess at what they needed in order to populate the class data they needed and would often pull in full tables they didn't even need. I'm trying to do the best I can with what I have and I am just looking for an answer to a simple question to make sure I'm not taking our design down a road that is filled with pitfalls I cannot foresee.
May 9, 2012 at 1:41 pm
SQLKnowItAll (5/9/2012)
A stored procedure returns the columns needed for the app, independent of the table structure. So, if you have to add a column to a class you already know which stored procedure is used. You don't need to search for anything. If a column is added to a table, you need to change 1 SP and 1 class. I presume the class change was really what set off this change, so in actuality you are changing the class first. Then you have a table with data and the stored proc for only that class right?
You could have any number of SPs that return data for a particular class.
E.G.
SP_GetEmployeesByLastNameInitial
SP_GetEmployeesByDepartment
SP_GetAllEmployees
All of those return "Employee Data." The Employee class does not change per each SP so the SP needs to adhere to the definition of the class. What's the easiest way to do that? The best way I've found is to define an object in the database that reflects exactly what that class needs. Then when you're writing an SP that you know is returning Employee data you know that you need to return the data from the EmployeeView as that has everything that the Employee class is expecting and requiring to be created and be used throughout the app.
May 9, 2012 at 1:41 pm
We want to create a virtualization within our database to reflect the makeup of a class in code ... what is better for this? A View or a Table-valued Function?
That's how I treat a view anyway, so I say view.
Jared
CE - Microsoft
May 9, 2012 at 1:42 pm
Putts (5/9/2012)
Allow me to rephrase my question to get off the particulars.We want to create a virtualization within our database to reflect the makeup of a class in code ... what is better for this? A View or a Table-valued Function?
Lynn Pettis (5/9/2012)
So, if you have a table with 100+ columns, even if the app only needs 10 of those columns for a particular function you return all 100+ to the app? And you do this regardless of the number of rows of data being returned as well (1 row or 1000 rows)?
Okay, I will leave now as I can see there being performance issues with this application.
Lynn, I've already discussed that concern in a previous thread here. I have fought hard and long with other developers that would make mega-views and return 50some columns just because they wanted to make sure they had the 12 they needed to create an instance of a class. What I have come up with is creating the class in code and then creating a "bare minimum virtualization" of that class in the database. It's the best of both worlds that I could come up with that both reduced the load on the database and also made development move along as quickly as possible.
Prior to doing what we do now, people would basically guess at what they needed in order to populate the class data they needed and would often pull in full tables they didn't even need. I'm trying to do the best I can with what I have and I am just looking for an answer to a simple question to make sure I'm not taking our design down a road that is filled with pitfalls I cannot foresee.
The issue I see, is your trying to create this abstract layer IN the SQL Server, instead of in your data access layer. I understand your goal, but I would move it to the DAL instead of trying to do it at the DB level. Especially since you do not have a full time DBA, this becomes increasingly important.
Perhaps creating the typical CRUD operations in the SQL Server and the base classes based on those, and in the DAL, abstracting those out to your virtual class makes more sense? This makes it a lot less ambiguous what is being saved and retrieved from the DB AND allows for easier migration to an ORM at a future time.
May 9, 2012 at 1:45 pm
SQLKnowItAll (5/9/2012)
We want to create a virtualization within our database to reflect the makeup of a class in code ... what is better for this? A View or a Table-valued Function?
That's how I treat a view anyway, so I say view.
And I know that that's the standard answer and that's what I've always done ... but then I see comments like the ones on this article ...
... and I start wondering if there's something better out there and so I thought I'd ask the guys/gals I know I can trust here at SSC. Because I want to know that what I'm about to do is not going to cause me any more trouble/bad performance than I'd have with the Views.
May 9, 2012 at 1:46 pm
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
A stored procedure returns the columns needed for the app, independent of the table structure. So, if you have to add a column to a class you already know which stored procedure is used. You don't need to search for anything. If a column is added to a table, you need to change 1 SP and 1 class. I presume the class change was really what set off this change, so in actuality you are changing the class first. Then you have a table with data and the stored proc for only that class right?You could have any number of SPs that return data for a particular class.
E.G.
SP_GetEmployeesByLastNameInitial
SP_GetEmployeesByDepartment
SP_GetAllEmployees
All of those return "Employee Data." The Employee class does not change per each SP so the SP needs to adhere to the definition of the class. What's the easiest way to do that? The best way I've found is to define an object in the database that reflects exactly what that class needs. Then when you're writing an SP that you know is returning Employee data you know that you need to return the data from the EmployeeView as that has everything that the Employee class is expecting and requiring to be created and be used throughout the app.
Ok, so you need 1 more column. You add it to the class and the table. Presumably, since each SP is calling the function, you do not have to do anything to the SPs. All you have to change is the function. If you use a view instead of a function, you have to change the view and recompile each sp.
OR
You can use 1 SP built to handle all 3 of those scenarios and then no views, no functions, and you only change 1 SP. 1 SP per class.
Jared
CE - Microsoft
May 9, 2012 at 1:48 pm
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
We want to create a virtualization within our database to reflect the makeup of a class in code ... what is better for this? A View or a Table-valued Function?
That's how I treat a view anyway, so I say view.
And I know that that's the standard answer and that's what I've always done ... but then I see comments like the ones on this article ...
... and I start wondering if there's something better out there and so I thought I'd ask the guys/gals I know I can trust here at SSC. Because I want to know that what I'm about to do is not going to cause me any more trouble/bad performance than I'd have with the Views.
But, you are not going for optimization with your view. You are using it to simplify your coding process. So the above article is a moot point to you.
Jared
CE - Microsoft
May 9, 2012 at 1:51 pm
DiverKas (5/9/2012)
The issue I see, is your trying to create this abstract layer IN the SQL Server, instead of in your data access layer. I understand your goal, but I would move it to the DAL instead of trying to do it at the DB level. Especially since you do not have a full time DBA, this becomes increasingly important.
Perhaps creating the typical CRUD operations in the SQL Server and the base classes based on those, and in the DAL, abstracting those out to your virtual class makes more sense? This makes it a lot less ambiguous what is being saved and retrieved from the DB AND allows for easier migration to an ORM at a future time.
That's basically what we're doing. But if we can't rely on all SPs returning all necessary columns then we can't write a singular method that's able to translate data from the database to a class in the code.
If you all could see how obscene some of the database calls were getting before I started pushing our group to move to this standard then I think you'd understand why I'm not for "just let the SP get whatever data the creator thinks he needs and then code accordingly"
If we do decide to move to an ORM at some point then we'd simply start stripping the DAL out of our code as well as these Views/Functions that provide the aliasing of the Table data and go straight from the base code classes to the DB Tables - leaving a BLL -> ORM -> Tables structure. But we've had such nightmares trying to troubleshoot issues we've had with ORMs over the years that I don't see us ever moving to one again.
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply