May 8, 2009 at 3:54 am
Developer failings from my experience (SQL/BI developer):
1. Excessive use of cursors. Some developers seem to prefer to work with one row of data at a time then wonder why the application runs slowly.
2. Indexes - too many or not enough. In my last couple of contracts I've spent some time showing developers how to view Execution Plans, and use the Index Tuning Wizard and Database Engine Tuning Advisor. Some of them didn't know the tools existed!
3. Use of the "sa" login everywhere
4. Using inappropriate data types. eg. CHAR(255) for surname fields which are obviously variable length, or not allowing enough characters for a field based on the sample data that they created
5. Documentation. What's that? 😉
May 8, 2009 at 4:03 am
As for me, the most evil thing happened when developers should agree their plans with customers and correct them according to their caprices (They wanted triggers, but I knew it will be madness...)
May 8, 2009 at 4:18 am
A few examples I've seen personally, (some of which have already been mentioned);
1) Thinking that cursors are a perfectly reasonable thing. (Overheard someone say "I didn't realise cursors were bad").
2) Not allowing auto-generating primary keys - thus, you have to generate each key value, one at a time, and can't use "INSERT INTO".
3) Having about 20 columns of the form "{name}1", ..., "{name}20", so I had to write a lot of code to convert that into the proper set-based lay-out.
4) Inappropriate data-types. Far too many use "varchar"s for holding single character keys.
5) Too much use of dynamic SQL, even for common functionality, so there's far too much copy-and-paste.
6) No documentation of db objects.
May 8, 2009 at 5:16 am
Perhaps my experience is unusual, but I've recently determined that clustered indexes do not perform nearly as well as non-clustered indexes. Perhaps this is because the tables I was working with were very wide, but non-clustered indexes which were covering significantly outperformed the clustered index (this is on SQL Server 2005). I now keep my eye out for cases where there is a clustered PK and consider creating a non-clustered version and add any columns not in the key which are typically needed to be included in the index.
May 8, 2009 at 5:17 am
Oh dear.
I'm a developer rather more than a DBA, but I'm far from ignorant about databases.
Normalisation - yep, know how to do that (and about denormalisation, though I might suggest in testing that's becoming less and less relevant in real-world environments).
SQL injection - been carefully protecting all my code against it for years
Indexes - well, I know the difference between clustered and nonclustered indexes and their different applications, and that a PK is alread a CI
Rows that fit into pages? Certainly know about that one.
(Rare) Appropriate use of cursors? Yes, know that.
Appropriate use of UDFs to avoid RBAR queries? Yes.
Am I perfect? No, but then I'm still learning. I've only been doing this full time for about 10 years so far. I've no doubt I'm still making mistakes that I'll cringe at in a few years but that's life in any discipline.
I've worked with people (or inherited their code) who clearly couldn't design a database schema and code for toffee. Their non-DB code in whatever language we were working in on that project tended to suck too. Sure, there are lazy, incompetent buffoons who shouldn't be let near a coding environment of any description - they exist in any paradigm, and the good at one, when they've got to work at least slightly cross-discipline, are usually good at the other after a little acclimatisation too.
May 8, 2009 at 5:24 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!
You are assuming that there IS a DBA in the shop or that the developers consult or listen to the DBA. All too often a development shop sees the database as a mere inconvenience, an impediment in the way of writing a real killer app. The fact that there is data involved that will have to be gathered, stored and manipulated is secondary to the app.
IMHO one of the biggest mistakes a development shop can make is failing to tap the power of the database engine by placing all the T-SQL in the application code rather than using stored procedures.
May 8, 2009 at 5:56 am
Cor, Phil, you ain't arf serious. But, yet so right. The older I get, the more I seem to deliver the kind of message/rant/lecture to people that my dad, a developer from the 60's, gave to me: Always test with a good chunk of data and don't forget the empty set. Simple, really.
May 8, 2009 at 6:03 am
SELECT * FROM ...
Sure this is useful for ad hoc queries, but too often it appears in production code without good reason.
May 8, 2009 at 6:07 am
The biggest mistake developers make? Easy... Failure to consult and work with a good DBA when designing and developing an application. In other words, before and during the design and development process - not after when something is running like a crippled slug in molasses.
The second biggest mistake developers make? Presuming that SSMS, SSRS and SSIS are acronyms for strains of the Sars virus... (and yes, thats true, based on a guy we interviewed a while ago who "knew" SQL Server). :w00t:
May 8, 2009 at 6:15 am
Amen to the injection. Field validation in the applicaiton is a biggy.
Databases are often used as spreadsheets. Just like developers are taught in school. Data access can be an art form too because all record sets are not sized equally. A DBA can help the developer here. We often know the data better than the application developer but are clueless about the application. It changes but those changes aren't uploaded to the DBA's KB.
I think that we could spend some quality time with our developers and not looking at them as the impediment but as an opportunity to tout our benefits.
SELECT * FROM DEVELOPERS WHERE CLUE IS NOT NULL;
Best way to resolve the query above is to lend a hand or to "insinuate" yourself into the development process from the very begining. Involving the DBA with the business side of the application as well as the data is a winner.
Bob
May 8, 2009 at 6:23 am
Lack of understanding the SQL Server security model. Using sa or putting everyone in the dbo or datareader and datawriter roles. SQL in the application not in stored procedures. Not using parameterised SQL, whether calling stored procedures or ad hoc SQL in the application. That's the one thing that I like about LInq to SQL and Entity Framework, at least if they are using that they are most likely using parameterised sql.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 8, 2009 at 6:33 am
I've been exposed to the quirks of third-party software more than I have in any other position. The most irritating issue so far has been applications that have requirements for a user with sysadmin rights on the server.
I've also seen deployed third-party databases with autoshrink turned on despite model not having it enabled. I've seen third party databases installed with data files in the location of the system databases and not the default location defined for the server. In many cases, there seems to be little consideration given the databases that is being deliveered with a product.
May 8, 2009 at 6:43 am
I know we all like to do a bit of a complaining, but what about getting a bit positive and changing this thread or creating another that gives those poor development people some clues to good practice? How about a top 5 things to do or not do?
May 8, 2009 at 7:00 am
One word. Cursors.
"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
May 8, 2009 at 7:01 am
Greg Webb (5/8/2009)
Indexes - well, I know the difference between clustered and nonclustered indexes and their different applications, and that a PK is alread a CI
Oh dear, what makes you believe that?
One of the problems I sometimes see is that a developer has chosen the primary key correctly (it allows foreign keys pointing into the table) but has made it clustered when it should not be (because the most frequent operations involve dealing with a large number of rows which are adjacent if ordered by a particular column which is not the primary key and c perhaps cannot be the primary key because the same value can occur more than once in it. The system then performs poorly because it doesn't have a clustered index on the right column.
When a developer understands that the primary key doesn't have to be a CI he will be able to write things like
create table thing(
thingID int identity constraint PK_Thing primary key nonclustered,
thungID int not null constraint FK_Thing_Thung references Thung(thungID)
on delete cascade on update cascade,
morecols nvarchar(256)
)
create clustered index IX_Thing_thungID on Thing(thungID)
and get the performance benefits of having the right rows placed close together on the disc. This can be very important if the table occupies several tens of Gigabytes and a typical query looks a a range of values for thungID hitting maybe a hundred thousand rows.
Tom
Viewing 15 posts - 16 through 30 (of 113 total)
You must be logged in to reply to this topic. Login to reply