What are some references, lessons and or best practices for SQL optimization training.

Question

I know this falls into the black arts area of programming, but this is suddenly an area that I need to strengthen in my professional life. There are a couple of topics were this subject has been touched on but I'm not sure the item has been really addressed about how to become one with the system. For example: I became a phenomenally better C++ programmer when I began to understand how the compiler worked, I became a better software engineer when I understood how people worked. They are different study paths.

What I'm asking is if you were to put a self help course together to become better at not just writing syntactically correct SQL, and well normalized databases, but fast optimized ones as well. What would you focus on, who would you talk to, who would you read, and how would you get there?

Answer 1

I looked a long time for resources to help me understand exactly how the query optimizer worked, and how to really make sense of the query plans. I finally found this:

T-SQL Querying, by Itzik Ben-Gan

For Sql Server 2005, I haven't found anything else that gets close.

Answer 2

SQL Server Query Performance Tuning Distilled by Sajal Dam

Answer 3

Best 3 tips from my own experience:

• Index all columns you are going to join tables on
• Specify the fields you are actually going to retrieve, rather than "SELECT * FROM..."
• Use SQL aggregate functions rather than rolling your own. Common mistake: Opening a recordset and using a "Count" property of the recordset, rather than using "SELECT COUNT(*)..."

I know this isn't a full fledged resource or anything, but it will get you started.

Good web resource: http://www.sqlteam.com/tag/sql-server-performance-tuning

Answer 4

The Guru's Guide books are quite a good intermediate-advanced level SQL Server resource. In particular, the third one in this series has a fairly detailed architectural dissection of SQL Server 2000, which might give you the sort of insight you're looking for.

Answer 5

I am just learning more SQL for my job (I knew a little before, but am working on something which requires a lot of database interaction). I'm reading:

SQL for Smarties by Joe Celko, and the SQL Cookbook by Anthony Molinaro. If you were choosing one, I'd go with SQL for Smarties, as it has more theory in it and gives a broader view, whereas the cookbook is more "here is a specific problem and how you would solve it in each of the major db applications"

Answer 6

Learn BerkeleyDB. It will force you to learn the low level details of how databases work (to an extent). As someone with multiple years of SQL Server behind me, learning BDB took me to a new level. There is a ton of knowledge that translates across multiple platforms (i.e. locality of data, effects of various file formats, disk cluster specifics, etc).

I'll warn you that it's not for the faint of heart.

Also, make sure you pick up a book on BDB if you want to attempt this. Learning strictly from the reference material doesn't work so well.

Answer 7

I would check out The Art of SQL By Stephane Faroult and Peter Robson from O'Reilly. This gives a great lookinto principles of data normalization and thus optimization.

Answer 8

You need to learn to read the query plan. You need to know how to pseudo-code the inner workings of an inner loop, merge join, and a hash join, amongst others.

Query planning is a black art, but reading the plans is not so bad. Look at the plans for everything you are going to run (make sure that you have a representative size sample of data and current stats.) You might be able to make sense of what the black art is producing. Looking at the query plans quickly lets you know more about how the database works, and when it is going to do something less than efficient.

Take those inefficient points, and look at them. Look at the method it chose. An index may or may not change the plan (if it doesn't help, you can always drop it.) Learn the differences between B-Tree, R-Tree, and bit mask indexes, not just when they are recommended, but also how they are structured and how they work, so you will know WHY they are recommended.

The only real way I have found to learn it is to do it.

The only exception to the rule is Oracle 9 and newer sometimes chooses Merge Join Cartesian for no apparent reason. Once again, black art.

Answer 9

SQL Server Query Performance Tuning Distilled by Sajal Dam is a good book.

From my experience several things besides what have already been said come to mind. First avoid cursors at all costs especially in inserts, deletes, updates or triggers! Replace subqueries with derived tables. Denormalize if need be, especially if you need to join to two or three other large tables just to get something that you will need in every query such as client id. Write sargable queries (google this if you don't know what that is.) Check out whether a temp table, table variable or derived table will be faster. Avoid the use of functions.