LESSON-11 TIPS ON DENORMALIZATION OF TABLES (Cont’d; Ashraf, Khan, and Mir)
LESSON-11 TIPS ON DENORMALIZATION OF TABLES (Cont’d; Ashraf, Khan, and Mir)
11.1 Performance Considerations:
a. Recognize the factors that affect performance
b. To be able to spot critical performance bottlenecks
c. To be able to apply some of the techniques that improve performance
When to de-normalize:
a. Long access paths: multiple joins, aggregation of low level data
b. Leading to: excessive I/O usage, excessive response time
c. De-normalized enduser views: setups, maintenance
d. Possible excessive storage requirements
Un-normalized data structures will also cost:
a. Extra Data Maintenance: update, insert, delete
b. Possible inconsistencies
c. Excessive storage requirements
d. Loss standard of instance MIS of retrieval supports
What time to De-normalize:
POINT NO-1:
WHY? Reduce Joins leading to; reduced I/O, reduced CPU
WHAT?
a. Infrequently – updated items
b. Items accessed consistently whenever a different table is referenced
HOW To Identify?
a. From Object (Table) form end of a relationship into Object (table) at ‘Many’ End
c. Column (attribute) usage statistics, frequency of co-retrieval and frequency of updates.
De-normalization:
a. Making Joins efficient: reduce joins leading to reduce I/O, and CPU
b. What to de-normalize: column accessed consistently whenever another object (table) is referenced
c. Columns duplicated from one object (table) at ONE end of relationship in table at MANY
d. Look at application / column usage information for frequency of co-retrieval/ update.
POINT NO-2:
a. WHY? Reduce Aggregation
b. How? Maintain summary data
What?
a. Frequently aggregated items
Note: If aggregation is periodic consider object (table) populated when required
How To Identify?
a. Summarize object (table) at ‘MANY’ End of a relationship into Object table) at ‘ONE’ End
b. Column (attribute) usage statistics, frequency of co-retrieval and frequency of updates
De-normalization:
a. Reducing aggregation; maintain summary data
b. What to de-normalize; frequently aggregated items
Note: If aggregation is periodic, consider a temporary table, populated when required
c. Summarize column information at MANY End of relationship in
object(table) at ONE End.
*****
Click To MAIN PAGE: OBJECT- ORIENTED RELATIONAL MODELLING E-MONOGRAPH, By J. Ashraf, M. Khan, and H. Mir
*****
MESSAGE: DEDICATED TO ANONYMOUS COMBATANTS OF KNOWLEDGE
مکتب علم الل مہد منل لحد Learning continues from birth to death
FOR PROMOTION OF LEARNEDNESS SHARE WITH FRIENDS ABOUT;
- CAUSE AND EVENTS – SPATIAL And TEMPORAL RESPECTIVELY: https://be4gen.wordpress.com/2012/11/16/cause-and-events-spatial-temporal-respectively/
- WHETHER ‘BRAIN-POWER’ OR ‘HEART-IMPULSE’ PLINTHS ‘HUMAN GEN’? http://sunedu.wordpress.com/2012/09/17/whether-brain-power-or-heart-impulse-plinths-human-gen/
************
No comments:
Post a Comment