June 19, 2015 at 9:15 am
Michael L John (6/19/2015)
The unfortunate part is that it's sometimes very difficult to get the crowd to see the importance of doing it "right" and what "right" means. If it's overwhelmingly impossible to do so because of the entrenchment of ignorance or arrogance on the part of the leaders, it might be time to move on.
When do you make this call? What factors do you use to say it's time to give up?
When you stop trying to prevent problems, and are simply running around behind the devs fixing issues.
To be honest, I've learned enough about the interview process to know what's going to happen before I hire on and avoid such companies like the plague. It hasn't caused me to go hungry yet. 🙂
--Jeff Moden
June 19, 2015 at 2:22 pm
Grant Fritchey (5/20/2015)
ForumsFora. Language Nazis need not apply. Your meaning was clear.
sorry - just could not help myself 😉
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 23, 2015 at 6:52 am
Matt Miller (#4) (6/19/2015)
Grant Fritchey (5/20/2015)
ForumsFora. Language Nazis need not apply. Your meaning was clear.sorry - just could not help myself 😉
Curse you!
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 24, 2015 at 2:05 am
Could you try getting management help to introduce some new tools or even just new tricks for the devs with the selling pitch that these are going to speed up your code and make your life easier as well as improve your skill set for future empoyers - something developers usually are keen on.
I'm thinking of things like ASP.NET data access layers - the automatic stored proc creation produces better code than select * or get them using a modern ORM product. Also tips like In management studio, highlight that select * from query and right click and select "Design query in editor" and just tick the columns you actually need. Also this often converts a Select * query into a select col1,col2,col3 query anyway.
As for the paper box trick, simulate a cursor by getting them to fetch the whole box, pick out one piece of paper from a particular position, scribble on it (update) and put it back then return the box outside. When they come back get them to repeat that process of fetch box get sheet, return box - a cursor loop. And if you have an indexed folder there as well get them to compare how quickly they can pick out a sheet from a named section (indexing) rather than going through the whole box.
Having to physically walk through the processes might just put them off using them.
June 24, 2015 at 9:12 am
To be honest, having Developers physically walk through the papers in the box example is annoying at best and, at worst, demeaning and will earn you a whole lot of disrespect if you force them to do it. Treat your developers as adults. You can explain the paper in the box example as an analogy but the proof is in the code. If a simple demonstration of the code doesn't prove your point, then it's time to fire the developers and the person that made the mistake of hiring them.
Frankly, if you required me to physically participate in a paper box example, I'd go out of the room, pick up the paper box, bring it back to the room, and dump it on your head. 😉 If that meant me having to leave the company, then so be it but I'll not allow myself to be demeaned by others with such stupid physical required participation.
Even though they may not always act it, ALWAYS treat your Developers as adults and with respect even if you don't like them.
--Jeff Moden
June 24, 2015 at 4:08 pm
Give them the url to this thread...
June 25, 2015 at 1:43 am
jshahan (6/24/2015)
Give them the url to this thread...
Yeah, that'll go down like a lead balloon. How would you react if a colleague sent you a URL where they were discussing your shortcomings and asking for ways to improve your behaviour?
June 25, 2015 at 10:15 am
I encourage the "demo" mindset. The box of paper exercise would feel good but would kill the working relationship you have with these guys. Personally, my best experience with the "demonstration" was with a web interface to an online internet-accessible database. Our work area was "bullpen" style with half-walls and the developer doing the frontend was across the open expanse and our monitors faced each other. He was on the phone with his team and customers in a conference call and I purposely maximized my browser window with his login screen on it. I typed the old "Little Bobby Tables" (XKCD reference) stunt [in the login name text box] but put in some select statement instead of the evil "drop table" statement. The "audio" of the conversation let me know my timing was perfect:
Him: "Yeah, it looks good. We've done all the testing and we're ready to g..."
Me: <clicks the 'Login' button>
My browser: <showing a list of all tables in the database, a clear sign that I was in the database>
Him (clearly seeing what's on my monitor): "...uh, I'm going to have to go now. I'll call you back."
Classic SQL injection at the login screen.
Lesson: Dynamic SQL is dangerous, stupid and just bad form.
I'd suggest the same thing, in a conference room with the group. "OK, now that we've covered dynamic SQL, let's move on to SELECT *. Let me show you what happens when we add a column/select just what we need/select a column that's indexed/et cetera..." Maybe a session of watching the activity with SQL Profiler wouldn't hurt either. Reads, writes, I/O.
Battling the "we've always done it this way and it isn't broken" fight needs to be impersonal and riddled with indisputable fact. Also, get a game plan. It's human nature: Nobody likes their pet databases to be changed by force and they certainly don't like be presented with "this is broken" without "and this is how to fix it" or without "this is what you gain by fixing it." Remember: it's not about "I want this fixed" but "you will gain SO MUCH if you let me help you fix this".
...while thinking "you need to trade your dirty old dollar for my shiny new quarter"...
LOL Welcome to being a DBA: part technical expert, part expert troubleshooter, part con artist.
June 25, 2015 at 10:22 am
Used properly dynamic SQL is a viable tool for development. The key here is "used properly."
June 26, 2015 at 2:22 am
Your Name Here (6/25/2015)
Lesson: Dynamic SQL is dangerous, stupid and just bad form.
Using dynamic SQL without parameterising it is dangerous, stupid and bad form. If the developer in your example had still used the dynamic SQL, but passed in the login details as parameters rather than directly hacking it into the SQL string, then it would have worked fine.
June 30, 2015 at 7:50 am
paul.knibbs (6/26/2015)
Your Name Here (6/25/2015)
Lesson: Dynamic SQL is dangerous, stupid and just bad form.
Using dynamic SQL without parameterising it is dangerous, stupid and bad form. If the developer in your example had still used the dynamic SQL, but passed in the login details as parameters rather than directly hacking it into the SQL string, then it would have worked fine.
I agree. The "best practice" (notice the quotes please. Sarcasm is "on" ;-)) of totally avoiding Dynamic SQL was spawned as the wrong kind of deterrent for SQL Injection. "If you don't use it, it can't happen" is the general thought. But, as with any such rule, you're giving up a wealth of power, flexibility, and simplicity. It like saying you can cut yourself with a skill saw, therefor, you should never use a skill saw. Just totally wrong thinking. The correct thing to do is to learn how to use the tool properly and safely and then enforce those methods.
--Jeff Moden
June 30, 2015 at 8:40 am
Addendum: I use dynamic SQL for a lot of DBA work - ad hoc work that doesn't require a cached execution plan because it only runs when I want it to. It's necessary in certain situations and I freely admit it is acceptable where needed and used intelligently when there is no other way to accomplish the task.
Conversely, I've worked with developers in the past that created
every.
single.
command.
as dynamic SQL whether it needed it or not. The entire application's interaction with the database was via dynamic SQL. I personally watched SQL Profiler show me DML that could have easily been set-based running on one row at a time for millions of records based on a concatenated string of T-SQL in an EXEC() statement that included enough detail to make each statement unique. (I can show you the hole in my belly where my soul tunneled out and ran away).
I admit I'm jaded and anti-using-dynamic-SQL-in-highly-active-database-applications. I'm not entirely against it, just against the abuse of it. I'm not (or at least I [try] to be) "that guy" who sits back in is chair and spouts unrealistic theory. I've seen it be a great solution and a horrible solution.
July 3, 2015 at 8:16 pm
Nah... you're not jaded... experienced and wise is more like it. I've see the same ridiculous things, even recently, like having the front end create and transmit a quarter million very wide, full up, INSERT/VALUES statements instead of (in this case) passing 8 parameters and letting SQL Server generate the quarter million rows as a single insert would work so much better at all angles. Then, to add insult to injury, they had the nads to blame the database for it taking so long and the customer's network for not being able to get anything else done when a couple/three of these things ran at the same time.
And I don't think you're anti-dynamic-SQL. You're just like a lot of the rest of us that are long in tooth... we normally say "It Depends" but we get our hackles up when we see stuff that we have already seen go south in the past because of a not so rare combination of ignorance, stupidity, and the "I just want to get if off my plate" attitude. We also don't want to read about ourselves in the news as being the latest company that got hacked due to SQL Injection.
--Jeff Moden
July 8, 2015 at 9:52 am
Jeff Moden (6/19/2015)
Michael L John (6/19/2015)
The unfortunate part is that it's sometimes very difficult to get the crowd to see the importance of doing it "right" and what "right" means. If it's overwhelmingly impossible to do so because of the entrenchment of ignorance or arrogance on the part of the leaders, it might be time to move on.
When do you make this call? What factors do you use to say it's time to give up?
When you stop trying to prevent problems, and are simply running around behind the devs fixing issues.
To be honest, I've learned enough about the interview process to know what's going to happen before I hire on and avoid such companies like the plague. It hasn't caused me to go hungry yet. 🙂
I finally gave up.
After three years of providing proof in code, fixing things myself as proof, providing many many articles and books, and sometimes just pounding my head on the desk, I left for another position.
Hopefully I asked all of the right questions, and received all of the right answers throughout the interview process. Plus, knowing some folks who are already there helped!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 8, 2015 at 10:23 am
200+ columns? Look if you have a developer, in particular a lead; that does not know that this is an issue I am not sure there is much the community can do.
But...
HUGE indexes that will perform as bad as a table without indexes, loss of relational data; I mean why not just have one huge table?
Page loads
Network traffic
Number of indexes required on table.
Meet or exceed maximum bit count for table width (Surprised you have not yet hit that).
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply