1 / 21

Database Performance

Database Performance. Database Performance. Perfomance can be improved through : Denormalisation Optimisation of SQL Indexes Clusters. Database Performance. DENORMALISATION Normalisation improves the logical database design and prevents anomalies BUT More tables more joins

petra
Download Presentation

Database Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Performance

  2. Database Performance Perfomance can be improved through: • Denormalisation • Optimisation of SQL • Indexes • Clusters

  3. Database Performance DENORMALISATION • Normalisation improves the logical database design and prevents anomalies BUT • More tables more joins • Joining > 3 tables is likely to be slow • Denormalisation reverses normalisation for efficiency purposes

  4. Database Performance SQL OPTIMISATION Select * from ...; DBMS DATA FILES

  5. Database Performance INDEXES An index is a table or some other data structure that is used to determine the location of a row within a table that satisfies some condition. • Indexes may be defined on both primary and non key attributes.

  6. Database Performance INDEXES • oracle allows faster access on any named table by using an index. • each row within a table is given a unique value or rowid. • each rowid can be held in an index. • an index can be created at any time. • any column within a table can be indexed.

  7. Database Performance WHEN TO CREATE AN INDEX Beforeany input of data for Unique index Afterdata input for Non-unique index

  8. Database Performance HOW DO YOU CREATE AN INDEX ? EXAMPLE :- (a) CREATE INDEX TENIDX ON TENANT(SURNAME); (b) CREATE UNIQUE INDEX TENIDX ON TENANT(SURNAME);

  9. Database Performance GUIDELINES FOR USE OF INDEXES • > 200 rows in a table • a column is frequently used in a where clause • specific columns are frequently used as join columns

  10. Database Performance POINTS TO WATCH • avoid if possible > 3 indexes on any one table • avoid indexing a column with too few distinct values For example:- male/female • avoid indexing a column with too many distinct values • avoid if > 15% of rows will be retrieved

  11. Database Performance CLUSTERS • A disk is arranged in blocks • Blocks are retrieved as a whole and buffered • Disk Access time is slow compared with Memory access • Gains can be made if the number of block transfers can be reduced

  12. Database Performance CLUSTERING • clusters physically arrange the data on disk so that frequently retrieved info is stored together • allows 2 or more tables to be stored in the same physical block • can greatly reduce access time for join operations . • can also reduce storage space requirements.

  13. Database Performance CLUSTER DEFINITION • clustering is transparent to the user no queries have to be modified no applications need to be changed • tables are queried in the same way whether clustered or not

  14. Database Performance DECIDING WHERE TO USE CLUSTERS • Each table can only reside in 1 cluster • At least one attribute in the cluster must be NOT NULL • Consider the query transactions in the system • How often is the query submitted • How time critical is the query • What’s the amount of data retrieved

  15. Database Performance HOW TO CREATE A CLUSTER Step 1 create cluster tenpay(tenid char(8)); Step 2 alter cluster tenpay add table tenant where tenpay.tenid = tenant.property_id; Step 3 alter cluster tenpay add table payment where tenpay.tenid = payment.payment_id

  16. Database Performance • CLUSTERING EXERCISE WAREHOUSE STOCK 3 PRODUCT 1000

  17. Database Performance • To speed up access time to data in these three tables (WAREHOUSE, PRODUCT, STOCK) it is necessary to cluster either STOCK around WAREHOUSE, or STOCK around PRODUCT. • How do we decide which will be the most efficient? • For the purpose of this exercise we will assume that each block can hold 100 records.

  18. Database Performance If STOCK is clustered around PRODUCT No of products = 1000. There will be 1 record for each PRODUCT in each WAREHOUSE. Therefore each product would have 3 records • Each block would contain 100/3 products, i.e. 33 products. There would therefore be a 1 in 3 chance of accessing a particular stock item by reading one block of data.

  19. Database Performance If STOCK is clustered around WAREHOUSE • No of warehouses = _____. There will be ____ record for each item of STOCK in each warehouse. Therefore each warehouse would have ______ records. The records for each warehouse would have to be stored across ______ blocks. • Access would therefore be faster if STOCK is clustered around the product.

  20. Database Performance OPTIMIZING PERFORMANCE Performance can be regarded as a balancing act between:- • access performance • update performance • ease of use/modification

  21. Further Reading • Query Optimisation • Connolly & Begg, 3rd edition, chapter 20 • Connolly & Begg, 4th edition, chapter 21 • Performance Tuning (including denormalisation) • Connolly & Begg, 3rd edition, chapter 17 • Connolly & Begg, 4th edition, chapter 18 • Next Week • HCI

More Related