The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Optimisation

Are there any good guides out there which explain how to improve database performance (not necessary proprietary changes)? I am looking for something to explain different ways of improving general performace for large amounts of data, how to structure a good SQL query, indexing tables etc.
Tuesday, January 23, 2007
I could start a list of the things I do - I'm sure you could, too.  But I've never come across a good book on the subject - I'll be interested in any good references!
Mike S Send private email
Tuesday, January 23, 2007
I vaguely remember seeing some excellent chapters in my collegate database theory textbook. If you're really interested, I can go look for the book, but I suspect whatever's taught in your local University will be good enough.

The reason I'm being vague is because optimization is really something unique to the specific circumstances - it's like the warning against doing premature optimization of code. As such, I'm not sure you're going to find a good "cookbook" that covers every reasonable case. But the textbook will teach you the theories and formula (database calculus) such that you can decide for yourself what needs to be optimized and how.

The next best thing is to just ask us for general best practices. Three of mine are...

1) 3NF is not the best fit for every situation. If you need to write a lot of custom queries "on the fly" and you see yourself joining tables a lot, it actually can improve performance to join them at the schema level, at the expense of relational integrity.

2) If you have a relatively small "lookup" table, such as a list of the 50 states, it may be faster to a) cache the table in the application or b) merge the state names into the main data tables and use constraints to ensure data integrity rather than foreign keys.

3) Read the user's manual for your database and learn how to review an "explain plan". Most database are fully capable of collecting statistics on how long it takes to run queries, and generating profiles that show where the bottlenecks are. This is probably the easiest, best way to determine which columns should be indexed and which shouldn't be.
Tuesday, January 23, 2007
See "Database Tuning" by Dennis Shasha and Philippe Bonnet.
Paul Mansour Send private email
Tuesday, January 23, 2007
Another good one is "The Art of SQL" by Stephane Faroult and Peter Robson
Rua Send private email
Tuesday, January 23, 2007
"The Art of SQL" is a good one, yes -- at times a bit too fundamental, but since the fundamentals are good ones, it's definitely worth a read.  Dan Tow's "SQL Tuning" is excellent as well; I use it often.  Both have proven indispensible.  Both highly recommended.
Chris Nunciato Send private email
Tuesday, January 23, 2007
It's very difficult to set rules on how to optimise structures and SQL. The principle is really to do the least amount of work possible, which sometimes means using an index and sometimes means not uaing one. The key to database performance, IMHO, is to be as familiar as possible with the individual RDBMS(s) you're using and to leverage the available features. For example in Oracle the use of analytic functions and subquery factoring clauses can each improve performance in appropriate circumstances by a significant factor.
David Aldridge Send private email
Wednesday, January 24, 2007
I'm not a DB guru, but "High Performance MySQL" from O'Reilly ( seemed to me to have some good examples ranging from servers and network preformance to queries. At least some of this would be relevant to other SWL-based DBs as well.
Wednesday, January 24, 2007
(Sorry; typo: "SWL" should of course have been "SQL")
Wednesday, January 24, 2007
My list would be :-

1. Setting for the optimum buffer size, timeouts, memory and lock settings
2. Whether you want to use an n-tier client server structure to utilise idle servers
3. Asyncronous calls compared to queing
4. Stored procedures

1. Intelligent use of indexes / good database design
2. Intelligent use of hash tables
3. If it's a simple list, write it up directly in code - it's much faster!!!
4. Optimised SQL form (eg. using the best JOIN whether it's INNER or OUTER, LEFT or RIGHT, etc.)
5. Minimized database access (consider disconnected recordsets)
6. Clever use of caching and cache expiries
Ezani Send private email
Thursday, January 25, 2007
This is one of those times where running an industrial-strength database like Oracle comes in handy. You can tune the heck out of any query. The database, tools and documentation are there, and free (using XE). I really don't know how a book could be so general as to say "this is the most efficient way to do this" for any DBMS. At best they could say "this is a really bad idea" for any DBMS.
Friday, January 26, 2007
95% of optimization is tweaking SQl and first thing you want to do is to cut number of reads from database:
 * use parameters (bind variables) in sql to reduce parsing overhead
 * check index usage for queries on single table (add/modify index)
 * redesign join queries to make your database do first read on most selective criteria
 * try to replace number of small reads with one large read
This 4 advices cover about half of each book on SQL optimization and they will speed up 90% of your application, no matter what database.
Saturday, January 27, 2007
Check out the "Most Popular SQL Server Tips" at the bottom of the home page.  A lot is specific to SQL Server, but I'm sure a lot translates over as well.
Mick Send private email
Tuesday, January 30, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz