September 9, 2013 at 7:16 am
Good Morning Everyone.
This is my first post in the forums, but I find the emails very informative and knowledgeable. I have an issue and am wondering what others have done to maybe help resolve it.
My Manager is an ex Classic ASP/Access developer. My team of Programmer Analyst have converted everything to ASP.NET/Silverlight with SQL Server backend. We're in the healthcare field and things are constantly changing and deadlines for submissions always around the corner. Manager still has this Access mindset with limited T-SQL knowledge. He creates views on top of views in order to get what he needs done (Access mindset - run make table query to use in another query) . He says it's a temporary fix to get what he needs, but since he doesn't go back to clean them up, they become permanent.
We've tried to get him to stop and just ask us to create what he needs, but that doesn't work. Any advice would be great!
September 9, 2013 at 8:20 am
I used to work for a company which followed a similar approach. In my experience its usually been down to the overall attitude towards how the product is developed. Generally cutting corners and a lack of design time trying to meet unachievable timescales.
I would try and quantify why you want to stop this behavour, for example look at the usage of the views and quantify if they perfom poorly, also demonstrate how changing to another apprach can be more efficent. Also I have seen some very poor code over the years with this kind of apporach that can be easily over come using windowing functions, CTE's, derived tables, temp tables etc etc.
You will only really be able to convince a change if you go to them with solutions rather than problems.
MCITP SQL 2005, MCSA SQL 2012
September 9, 2013 at 10:51 am
Take a look at some of your more common or longer running queries that are using these nested views. Then, check the execution plan, specifically looking at the first operator, the SELECT or whatever. Look for the reason for early termination. If it's timeout, and from the sounds of things, that's likely, then you can explain exactly what that means. It means that the optimizer has attempted to get you a good execution plan, but, due to the complexity of the query, so many objects, duplicate objects, unpacking views, etc., the optimizer was unable to give you a good plan. Instead, you have a plan that is either outright bad, or at least is unstable and likely to run different ways at different times. Those timeouts, assuming you're getting them, are absolutely hurting your performance on multiple levels. What you want to see for early termination is "Good enough plan found." This shows the optimizer successfully completed it's work. You can also show the stored compile times and compile CPU for the timed out queries and compare them to the success plans, usually these will be much higher.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 10, 2013 at 7:56 am
He creates views on top of views in order to get what he needs done...He says it's a temporary fix to get what he needs, but since he doesn't go back to clean them up, they become permanent.
What you're describing is a form of technical debt (http://www.c2.com/cgi/wiki?TechnicalDebt). Most shops experience this in varying degrees (you take shortcuts to meet a deadline, promising yourself you'll go back and clean it up eventually), and it can be hard to avoid completely.
But, as with, any debt, if you let it accrue to too high a level, you end up spending more resources paying off the interest (i.e. managing the less-than-optimal code) than you do paying down the principal (refactoring).
I would suggest that you document the implementations that you consider to be technical debt and revisit that document any time you have a refactoring window. I would assume that your manager isn't the only one incurring such debt, so this document shouldn't feel like a personal attack if you share it with him; but if he recognizes that he's frequently raising the debt level with his shortcuts, maybe he'll try harder to conform to best practices? You could even approach him with the idea of tracking technical debt first (w/o implying he's a part of the problem), and if he agrees it may be harder for him to ignore his role in generating it once it has all documented.
September 10, 2013 at 9:10 am
First idea: Educate your boss quickly.
Second idea: Ask your boss to name the views based on when they'll no longer be needed, so you can see what to clean up.
Third idea: Create a separate database/schema for your boss. Show your boss how they can use this as their own personal area, for all the stuff they do (creating views). Remove your boss's rights to create views in the original database/schema. Slowly educate your boss - but in the meantime, the views are isolated from the original.
September 11, 2013 at 3:04 pm
Interesting problem.
I started my career as an access developer and 15+ years later I am now a sql developer. If your boss doesn't truly know sql, he should not have anything but read access to your production systems. Access was more of a GUI way of creating views as opposed to sql where you basically write queries.
I had problems at first adjusting but after a year I didn't miss access at all.
My first approach would be to be honest with him. As creating views based on views is not very efficient and probably could take time to modify or maintain.
If that doesn't work you can streamline some of these views and recommend more efficient ways of doing them.
September 11, 2013 at 4:16 pm
Seems like your manager needs a promotion to a position where he can't make any damage. :--)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 11, 2013 at 8:30 pm
Thanks everyone for you suggestions. They're all very informative, especially the "technical debt" post. That caught my eye and gives me a way to explain the situation to him in a way he can understand it.
I've decided to take on the task of streamlining.
My first step was to retrieve all the views that weren't even working (deleted/renamed table/view/column/etc). That alone was about 20 percent out of all of them.
Next step is to write a procedure that will give me a datasource hierarchy of each view and work on converting them to stored procedures.
All this while training him on creating stored procedures and how to use temp tables (for now) and CTEs later.
Can't remove his access because well, he's my manager and in this business there are deadlines for submissions to government related departments. So there isn't any slack there.
Hopefully, all of this will work. I would hate to leave my position/team over something like this.
September 12, 2013 at 8:27 am
September 12, 2013 at 3:59 pm
Good luck, Louie, hope it works out for you. And I like your plan to yank out all the deprecated/broken stuff; anything that can separate the noise from the signal is a step in the right direction.
Also, liked the Chicken Allegory in the link that Nadrek posted; that's a very accessible way to portray the issue.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply