Database development mistakes made by application developers

http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers/

by phalien • 16 years ago

154 69 16 years ago

Also: don't make assumptions about how you believe a database works, just because you've used another one (or another version of the same).

And measure every optimization you make, too :) I saw someone doing an optimization that should have accelerated the processing, only to discover once in production it was 4x slower.

DrJokepu• 16 years ago

Interesting list, have a few good points, unfortunately some points are personal opinions rather than universally agreed practices.

CWIZO• 16 years ago

Don't know if this was mentioned in the answers (haven't read them all):

Don't assume that once you make your query run perfectly it will stay that way for ever. Once row count grows, your query might start to perform very poorly. Happened to me just recently, when a query was running in the 0.0001s range, but over night crawled to a halt and took 1 minute(!) to execute. Always pay attention to your querys and monitor if anyone of them is starting to slow down.

Estragon• 16 years ago

Are there wrappers for database APIs which log the time spent on each query?

IgorPartola3• 16 years ago

The MySQL slow query log could be of use. Of course you don't get a stack trace this way but it's essentially free (as in built already and not a huge performance hit).

ZoFreX• 16 years ago

I know MySQL has an option to log slow queries for you: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Devilboy2• 16 years ago

You should use your RDBMS for that kind of stuff, all the good ones can log everything you ever wanted to know about your database queries.

protomyth2• 16 years ago

Your second point (overnight performance hit) needs to be explained to managers often in projects that didn't spend the money in tools or proper performance testing.

Don't expect some gradual curve of loss of performance. Once a table exceeds the cache size an ok performing query can go very bad with no warning (say 100x longer). This might mean that you really need to do a production patch of some type. Plan for this before it happens.

projectileboy• 16 years ago

Given the rise of NoSQL solutions and the amazing lack of query runtime optimization performed by most relational DBs, looking at this list made me ask "why even bother?"

mcantor4• 16 years ago

There's a yawning chasm of difference between "a query that gets the right data in the right way, but does it somewhat inefficiently," and "a query that gets the right data in a stupendously roundabout way." For the former, query optimization is a reasonable salve. For the latter, no amount of automated optimization can save you from crippling performance problems.

Look at it this way: If someone comes to you and says, "I want to get from New York to California in 2 hours, so I'm going to take a 747." Off the top of your head, you could tell them, "I don't think you can do that without a Concord." A computer could easily optimize that plan.

But if someone says, "I want to get from New York to California in 2 hours, so I'm going to stick grapes in my nostrils and soak my shoes in canola oil," there's nothing you can do to help this person, because they clearly have no idea where to even start. A computer could do no better.

Using or instead of in() is taking a 747 instead of a Concord. But many of the points in that article, like using distinct to mask a lack of understanding about how you're asking for data, are very much the grapes & canola-oil-Nike's of SQL.

EDIT: Whoops. Sorry; since you had been downvoted, I misread the gray-on-light-gray text as "Given the amazing amount of run-time query optimization performed by most relational DBs." Guess I should have run my comment through an HN Post Optimizer.

NoSQL solutions are not a drop-in replacement for relational databases, either, though. They're different tools for different jobs.

No matter what, you're going to need to look at the same data in different ways. If that isn't true, then the decision is easy. But in pretty much every non-trivial application I've ever worked with, the source information and the requirements for the resulting data format have never been the same for every feature.

When you decide to go with NoSQL, you are implicitly accepting some of the burden previously handled by the RDBMS: The onus is now on you to do your own "indexing," your own "views," your own "normalization." I only have experience with CouchDB, which provides an excellent array of tools for handling these responsibilities, but for certain types of data, there is still no substitute for an RDBMS.

arethuza• 16 years ago

The more I use CouchDB the more I see it as something that complements "traditional" relational database features.

I'm actually rather hoping that someone produces a hybrid NoSQL-SQL system that gives the best of both worlds.

Nitramp• 16 years ago

"amazing lack of runtime optimization"? Do you have anything to back that up?

Most "old" RBDMSes (i.e., not MySQL) are actually ok to quite good at optimizing queries. Sure, you will always find things that are not optimized properly, but having worked on a query optimizer myself, this is actually much harder than you might think it is. Not only coming up with optimizations, but also making sure they are legal, and don't degrade certain queries. There's nothing worse than optimizing a legit query to pieces...

cubicle672• 16 years ago

For what it's worth, here's a few from me

Indexes. Everybody says it, but I'm still surprised at the number of devs who have next to no idea about index and how they work.

Few guides: Every index adds a write penalty, so don't just add them to everything. If you've got a compound index, it will only be used if the query also uses all the fields in the index (If you build an index on surname, firstname and dob, but execute select * from users where firstname="Bob" and surname="Jones", the index won't be used). Benchmark the difference each index makes, and stand back in awe at the improvement a few well places indexes can contribute.

[Edit: as pointed out below, my experience is well out of date and it looks like there's a number of database engines where the compound index thing doesn't apply]

Learn to think in sets. Most devs are used to operating on discrete pieces of data; we're not used to thinking in sets, and so writing efficient SQL doesn't come naturally. Put in the effort and learn it and you'll be well rewarded. SQL is built around sets of data, and can almost assuredly do stuff faster than your code can. Hint: anytime you find yourself getting the result of a query, iterating over the result set and executing another query for each result you're doing it wrong

Learn how your ORM works. If you're using an ORM (and most of us do), learn how to use it efficiently. This is where having a good working knowledge of SQL helps, because you can then find out how to help your ORM create good SQL. If you're using ActiveRecord, understand how to correctly use :conditions, :include etc, and what they're doing behind the scenes. Ideally you want your ORM to make one call to the DB, and to retrieve everything you need in that one call (calls are expensive, even for very small queries). Poorly written code causes the DB to get hit constantly. Not good,

simonw• 16 years ago

> (If you build an index on surname, firstname and dob, but execute select * from users where firstname="Bob" and surname="Jones", the index won't be used).

I did not know that - I always thought that a compound index would be used for a query against a subset of those columns provided the subset was a prefix of the overall list - so in your example, a query on surname would use the index but a query on firstname and dob would not.

morgo4• 16 years ago

The poster corrected himself above - it's not the case.

nbpoole• 16 years ago

> (If you build an index on surname, firstname and dob, but execute select * from users where firstname="Bob" and surname="Jones", the index won't be used).

I don't believe that's true, at least not for all RDBMS. I just checked the docs for MySQL and PostgreSQL and they seem to agree with me.

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-index... | "The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name."

http://www.postgresql.org/docs/8.2/static/indexes-multicolum... | "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns"

cubicle672• 16 years ago

[Edit: removed defensive response. I've updated my original comment to remove its absoluteness]

morgo4• 16 years ago

If we want to 100% correct, indexes don't just hurt writes, and they don't hurt all writes either. Two examples (there are more):

- If I do an UPDATE query, and I don't modify indexed columns, the index doesn't need to be modified.

- If I have indexes on (last_name, first_name) and (last_name) the (last_name) index is redundant. Some queries may use the last_name index when I don't specify the first_name - but they could have used the other index. Now I have bloated what contents need to be memory resident (affecting reads as well).

ams61104• 16 years ago

This is called a "covering" index, e.g. the index on lastname+firstname also "covers" queries on with a predicate on just the last name. Most relational databases will do this, but it's always good to be sure in your particular case.

swah2• 16 years ago

After reading the first example at http://www.postgresql.org/docs/8.2/static/indexes-intro.html, I'm a little confused.

I thought that operations using primary keys were always fast, that they were somehow always indexed.

I thought this would be fast:

  SELECT content FROM test1 WHERE id = constant;

and this would be slow because it wasn't on a primary key:

  SELECT content FROM test1 WHERE content = "xxx";

EDIT: this comment was supposing that 'id' was actually of type "serial primary key".

tbrownaw• 16 years ago

I don't see any primary keys in that example, just one column that has an index (fast to look up on) and one that doesn't (slow to look up on, but they don't do that in the example).

IgorPartola3• 16 years ago

A primary key is just a unique index. All of its other properties and uses have nothing to do with the underlying implementation. It may be slightly faster for some types of queries than a "secondary" unique index if the RDBMS clusters the data by the primary key and you are using traditional hard drives. In your example, if you indexed the content column, the lookup on it would be fast. As a bonus, make sure to read up on composite indecies. They are the bread and butter , so to speak.

henrikschroder2• 16 years ago

Generally:

In a primary index, the leaf nodes are the actual data records, so when you've followed the index to the leaf you have the exact physical location on disk that you need to get the data from. If you do a query that retrieves multiple records, for example

  SELECT * FROM test1 WHERE id > constant;

...then that translates very nicely into a bulk read from the underlying datastore.

Contrast that with a secondary index, where each leaf node essentially contains the physical address of the record, so for each record you're returning, you have to make an additional random seek to the underlying datastore to get the entire record. This is why primary indexes are faster than secondary indexes, in general. Various caching schemes smooth out the difference though.

Another good tip to keep in mind when writing indexes is that you can have covering indexes. If you often do a query of the type

  SELECT a, b FROM table WHERE c = ... AND d > ... ORDER BY e, f

If you then have an index over (c,d,e,f,a,b), that index can cover the entire query, i.e. you can return the records by just looking at the index, you don't need to fetch the actual records.

binarymax3• 16 years ago

Good Additions. I would also point out that if devs mess with indexes they definitely need to know the difference between clustered and nonclustered. I've seen atrocities due to a simple misunderstanding of this (guess what happens when you confuse the two and implement the opposite!)

tocomment• 16 years ago

Use non-clustered, right? clustered is just for massive operations with distributed databases and junk, right?

jfb5• 16 years ago

Pedantically, SQL is built around bags. It's one of the worst misfeatures.

IgorPartola3• 16 years ago

My addition to the already great list: using temporary tables to serve content. Temp tables are great when you have a complex report, and breaking things up into stages gives you a performance boost (think you need to do multiple lookups against a list of users that is small but takes a long time to build). So if you generate this report at midnight via a cron job, temp tables work great. However, generating a bunch of them at once is a performance hit. Instead, use indecies or (in the worst case) caching with triggers.

alexeyklyukin• 16 years ago

A suggestion to use surrogate keys instead of natural ones doesn't seem right to me. IMO proper choice of natural keys leads to better mapping from a knowledge domain into the corresponding relational model; if you are unable to find natural keys maybe there's something wrong with your database schema?

ams61104• 16 years ago

A professor of mine (this was YEARS ago) maintained that if each table did not have a natural key then the model was wrong. In theory this may be correct, but the use of surrogate keys avoids a lot of problems in practice.

jasonlotito3• 16 years ago

Not being able to find a natural key isn't why you use a surrogate. You use a surrogate because natural keys are not static: they can change. Surrogate allows you to have a key that is unrelated to the data, data that will change. Basing keys on changing data is risky.

I've experienced this myself, and while natural keys do work in theory, in practice, they are prone to failure.

jfb5• 16 years ago

ON UPDATE CASCADE. Tis a pity it's not more widely implemented.

matwood• 16 years ago

No matter how correct you think your schema is today something will always come up. Surrogate keys also give you a way to always have a single column key to use in joins and foreign key constraints.

ora600• 16 years ago

The discussion in the comments of the following blog post make some pretty good points in favor of surrogate keys:

http://prodlife.wordpress.com/2007/09/05/meaningless-foreign...

ergo98• 16 years ago

6. Not simplifying complex queries through views

Number 6 needs to be used with discretion.

When you reference a complex view, you are in essence making use of a stored query snippet -- there is no magic to the view, and unless you've materialized it (which is very rare), there is no efficiency advantage. All it really is is code sharing.

So what's the problem then?

The problem then is that the query analyzer has to assume that you actually care about every element of said view, every returned column, every spurious join. I've dealt with too many performance problems where people made use of an overly generous view that invalidated most indexes, removed the potential for covering indexes (the #1 most important performance element of a database), and did a lot of joins that were irrelevant to the usage.

protomyth2• 16 years ago

6 and 13 (use IN not OR) really rely on the optimizer doing its job and can hide some nasty performance issues. I do know one release of Sybase would freak on IN but be fine for an OR (have no idea why). Also, views where very dicey.

mdoyle• 16 years ago

I started in database development in 1995ish. Surprised to see an article like this detailing the exact same problems we had back then. Our solution was to have classroom sessions with developers, to provide them with the education they wanted/needed. Biggest problem back then was the use of embedded SQL rather than stored procedures.

SkyMarshal• 16 years ago

I was going to post the exact same thing. I started in '99 and am shocked, shocked to see this is still an ongoing issue. I thought by now most devs had at least read a little Codd, Date, and/or Pascal.

Maybe it comes from having started building web and db apps for large enterprises where the DB was the nucleus of the operation and the apps the satellites. It seems among the web crowd it's the opposite - the app is the nucleus, with the DB and presentation layer the satellites, resulting in ORM among other things.

In those enterprises, access to the DB's was strictly controlled by the DBA team and data made available to apps via stored procs only. The app devs would request a stored proc with certain parameters that would return a certain dataset, and the DBA team would implement it and grant access to that app only (or grant access to an already-existing sp that did the same thing).

The idea of ORM still bugs me for that reason, even though I use it anyway. I can't help but suspect it's the formalization of a convenient but bad practice. I could be wrong about that though, biased as I am in this regard.

jacques_chester• 16 years ago

> It seems among the web crowd it's the opposite - the app is the nucleus, with the DB and presentation layer the satellites, resulting in ORM among other things.

Indeed, this is one of the strongly-held "opinions" of Ruby on Rails. The database is more or less treated with contempt, a mere bitbucket.

mcantor4• 16 years ago

I never understood the groans of dismay from other developers when the subject of writing SQL comes up. I'm a dyed-in-the-wool application developer, but I love writing SQL.

Unlike HTML & CSS, which seem designed to spread into a greater morass of obtuse and interdependant hacks with every trial-and-error iteration, SQL has always felt elegant to me.

It takes just as much time to craft an SQL query as it does to translate a design into HTML/CSS, but 99% of the time I can sit back and be proud of my handiwork; even re-using it later.

On the other hand, I have never completed a webpage without looking at the resulting markup and thinking, "What an ugly goddamn kluge." SASS and JQuery and HTML5 and CSS3 don't make a difference. Maybe I'm just bad at it, though.

I eschew ORMs at every opportunity, and it is mysterious to me why "writes SQL for you" is such a common "feature." I would liken it to a tool which "paints paintings for you" or "designs your app for you." Why would I entrust a computer program to do something which requires the creative spark of a human?

contextfree2• 16 years ago

I like the relational algebra underlying SQL. I don't like the clunky syntax, or the weak type system, or the lack of a good composition mechanism. Agreed that HTML and CSS are even worse!

metageek• 16 years ago

Having written a SQL->SQL compiler (), I can state that the language is a mess. I still like it better than the NoSQL tools I've used, though.

() The company's product was, broadly speaking, a specialized analytics engine, with temporal features. The analyses were written in a dialect of SQL-92 (just SELECT statements, plus extra features for temporal logic and currency conversion), which I had to compile to PL/SQL and T-SQL to run as stored procedures.

mcantor4• 16 years ago

By "lack of a good composition mechanism," are you referring to the trappings of writing and dealing with SQL syntax itself? As in, "Ugh. I decided to change this join into a subquery, and now my @#$#% parentheses are imbalanced!"?

If so, I agree strongly, but I feel like that problem could be solved by better error reporting on the part of the compiler. I dread SQL errors now, at least with Oracle (which I work with the most often), because they almost never tell me what I need to know. To a certain extent it's essential to the abstraction of data reporting itself, but certain errors are just comically vague.

jfb5• 16 years ago

Or the lack of closure under composition. It really f-ing grates.

docgnome• 16 years ago

I think this has more to do with people hating to write CRUD vs crafting queries for a report of some sort. I can see the argueement for thinking about SQL as wasting brain power when you're just doing simple interactions.

mcantor4• 16 years ago

I agree as far as Create, Update and Delete are concerned.

It's the Read part that gets me. I'm all for frameworks that map HTML forms to model object properties to database fields so you don't have to write boilerplate, but I don't think you can treat "Read" like the other three. For the life of me I cannot articulate my reasoning right now, elegantly or otherwise, but ORMs brush a lot of complexity under the rug in that respect.

henrikschroder2• 16 years ago

ORM's are great for the 99% of your DB interaction which basically boils down to "Fetch a record from this table and put the result in an object", or "Fetch these records from that table and put the result in an array of objects".

However, for that remaining 1% of complicated queries, a good developer can do a much better job than any ORM.

iaskwhy3• 16 years ago

A sort of hack I use sometimes (clearly documented) is to avoid joins when dealing with large data, mainly if caching is not an option (it should be but, hey, I found out it's not always like that!).

Say you have a table for people and another for cities where they currently live at. If you were to do this for a small country you won't deal with many different cities so a join won't waste much time. But if you're using all the countries in the world then the number of cities is really big. When that happens, use two different and simple queries: one to get all the people, another to get all the cities. Then just check if both entries exist on your sets and use them on a while or for-each loop. It can be a big improvement.

Note: This is a really simple example which isn't the best case scenario for this tip. Think large sets with lots of joins between them.

torme2• 16 years ago

Can you explain this some more? You don't really list what you're trying to achieve in your example and I can't think of a case where looping over a large data set in code is better than querying against it.

iaskwhy3• 16 years ago

I believe it's not easy to explain but let me try again and if it doesn't work and you are really interested in it drop me a line by email and I try with some more complex examples, ok?

Once I had this problem with a calendar on some application from a client. It was a month calendar and for every day there were some conditions that needed to be verified: is it an holiday?, is it fully scheduled?, is there at least one person available that day?, and so on.

How was it working? Well, for every day it would query the database for each one of those conditions which would in the end sum up to 5 minutes since it was a not so small dataset.

So how could we fix this? I started with the usual approach, let's just try to get this done in one query and some joins. There was some improvement but it as a messy query full of joins and even some sub-queries. It took something like 20 seconds to load that calendar and that wasn't enough.

Next approach: divide the most complex joins and sub-queries in some really simple queries. Examples: get a set of all the holidays, get a set of all the fully booked days, etc. Then, when you loop to show the calendar you just check if the iterating day is on the holidays set, then on the next one until you find one that works; if you don't just leave it blank. This made the calendar loading right away.

Benefits: cleaner and (much) quicker code (and also easier to cache if you can, just cache each query individually since some of them don't change that often, like holidays).

Is it making any sense now?

jfb5• 16 years ago

#1 on my list: don't assume your application is the only one that will ever need access to your data. PUT YOUR DATA IN YOUR DATABASE.

#2: don't put stuff that aren't true facts into your database -- just because it's a reliable cross-process data store does NOT mean it's appropriate for all cross-process data sharing.

#3: don't fall in love with SQL. It fucking sucks.

wenbert• 16 years ago

Very good points. I have bookmarked the Stackoverflow post. I am glad that I had a good teacher for this subject in college. I really can't stress enough how important it is to design your database properly. I personally think that designing the database structure is one of the most difficult things to do when starting out a project. It is also the most fun :D Also, the satisfaction of optimizing your queries with just a few tweaks and seeing large amounts of improvements feels very good.

I love SQL and find it easy to solve problems with it. But I feel the opposite for ORMs. It adds another "learning barrier" for me. I hate it because sometimes I know how to solve a problem in SQL but have a really hard time solving it using the ORMs syntax. I have my doubts on it because at the start of the project you are supposed to define what DB you will be using.

Also, am I the only one who wasted countless hours trying to figure out how to execute a query using an ORM? While being absolutely sure how to do it with SQL?

This is my current situation. I am not that experienced and have a lot to learn. That is why I will be spending more time learning the ORMs I will be using and more hours figuring it out :)

Database development mistakes made by application developers

Related Stories