Add Derived Data to Your DBMS [performance tuning] Strategy

Report
“Add Derived Data to Your DBMS
[performance tuning] Strategy”
Group 3
Andrew Hall
Zihong Huang
Relationship to our course: Performance tuning is the focus for weeks 2-6
We learned many tricks in chapters 17, 18, 19.
Derived data is another trick commonly used in Data Warehouses!
Motivation
● So why would derived data be needed in a
DBMS?
o
o
Performance (think of materialized views)
Quick responses
Citation: http://www.psdgraphics.com/file/hourglassicon.jpg
Types of Derived Data
●
●
●
●
●
Aggregates
We’ll talk about just this one
Text analytics
Calculated scores
ETL (extract, transform and load)
Adjusted data
Aggregation: Materialized Views
CREATE TABLE country (
name char(50),
year char(4),
population decimal(11),
primary key (name,year)
);
Traditional selection
with aggregates
Pre-computed aggregation
via views
CREATE VIEW Pop_View as
SELECT name, AVG(population)
average_population
FROM country
GROUP BY name;
SELECT name, AVG(population)
FROM country
GROUP BY name;
Better performance
if view is materialized!
SELECT *
FROM Pop_View;
Aggregates Examples
● Course Registration
o The available seats in a class
● Number of patients prescribed blood-thinning drugs
● Amount of Kemps milk sold at Cub Foods each month
● Total number of flights and the average percentage of filled seats in
those flights
Companies/Products Supporting
Materialized Views
• Oracle
• PostgreSQL
• IBM DB2 (materialized query tables)
• Microsoft SQL Server (indexed views)
Questions?
References
1.
2.
3.
Monash, Curt. “Add Derived Data To Your DBMS Strategy.” InformationWeek. N.p., n.d. Web. 10 Feb. 2015.
http://www.spatial.cs.umn.edu/Courses/Spring15/5708/labs_2015/country.sql. Web. 10 Feb. 2015.
“Materialized View.” Wikipedia, the free encyclopedia 31 Jan. 2015. Wikipedia. Web. 11 Feb. 2015.

similar documents