For Dummies 978-0-470-92996-4 Scheda Tecnica Scaricare il pdf

Chapter 1: Tuning the Database

In This Chapter

✓ Examining the work to be done

✓ Contemplating physical design considerations

✓ Choosing and clustering indexes

✓ Co-clustering two relations

✓ Indexing on multiple attributes

✓ Tuning indexes, queries, and transactions

✓ Query tuning in a high-concurrency environment

✓ Benchmarking

✓ Keeping user interactions separate from transactions

✓ Holding down traffic between application and server

✓ Creating a precompiled list of frequently used queries

he word tuning is generally taken to mean optimizing an existing system

that isn’t operating at top capacity. Tuning doesn’t do you much good,

however, if your initial design isn’t at least close to optimal in the first place.

Tuning can take you only so far from your starting point. It’s a lot easier to

tune a slightly off-pitch B string on your guitar to a perfect B than it is to

tune a G string up to a perfect B. (Also, you’re a lot less likely to break the

string.) Tuning for optimal performance should start in the initial design

stage of a database, not at some later time when design decisions have been

cast in concrete.

The performance of a database management system (DBMS) is generally

judged by how fast it executes queries. Two types of operations are impor-

tant: the retrieval of data from a database and the updating of records in a

database. The speed at which records can be accessed is key to both types

of operations, because you must locate a record before you can retrieve or

update the data in it. The users’ data model on which you’ll base your data-

base design is almost certainly structured in a way that isn’t the best from a

performance standpoint. The users are primarily concerned with function-

ality and may have little or no idea of how the design of a database affects

how well it performs. You must transform the users’ data into a conceptual

schema that you actualize in the form of an Entity-Relationship (ER) model

diagram. Recall that the Entity-Relationship data model and its associated

diagrams are extensively covered in Book II.

40_9780470929964-bk07ch01.indd 57940_9780470929964-bk07ch01.indd 579 2/24/11 3:45 PM2/24/11 3:45 PM

COPYRIGHTED MATERIAL

1 2 3 4 5 6 ... 11 12

Sommario

Pagina 1 - COPYRIGHTED MATERIAL

Chapter 1: Tuning the DatabaseIn This Chapter✓ Examining the work to be done✓ Contemplating physical design considerations✓ Choosing and clustering in

Pagina 2 - Analyzing the Workload

588 ✦ An OR connective in the WHERE clause could cause a problem. ✦ If you expect the optimizer to select a fast but sophisticated, plan, you could be

Pagina 3

Book VIIChapter 1Tuning the Database589Separating User Interactions from TransactionsBecause computer instructions operate in the nanosecond realm and

Pagina 4 - Choosing the Right Indexes

590Book VII: Database Tuning Overview40_9780470929964-bk07ch01.indd 59040_9780470929964-bk07ch01.indd 590 2/24/11 3:45 PM2/24/11 3:45 PM

Pagina 5 - Clustering indexes

580Analyzing the WorkloadOptimal design of a database depends largely on how the database will be used. What kinds of queries will it be subjected to?

Pagina 6

Book VIIChapter 1Tuning the Database581through the table until you come upon the record you want. This feature can be a tremendous time-saver for a qu

Pagina 7 - Choosing an index type

582Choosing the Right IndexesIndexes can improve the performance of database retrievals dramatically, for several reasons. One reason is that an index

Pagina 8 - Tuning Indexes

Book VIIChapter 1Tuning the Database583on EMPLOYEE.DepartmentID usually performs best. The number of rows in the EMPLOYEE table is sure to be larger t

Pagina 9 - Tuning Queries

584Consider the following example:SELECT DeptNoFROM EMPLOYEEWHERE EMPLOYEE.Age > 29 ;You can use a B+ tree index on Age to retrieve only the rows i

Pagina 10 - Tuning Transactions

Book VIIChapter 1Tuning the Database585Figure 1-1 reveals a few things about the cost of indexes: ✦ A clustered index always performs better than an u

Pagina 11 - Chapter 1

586If you suspect that an index might be doing you more harm than good, run some test queries with the index both present and absent. Use the results

Pagina 12

Book VIIChapter 1Tuning the Database587For best results, tuning indexes must be an ongoing activity. As time goes on, the nature of the workload is bo

Commenti su questo manuale

Nessun commento

For Dummies 978-0-470-92996-4 Scheda Tecnica

Navigare online o scaricare Scheda Tecnica per Manuali per software For Dummies 978-0-470-92996-4. For Dummies SQL All-in-One Manuale Utente

Sommario

Commenti su questo manuale

Prodotti e manuali riguardandi Manuali per software For Dummies 978-0-470-92996-4