In this chapter, you learned about:
This chapter linked the theory presented in previous chapters with case study and database modeling in practice, to be presented in chapters to follow this chapter. This linking process has been done using the most critical factor to all database installations -- performance. If the database and its attached applications do not perform up to scratch, end-users will not be happy, and will probably go elsewhere, or find another employee to replace you. Performance tuning is a highly complex topic and this chapter has barely scratched the surface. The intention of this chapter has been to attempt to give a very brief mental impression of how database modeling theory might be applied in the real world, perhaps attempting to tweak a subconscious change in your train of thought, as you read through this book. In other words, the approach in this chapter is changing the direction of this book from theory into practice, in small steps.
The idea is to make it all a little easier to absorb.
Answer the following questions:
1. Which of these apply to OLTP databases?
a. Large transactions
b. High concurrency
c. Frequent servicing opportunities
d. Real-time response to end-users
2. Which of these apply to data warehouse databases?
a. Lots of users
b. High concurrency
c. Very large database
d. High granularity
3. Which aspect of a query affects performance most profoundly? Select the most appropriate
a. WHERE clause filtering
b. Sorting with the ORDER BY clause
c. Aggregating with the GROUP BY clause
d. The number of tables in join queries
e. The number of fields in join queries
4. Assume that there 1,000,000 records in a table. One record has AUTHOR_ID = 50. AUTHOR_ID as
the primary key. Which is the fastest query?
a. SELECT * FROM AUTHOR WHERE AUTHOR_ID != 50;
b. SELECT * FROM AUTHOR WHERE AUTHOR_ID = 50;
The above tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.
This was first published in April 2006