May 8, 2009 at 8:37 am
the biggest nightmare I have seen thus far is a third party app where the developers have converted GUIDs into varchar(32) fields for storage in the db, worse yet each table also has an integer id which is a fine candidate as a PK.
add to this the fact that some of the varchar id fields have been used to store non-GUID type entries such as the word 'default' and the question of what Fozzy, Kermit and the gang are doing now becomes much clearer! 🙂
Assumption is the mother of all F***ups
May 8, 2009 at 8:41 am
Touche, touche!
It's definitely our fault, just wondering what things we should be looking for [Wink]
Interesting that you should say that Steve.
I've been fooling around with SQL Server since v4.2.1. I been DBA, developer, troubleshooter, you name it. There are so many issues mentioned here that I agree with and I don't want to parrot them.
As one who primarily does development today (much less of a DBA role) and, consequently, spends a lot of time fixing someone else's code, my biggest rant is SQL developers who don't know how to read and use an execution plan. IMHO performance is not solely within the DBA's realm. While a DBA might not want a developer futzing around with indexes, the developer should be responsible for understanding the performance of his own code and either fixing it or asking for help. A SQL developer who cannot read and understand an execution plan is not a SQL developer. We would generally not find it acceptable to task the network engineers with figuring out why web applications are slow. That is the job of the web application developer. To the extent that the SQL developer has control, he also has the responsibility.
The other issue is the development of databases and DML by procedural-trained application programmers who know nothing about set-based data architecture. While the .Net languages have come a long way from their precursors, they still support FOR loops so I consider them to be procedural languages. I spent a long time working in 4GLs where there was no such construct and it was never an issue. However, it was a good learning experience for dealing with set-based data. Most Windows application developers do not understand set theory as it relates to SQL Server. Many of the issues mentioned in this thread can be boiled down to that. It probably doesn't pay to train those application developers in depth relative to SQL, making them database specialists, but I think a basic understanding of set theory and how it is implemented in SQL Server would open a lot of eyes for application developers.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
May 8, 2009 at 8:49 am
Weird design normalization (breaking comments into 70-character fields that you then have to reassemble), hideously profligate use of cursors, the same field has a different name in different tables, treating the database as a data store (i.e. not using stored procedures), total lack of responsiveness from support.
But the one that really gets my proverbial goat is the fact that they won't provide us with a good data dictionary. They'll give you a E/R map, but no description about what is stored where.
(most of my current problems can be explained by the fact that they have to design for lowest common denominator among multiple database vendors. it doesn't help things, but it does explain things.)
A previous monstrosity that I had to work with used nchar everywhere when international support was not required. Also used nchar for date/time, constantly having to convert to do any sort of time/range work which was a huge part of the operation..
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 8, 2009 at 8:53 am
NoKoMo (5/8/2009)
Naming conventions. It seems a rather difficult task for most to name the tables and fields in a way that would make logical sense - thus supporting readability. More time should be given to thinking through what to call what. It is not a stretch to have field names match or come close to label names in the application.
And lack of prefixing selected columns with the table they come from. DBAs do this too. If you're joining more than a few tables it's a pain to have to go into each to see the source of each column.
But the biggest has to be executing .Net code which produces a recordset that gets iterated and updated; essentially a cursor. I would look at it and say I can do that same thing in a single T-SQL statement. Coming from a developer background myself, this is one of the hardest aspects; moving from row based to set based thinking.
Ken
May 8, 2009 at 9:08 am
G Bryant McClellan (5/8/2009)
I think a basic understanding of set theory and how it is implemented in SQL Server would open a lot of eyes for application developers.
Do you have any favorite "set theory" reference that you'd like to recommend?
I often have to explain connection strings and those 4GuysFromRollo are my favorites to send people to for research. I agree that the best way to solve a problem is to become part of the solution. And I also think that most Developers WELCOME thoughtful suggestions for improvements. So if lending them a copy of my Keith Devlin book makes my job easier I'll buy two copies.
May 8, 2009 at 9:09 am
I'm a developer first, and then a DBA second (because none are on site), so I will be following this discussion closely. Because I am a developer, I am a part of the "forest" and therefore not aware of my own shortcomings, when it comes to properly tuning an application for database interaction. Thanks, Steve, for a good topic!
Kindest Regards, Rod Connect with me on LinkedIn.
May 8, 2009 at 9:16 am
I spend most of my time as a controller these days, and see problems on both sides of the fence. I still develop just to keep a hand in, and maintain my understanding of all the developed systems. The last few years I have been seeing three bothersome trends:
1) Over-specialisation (developers and DBAs) that leads to horrifying miscommunication, narrowmindedness, and a scary abundance of misplaced confidence. At least in the old days the arrogance came from folks who earned their right to be dense, but now I'm seeing this from recent graduates. The end-result is none of the moving mouths are communicating, and that is always destructive. You cannot teach unless you first walk in the shoes of the student; lecturing isn't good enough. We all need to start talking the same language when we're not writing code or designing databases.
2) Rapid changes in tool-sets are leaving legacy builds of databases lagging, and rather than assistance in rebuilds a lot of time is spent pointing fingers. In our case, we have a system that has now crossed 3 versions of SQL Server, and shortly a 4th. While all the access code is isolated in stored procedures, there are no cursors, etc., the fact is the new tooling makes some of the past decisions pretty pathetic in retrospect -- but its damn difficult to revise legacy systems when the DBAs spend more time criticizing the past decisions "of developers" than resolving problems, and the developers spend more time growling than working (even though none of them made those past decisions). That aside, the tooling is a challenge these days because it is bringing such rapid change and potential, but with almost no easy guiding principles -- and implementation skills seem to be lagging.
3) I also have to leap on the indexing bandwagon, from a slightly different perspective. I am dealing now with a massive system (data wise) that has almost no indices in place. The problem is that the two DBAs working on it don't seem to have much better grasp of indices than I do, and I feel I'm ignorant to an embarrassing degree. What changes are being made and tested are taking forever and showing minimal (if any) performance improvements. If the database was perfection I might believe that probability, but...no way that is the case. So, I have developers tuning code endlessly because the index changes are providing no support for them, again leading to that frustrated silence between the camps; and two DBAs so busy building indices that evidently (I'm told) show massive increases in the profiler, but have no real-world impact.
All that aside, it's an interesting topic, mostly about communication and education.
I would love to see a real, simple, and developer-focused article or two about indexes. Drop all the terminology possible, and just shape an understanding so the developers can grasp what and why indexing is so useful. As it stands all the articles are either too superficial for them, but require terminology they don't necessarily have, or too specific.
May 8, 2009 at 9:18 am
By far, the most common mistake I've seen my fellow developers make is to deliberately avoid implementing foreign key constraints. In fact, I've inherited nearly every app I've worked on and not a single one of them enforced referential integrity via foreign key constraints. One of my project managers told me that enforcing referential integrity "sounded good in theory but isn't the real world." I had another project manager who simply said, "I hate foreign keys". My current co-worker (I'm in a two person shop) said that foreign key constraints are "a pain in the a$$." Incidentally, every one of the apps that sit on these databases is buggy, and much of the data is garbage--a direct result of these attitudes.
With regard to normalization, I had a colleague in a sister organization who was providing us with data that we had to import into our database. He provided the data in the form of one large table--no normalization whatsoever--not even a look-up table for states or provinces. When he found out that I normalized the data before importing it into our db (via DTS at the time), he told me that he didn't see the benefit in spending time on that. Something tells me that his apps were high maintenance as well.
May 8, 2009 at 9:21 am
mister boom (5/8/2009)
5. Documentation. What's that? 😉
Lol
May 8, 2009 at 9:21 am
Do you have any favorite "set theory" reference that you'd like to recommend?
I realize that invoking Joe Celko's name here may cause certain strong reactions, but his book Thinking In Sets is one that I would recommend. Like any other tome, you have to consider the situation the book was written in versus your environment. Thus, your mileage may vary.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
May 8, 2009 at 9:23 am
Michael Lysons (5/8/2009)
Good grief, sweeping generalisation about developers there, Steve. Seems to me that if a developer is implementing incorrect indexes etc, then it's actually the DBAs fault for letting them do it. DBAs, eh - tut!
Many tech shops have a very immature and basic approach to handling data from the application side first. Database administration is usually an afterthought - only when performance and managability start to materialize in downtime for an organization. So they scramble to hire a DBA and toss him or her into a legacy environment where these decisions have already been made or are continuing to be made because of the processes and political structures in place.
Only in an environment where the DBA is authorized to be the gatekeeper of such things, would the statement above hold true.
May 8, 2009 at 9:30 am
Phil Melling (5/8/2009)
Security considerations. Time after time I get databases come to me with instructions on how to deliver the various database objects, but no consideration on how users are actually going to be accessing them through the application. The worst offenders are the third party vendors where it just seems the norm to give the database user dbo privileges over the database.Why - for the love of God - why dbo???? I'll just build another brick wall to knock my head against then!
Hey Phil,
Do you want a few of the ones I have to support that "require" sysadmin? For the web application account!
Fortunately I have a large brick wall conveniently located next to my desk...
NO! BONK! NO! BONK!
May 8, 2009 at 9:30 am
One of the craziest I have seen at my company is creating a column as a varchar(1)! Yikes!
May 8, 2009 at 9:32 am
Frank Buchan (5/8/2009)
I would love to see a real, simple, and developer-focused article or two about indexes. Drop all the terminology possible, and just shape an understanding so the developers can grasp what and why indexing is so useful. As it stands all the articles are either too superficial for them, but require terminology they don't necessarily have, or too specific.
Not really developer focused, but have a look at these (disclaimer, they're all mine)
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
I'll put 'intro to indexes' on my list of things to write about.
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 8, 2009 at 9:48 am
I will pass the links along to the lads. I probably passed the one before, I think, as it is quite familiar.
Then they willc laim they had no time to read them. 😛
Viewing 15 posts - 46 through 60 (of 113 total)
You must be logged in to reply to this topic. Login to reply