May 8, 2009 at 7:17 am
DBA, schmee bee aaa. Who needs 'em? 😉
I wish I had access to a true DBA who knew their stuff. Oh wait! I do! Right here on this website.
I've seen junk code from DBAs and developers. Junk code is obvious regardless of the platform and language.
May 8, 2009 at 7:19 am
...If you're selling a product, I think you ought to know how to tune it for a database. If you don't, I'd like to see some recourse for clients. Maybe some common settlement in a lawsuit. A few of those and I bet you'd have more DBAs hired by software firms.
How about this solution. If you see bad database performance with 3rd party software and you track it down to bad database design, go to the vendor and show them how to make it better. They might not listen, but you might not buy from them again if they don't. I don't think we want to open the can of worms that is getting the courts involved when developers write bad code or design databases poorly.
May 8, 2009 at 7:23 am
As a developer, I've worked at different kinds of shops, and have heard different DBAs rant about different things.
Some things I do now because of these rants:
1. Ask the DBAs for review of a table design before I implement it.
2. Ask what kind of user I should use to access the tables in my application, and tell them what the minimum rights I think are needed.
3. Try to create primary keys on meaningful fields, i.e. customerID AND shiptoID if I am always going to need both of those to identify the record I need, rather than putting a primary key on an identity field
4. Always specify needed fields for selection and update, so field additions to tables don't break my application
5. Cursor avoidance
6. Use of bit fields for boolean values
7. Regularly refill the DBA candy jar, without interest in getting credit.
May 8, 2009 at 7:27 am
I am definitely guilty of retrieving more data than I need to, and using UDFs that make my code simpler and more reusable, but perform badly.
I am going to rationalize some of this since we're still on SQL 2000 and can't upgrade, and that I'm writing mostly reports that are run once a day and not over and over, but yeah...
May 8, 2009 at 7:31 am
This thread points out common mistakes made by developers working in relative isolation. By default one might think that the solution is to either block developer from doing work in the database or make them go back to school and become a deep subject specialists in database technology first. There's another way to look at it. It's a coordination issue.
Let me back way up to get a broader view. From a thousand-mile view, I see two trends. One is specialization of database and development skill sets. That's how we efficiently deal with all the complexity of technology and how we get around the danger of people with shallow command of a technology making key decisions.
Trend two is the quest to reduce time and complexity required to build an application by crossing and combining skill sets and/or coordinating stakeholders better.
Like some, I'm currently toying with the Microsoft "Oslo" which contains a "repository" database in SQL Server full of metadata about the application and its model. The goal stated on the MSDN Oslo home page is "to provide a 10x productivity gain across the application lifecycle."
Is that possible? It begs the question, is the age-old separation/friction between developers, DBAs and other stakeholders inevitable or just a hurdle holding us back?
What's noteworthy for this discussion is that Oslo and frameworks like it seek to allow not only developers but business people and other stakeholders involved in building the database schema for an application safely.
How can it be done safely, you might ask? That's a good question. It's still murky for me, but from what I can see, Oslo seeks to get stakeholders to think at a higher, "entity" level of abstraction (think Entity-Relationship method). If they do that successfully, than for fairly basic applications, it's possible that the schema could end up operational and normalized.
The eye-opener for me is watching MSchema, a grammar for modeling objects and their relationships, output T-SQL code. Imagine that--non-database developers and business stakeholders in a room sketching out models and then cranking out SQL DDL.
Of course, nothing is set in stone and in fact part of the point of the Oslo framework is to allow for continual, coordinated adjustment between the modeling and the coding. So at some point, the "database people" can review it. But will it get reviewed in the real world?
Oslo and these other frameworks cropping up seek to get and keep people coordinated including the deep subject specialists--coordinated in ways that our industry typically is not currently. It will be fun to see how it all works out.
I suspect we'll continue to see bizarre and dysfunctional stuff in the database. People are always trying to cut corners and get something for nothing. The promise of vast productivity gains will inevitably lead some to sketch out models, prop up applications quickly and keep those expensive deep subject specialists out of it.
But what's exciting for me is to see what kind of productivity gains we can achieve safely.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
May 8, 2009 at 7:33 am
The biggest ones I've seen:
- not indicating a primary key and an identity column
- not explicitly creating foreign keys between tables - just relying on the application to handle the relationships between the tables (yikes!)
- direct access to the database
- inefficient stored procs
- inappropriate use of functions
- creating t-sql statements through code in the application and just passing the string to the database to be executed...
I'm sure there are plenty more, as pointed out by others. I do have to say that I'm a little spoiled here at my job. The dev team and the DBAs do tend to get along well, and typically collaborate on projects to avoid these types of situations.
May 8, 2009 at 7:36 am
Some of these have already been touched on but ...
1. Implementing views without understanding them. Far too often I've seen views that either were not needed, or referenced views, which referenced views, which referenced views... You get the picture. You need to understand what SQL Server will be doing behind the scenes.
2. Don't try to do everything in one SQL statement. Many times people create gigantic statements with toooooo many joins and unions and derived tables and end up processing far more data then necessary. It can also be very difficult to support such code if you weren't the one to write it.
3a. Better use of temp tables. Minimize the amount of data being processed.
3b. Improper use of cursors. Recognize what overhead you are placing on SQL Server.
4. "sa" hard coded everywhere. We put a stop to that for obvious reasons. Don't tell people the SA password.
5. Understand how Linked Servers work. We are always fielding questions about how linked server security works. Check BOL for good explanations.
6. Don't use DTS / SSIS just because you can. I've seen a lot of DTS and SSIS packages that were not necessary because the work could have been performed in a stored procedure.
I'll stop here
May 8, 2009 at 7:42 am
majorbloodnock (5/8/2009)
Personally, I think the most common mistake made by developers is also the most common made by DBAs; being blinkered.
Blinkering?!?! Blimey, major, Great Scott, you bloody well confused me!
May 8, 2009 at 7:48 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!
Touche, touche!
It's definitely our fault, just wondering what things we should be looking for 😉
May 8, 2009 at 7:57 am
When I first started my current job they had not had a real dba before. The first thing I noticed on day one was that none of the tables had primary keys. This blew me away. All of the developers I work with now know you need a primary key. They may not always know the best field to put it on, but they know you need one. Another mistake I see is when developers try to design a table to exactly match a web page.
May 8, 2009 at 8:02 am
Worst mistakes I've seen were all from one dev. He uses cursors to step through table variables, so as to populate other table variables that can then have nested cursors run on them, and so on. He actually does this on the idea that it optimizes the code. I took some of his procs from over a minute to under a second, and he still uses that technique. Refuses to learn otherwise.
Same guy decided that the way to fix the performance problems in one of the databases was to create a "data cloud", which was to consist of a single table with a metadata column, a key-value column, a "tag" column, and a "value" column. The metadata column would include an XML definition of what other columns and rows to use, based on the set of key-values in it. So far as I know, he's sold the current IT management on the idea and is working on it for release this Fall. (I don't work for that company any more, so I'm not sure exactly where the project is at.) Early tests were "very promising", in that processing as many as 10 rows of data took only twice as long as the current code processing 100-thousand rows of data.
Two years ago, he created a set of nested, recursive, cursor-based UDFs to resolve an adjacency hierarchy, in an SQL 2005 database. Tested it on five rows of data, took it live. Soon as it hit real customer data, it tanked. Web pages started timing out. That was the week I was hired. I tested the hierarchy crawl on data from one of the big customers, and killed the proc after 8 minutes. (And then replaced it with something that took just over 1 second.)
Beyond psychotics like that, the bad practices I've seen have all been covered in other posts here. I just thought I'd share that one for entertainment value. Honestly, I'm not sure how he's still employed at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2009 at 8:15 am
Most of the problems I most often have with developers are the same as the problems I most often have with DBAs. heres a list of what I think of as dozen most common crimes:-
1) over-specialised, don't understand anything outside there own little patch of technology
2) not a clue about security - for example using SQL logins when trusted access should be used.
3) being unwilling to write stuff that flips between different technical environments to use the right tools - there are times when an application needs to be written partly as stored procedures, partly in scripting languages (.JS,.VBS,and .BAT files), partly in 2nd generation languages like C++ and Java, and partly in 5G langages like LML or Prolog; doing the whole thing in SQL (as some DBAs do) or trying to do the whole thing in C++ (as some developers do) is crazy.
4) being unwilling to flip between security environments as required to ensure that all code runs with the privileges needed and no more - people are often very unwilling to do this if it introduces some asynchrony because the flip can most easily be achieved by queuing something to be scheduled separately.
5) expecting or allowing schema detail (other than stored procedure names and parameters and function and specification of what record sets are returned and the minimum guaranteed sets of columns in those record sets) to be visible to code outside the database. (ALmost equivalently: not realising that the only SQL that should ever be embedded in code outside the DB is calls to stored procedures - but my rule is actually slightly stronger than that.)
6) using column numbers instead of names to select columns from rowsets.
7) adding extra SPs for no good reason: the classic is wanting one extra column in an output record set produced by an existing SP which can be trivially modified to provide the extra column, and making that an excuse for producing a pile of redundant code. (If a developer has written code outside the database that depends on there not being any columns in the record set other than the ones he knows about he's committed crime 5 above.)
8) writing unneccessary line by line code (I've known DBAs do that more often than developers - most non-DBA developers don't know how to write line by l;ine code in T-SQL).
9) not creating the right indexes (DBAs do this when they don't bother to understand the app; developers do it when they haven't learnt enough about how SQL behaves on large datasets.)
10) Failing to write defensive code - I want belt, braces, elastic band, piece of string, and redundant backups! A properly designed and written system will run and work properly even when one of its components fails, because it has error containment and recovery built in.
11) Failing to test adequately: testing has to cover large datasets, empty datasets, load testing, recovery testing (including disaster recovery testing), both black box functinal testing and white box testing, if high availability is required it should include component error injection too.
12) Failure to communicate - this actually one of the biggest problems If DBA's didn't suffer so much from it there would be far less bad SQL written by developers! If app developers didn't suffer from it the DBAs would be able to make better choices about (de-)normalisation and indices.
There are of course mistakes that developers make because they don't know SQL well enough; these range from trivial things to fairly big issues: putting values in quotes (that's an extra 4 bytes each time if you are working in unicode, so it risks pushing an SP's size across a record boundary in syscomments - not such a big deal, really); using patindex when charindex will do; writing "if exists" statements to protect a select or update or delete statement that covers the if conditions in its search conditions (I hate this because it makes the code more difficult to read); not using counter (tally) tables when appropriate; never looking at a query plan to see what is happening; not picking the right isolation level; not being careful about deadlock avoidance; not coding retry on deadlock; not scheduling preprocessing to run an non-peak times. All these can be fixed by education.
A bit of history: I came to my current employer years ago and found some databases that were a complete mess, and applications (C++, ASP in JS, excel macros in VBS) full of pretty nasty SQL, and no stored procs apart from some that weren't used by anything. Other databases that were not so bad, but the worst two were the worst I have ever seen. I introduced a rule that no new code other than stored procedure calls was to be embedded in apps. One bright young developer wrote a couple of sps each of which, in essence, took a string parameter and executed that string - clearly I hadn't explained the reason for the rule properly, the poor guy thought he was conforming (and he ws conforming with the letter if not the spirit) - that was MY fault, not the developer's, for not making sure that the letter of the rule expressed the spirit correctly. At least that's my attitude to developer problems in getting SQL and dtabase stuff right - I'm the expert on this stuff and it's my job to educate people; I don't like the attitude that I see in some comments on this topic.
Tom
May 8, 2009 at 8:16 am
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.
May 8, 2009 at 8:16 am
Lawks! Feeling a bit lazy this Friday, I'm watching this thread and thoroughly enojoying it. I particularly like the Mary Poppins turn the comments are taking
May 8, 2009 at 8:28 am
Charles Hottle (5/8/2009)
... The most irritating issue so far has been applications that have requirements for a user with sysadmin rights on the server. ....
we had one commercial 3rd party app that required SA (not just systemadministrator rights) password to install (the 'sa' name was hard coded in a convoluted install package).
...
-- FORTRAN manual for Xerox Computers --
Viewing 15 posts - 31 through 45 (of 113 total)
You must be logged in to reply to this topic. Login to reply