I used several books to study for the ICCP DBA exam including:
- Modern Database Management, 8th Edition by Hoffer, Prescott and McFadden
- Database Systems: Design, Implementation & Management 6th Edition by Rob and Coronel
- Database Administration: The complete Guide to Practices and Procedures by Craig S. Mullins
- An Introduction to Database Systems 5th Editon by C.J. Date
Of these, Modern Database Management was my favorite. Chapter 6 of this book covers physical database design and performance. There is a section in this book that has guideline for improving query performance. Here are a few of the recommendations:
- Use compatible data types, i.e., avoid type conversions
- Write simple queries
- Break complex queries into multiple, simple parts
- Dont nest one query inside another (just because you can use a subquery doesnt always mean that you should)
- Dont combine a table with itself, i.e. avoid self-joins.
- Create temporary tables for groups of queries.