Book Excerpt

Database modeling exercises

The following 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.

In this chapter, you learned about:

  • Factors affecting tuning of OLTP database models
  • Factors affecting tuning of client-server database models
  • Factors affecting tuning of data warehouse database models
  • Writing efficient queries using the SELECT command, the WHERE clause and joins
  • Auto counters and surrogate keys
  • The type of indexes useful for performance
  • Index types and how they apply in reality
  • When not to use indexing
  • Performance problems caused by views down the road
  • Application caching used to reduce database demand and network activity
  • 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.

    Exercises

    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 answer.
    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

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: