Hello Eric Kohnen ,

This is the first time i am visiting this site. You are doing a great job. Keep it up. Do you have archive of all these discussion? If yes where can i get them? That would be many times better than any book.

Is there any good book or website which tells how to write fast and efficient queries.

Thanks,

-Ranga

-- Anonymous, September 23, 1999

Answers

Ranga,

I do not have a separate archive of this forum. The forum, however, contains almost all postings since its inception 9/9/1997. I have only deleted out-of-date and non-SQL Server recruitment notices and the second copy of double posted questions.

No single book that I have stands out as offering comprehensive advice on efficient query construction. But, I just recently ran across a good 43 page article in TechNet titled, "MS SQL Server 6.5 I/O Performance Tuning Quick Reference" that has a number of useful tips, including proper use of indexes, Show Plan, Performance Monitor, and SQL Trace. Also read section 22 on join order importance, which includes the first lucid statement I have seen on a rule that I have absorbed but never quite stated. The author, Henry Lau, calls it the N/N-1 rule and it says that if you want to join N tables you need N-1 join conditions. Many a SQL developer, including myself, has forgotten to observe this rule. Section 23 on SQL to avoid will also help you to avoid some SQL traps.

Here are some SQL query coding hints from my own experience.

1. Have a copy of the data model to refer to when you write the query. This will help you choose the correct join conditions and the correct number of join conditions.

2. If need be, add indexes.

3. Don't try too hard to write everything in a single query. Often the only effective way to get the needed result is to create an intermediate table, index it, and use the intermediate table in the next step. Make sure that you account for the possibility, though, that your code could be called by 2 or more users simultaneously by using temporary tables when this can happen.

4. Checkpoint often. Consider writing a loop with a checkpoint in it, rather than one big query.

5. If you are updating a table, make sure your table is structured to allow an update in place.

6. Update statistics.

7. Look at the Show Plan.

9. If need be, add more tempdb and more locks.

10. Buy lots of RAM.

Hope this helps,

Eric

-- Anonymous, September 23, 1999