May 9, 2012 at 1:53 pm
SQLKnowItAll (5/9/2012)
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.
But then you're asking for performance problems from having a 'do everything' proc.
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:58 pm
Putts (5/9/2012)
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.
Woah. Why can't you rely on a SP returning all necessary columns? Thats how you write it. Its simple CRUD. For example, typical Order Header and Order Detail. When I want an order I make a call to the SP for the header and a call to the SP for the details. in the DAL and I can combine them into some super class... but the calls should be consistent and reliable. Why wouldnt they be? And don't say; "That isnt how ours are built" If thats the answer, than they must be fixed. It just isnt that hard.
May 9, 2012 at 1:59 pm
SQLKnowItAll (5/9/2012)
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.
... the problem with that being that SP_GetEmployeesByDepartment would require a Join to the Departments table while the other 2 would not ... and so on, so forth. In order to do what you're suggesting then you'd possible need to make multiple additional calls to get ID values for your foreign keys to then make the final call to this SP.
May 9, 2012 at 2:02 pm
SQLKnowItAll (5/9/2012)
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.[/quote]
..... if I wasn't concerned with performance at all then I wouldn't be here asking these questions and trying to make sure that what I do is not going to cripple our calls. Sure, I'm not using Views/Functions because of performance but I would also forego using them if the verdict was that performance would tank horribly because of using them.
May 9, 2012 at 2:04 pm
DiverKas (5/9/2012)
Woah. Why can't you rely on a SP returning all necessary columns? Thats how you write it. Its simple CRUD. For example, typical Order Header and Order Detail. When I want an order I make a call to the SP for the header and a call to the SP for the details. in the DAL and I can combine them into some super class... but the calls should be consistent and reliable. Why wouldnt they be? And don't say; "That isnt how ours are built" If thats the answer, than they must be fixed. It just isnt that hard.
You're losing me. We use the Views/Functions to make sure the SPs do return all the necessary data. That's the whole point of why I'm asking this question ... so that I can have one data object that has all the necessary data that the SPs can rely on to build the Resultset needed by the code without missing or messing up any particular piece of data.
May 9, 2012 at 2:12 pm
Do your stored procedures filter the data from the views based on values based to the stored procedures, and only return the columns needed for that call?
If so, why not move the select statements from the views into the stored procedures and filter the data directly in the query there instead of using an extra layer of indirection?
May 9, 2012 at 2:16 pm
GilaMonster (5/9/2012)
SQLKnowItAll (5/9/2012)
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.But then you're asking for performance problems from having a 'do everything' proc.
True... I suppose it depends on how that proc runs with actual data. What do you think about pulling all of the required data and then filtering from that. i.e. The proc pulls all data and the application filters it? Again, I suppose it depends on the amount of data?
Jared
CE - Microsoft
May 9, 2012 at 2:17 pm
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
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.... the problem with that being that SP_GetEmployeesByDepartment would require a Join to the Departments table while the other 2 would not ... and so on, so forth. In order to do what you're suggesting then you'd possible need to make multiple additional calls to get ID values for your foreign keys to then make the final call to this SP.
Why would that require a join to the departments table? I assume the department_id or something is contained with the employee record.
Jared
CE - Microsoft
May 9, 2012 at 2:27 pm
SQLKnowItAll (5/9/2012)
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
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.... the problem with that being that SP_GetEmployeesByDepartment would require a Join to the Departments table while the other 2 would not ... and so on, so forth. In order to do what you're suggesting then you'd possible need to make multiple additional calls to get ID values for your foreign keys to then make the final call to this SP.
Why would that require a join to the departments table? I assume the department_id or something is contained with the employee record.
Let's say the business units say that they want to be able to type in "IT" and get a report of Employees in the Department Information Technology which has a surrogate key of 13.
If you're saying that you only want that SP to be able to hit the Department_ID within the Employees data then you need to turn "IT" into 13 before you can do that. Assuming you have implemented this same philosophy on all other data elements in your Database then you may have 2 calls to get to the 13 you need to make the final call to the SP_GetEmployees. Now add that they also say that they want to be able to search on Location, TimeZoneCoverage, SecurityLevel and Length of the Employee's right pinky then you could be looking at making several calls to several SPs just to get all the foreign key values you need for SP_GetEmployees. ... Or you can have a SP_SearchEmployees that accepts all those values directly and does all the joins necessary to make that search happen in one SP. I just don't see why you'd opt for multiple small calls over a slightly-larger-all-in-one call.
Can I assume that you work someplace with an ORM that wraps all of this up into one call for your developers?
May 9, 2012 at 2:38 pm
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
Putts (5/9/2012)
SQLKnowItAll (5/9/2012)
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.... the problem with that being that SP_GetEmployeesByDepartment would require a Join to the Departments table while the other 2 would not ... and so on, so forth. In order to do what you're suggesting then you'd possible need to make multiple additional calls to get ID values for your foreign keys to then make the final call to this SP.
Why would that require a join to the departments table? I assume the department_id or something is contained with the employee record.
Let's say the business units say that they want to be able to type in "IT" and get a report of Employees in the Department Information Technology which has a surrogate key of 13.
If you're saying that you only want that SP to be able to hit the Department_ID within the Employees data then you need to turn "IT" into 13 before you can do that. Assuming you have implemented this same philosophy on all other data elements in your Database then you may have 2 calls to get to the 13 you need to make the final call to the SP_GetEmployees. Now add that they also say that they want to be able to search on Location, TimeZoneCoverage, SecurityLevel and Length of the Employee's right pinky then you could be looking at making several calls to several SPs just to get all the foreign key values you need for SP_GetEmployees. ... Or you can have a SP_SearchEmployees that accepts all those values directly and does all the joins necessary to make that search happen in one SP. I just don't see why you'd opt for multiple small calls over a slightly-larger-all-in-one call.
Can I assume that you work someplace with an ORM that wraps all of this up into one call for your developers?
In that case my list of departments would be separate from the SP that returns the data; i.e. the list box would query SELECT department_id, department_name FROM departments
Then I can not display the id to my users, but pass it to the filter. More trips to the database, but much simpler query. Plus, you may not want the user to be able to select from all departments, so you can filter the query there first.
The idea being that I would prefer not to get a list of departments from 500 rows after joining, but from the original table of 10 rows. Plus, in your case you are not returning the data until after the search definition is defined. So how do you even get a list of departments from data that you have not yet retrieved because it did not know which sp to call?
Jared
CE - Microsoft
May 9, 2012 at 2:44 pm
Lynn Pettis (5/9/2012)
Do your stored procedures filter the data from the views based on values based to the stored procedures, and only return the columns needed for that call?If so, why not move the select statements from the views into the stored procedures and filter the data directly in the query there instead of using an extra layer of indirection?
We work under a mindset of "This SP will return a list of Employees. The code can build an Employee object based on the data from EmployeeView so I will return all the columns from EmployeeView to make sure the code doesn't break."
I won't argue against you that if the Employee's Last Name is the only piece of data you need then that is all you should be returning. You're definitely right. But often while you may only need Last Name initially you usually end up needing EmployeeID and a few other things eventually. Some developers wanted us to just populate everything we could possibly need all at once (Employee data, Department data, Company data, etc) just because they may need it. I got everyone to agree to returning just the data for that one particular "object" (in this case, the Employee) at a time and any helper table data that's exclusive to that table. It's a hybrid which means, of course, that both sides of the fence are going to have problems with it ... but it also makes both sides just happy enough to not continuing griping over it. And, honestly, there's really no "DBA" side of the fence ... just me who's sitting square in the middle trying to find the happy medium.
To those who mentioned the fact that I'm my own DBA and should be trying to simplify things ... that's how it was for years and the lack of any standard let each developer just do whatever they want and, trust me, most did not have much concern for cleanliness on the database - as long as it did what they wanted then they didn't care. I'm trying to define a standard that's easy enough to follow that doesn't end up with a 50 column adhoc query just to return 3 columns' worth of data.
If I can supply simple instructions like:
1. Define the elements of your objects
2. Create a Table to store those objects
3. Create a View/Function that will return as close to just the data you need for that object
4. Create SPs to handle all Create, Insert, Delete and Select calls to that data
5. Make sure the Select SPs return the data from that View/Function.
... then I can try to maintain some level of order that will also have just enough benefit to the programmers that they stay happy and not give me crap about it every day 😉
May 9, 2012 at 3:39 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.
We ALL deal with that sort of thing, man. It's not unique to your situation. But most of us don't resort to adding additional layers, for the reasons we've tried to explain to you.
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?
I don't believe anyone is trying to make you feel inferior. We're simply pointing out that the situation you're creating for yourself isn't ideal, isn't necessary, and that it's going to cause you problems in the long run. The reasons have been identified by several people - some of them by you yourself 😎
You're trying to make maintenance of code easier - which is fine in principle - but the reality is that over time you're going to find out that you've made it more tedious. And at the same time, you're introducing the potential for performance issues.
When you create a stored procedure, your goal should be to make that stored procedure perform optimally. That's a lot harder to do if you're forced to use generic views (or functions) that may or may not be optimized for what you're specifically trying to accomplish. So yes - like it or not, the correct thing to do is to write each stored procedure in and of itself - and that usually means going right to the tables. And sure, that can mean some more work; but the goal should be good code, not easy code.
I've worked on environments that were setup exactly like it sounds like yours is. I've had programmers and DBA's try to sell me on it. And in every case, it's led to performance issues and additional time and energy being burned on maintaining and just keeping track of the additional layers. Buy hey, if you think you're the exception to the rule and you can make it work, best of luck to you :hehe:
Anyway, to answer your original question: It depends. I've seen performance gains when changing UDF's to views, and vice-versa. But honestly, you're basically asking whether a hammer or a wrench is better for driving a screw. Either will work; neither is right for the job.
May 10, 2012 at 5:19 am
cphite (5/9/2012)
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.
We ALL deal with that sort of thing, man. It's not unique to your situation. But most of us don't resort to adding additional layers, for the reasons we've tried to explain to you.
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?
Well said!
I don't believe anyone is trying to make you feel inferior. We're simply pointing out that the situation you're creating for yourself isn't ideal, isn't necessary, and that it's going to cause you problems in the long run. The reasons have been identified by several people - some of them by you yourself 😎
You're trying to make maintenance of code easier - which is fine in principle - but the reality is that over time you're going to find out that you've made it more tedious. And at the same time, you're introducing the potential for performance issues.
When you create a stored procedure, your goal should be to make that stored procedure perform optimally. That's a lot harder to do if you're forced to use generic views (or functions) that may or may not be optimized for what you're specifically trying to accomplish. So yes - like it or not, the correct thing to do is to write each stored procedure in and of itself - and that usually means going right to the tables. And sure, that can mean some more work; but the goal should be good code, not easy code.
I've worked on environments that were setup exactly like it sounds like yours is. I've had programmers and DBA's try to sell me on it. And in every case, it's led to performance issues and additional time and energy being burned on maintaining and just keeping track of the additional layers. Buy hey, if you think you're the exception to the rule and you can make it work, best of luck to you :hehe:
Anyway, to answer your original question: It depends. I've seen performance gains when changing UDF's to views, and vice-versa. But honestly, you're basically asking whether a hammer or a wrench is better for driving a screw. Either will work; neither is right for the job.
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply