Transcript Optimization_Denormalization

Dr. Djamal Ziani
Professor Assistant
King Saud University



Understand and refine the requirements
Example: A manager ask you to write a
statistical report about the professors.
Assume you have the following model:


SELECT lname, fname, salary, prof_rank, dept_name, school_name,
cont(stu_num)
FROM Professor AS P, Employee AS E, Department AS D, School AS S,
Class AS C, Enrol AS R
WHERE E.emp_num = P.emp_num
(1)
AND
D.dept_code = P.dept_code
(2)
AND
S.school_code = D.school_code (3)
AND
C.emp_num = P.emp_num
(4)
AND
R.class_code = C.class_code
(5)
Select prof_rank, count(emp_num), AVG(salary)
FROM Professor AS P, Employee AS E
WHERE E.emp_num = P.emp_num
(1)
GROUP BY prof_rank


Understand well the requirements
Have deep details on the requirements (what exact fields we want to
display, what calculation to do, what frequency, …)

To whom is dedicated the work (Manager, employee, Director, user)

Get the requirements written and sign off by the requestor.




Before you jump in the development, try to be familiar with the ER
and physical models.
Ask always the WHY question to understand well the models.
Write all your remarks and questions and send them to the
responsible.
Write all the changes that you can do to improve the models.


Tracing
Tune the SQL
◦ Performance statistics
◦ The properties of Objects such as tables, clusters, indexes, and
views
◦ The join order selected by the optimizer
◦ Degree of Parallelism

Distribution
Tune the access paths
Tune the memory
Tune the I/O and physical structure
Tune the resource contention

.





Are there missing indexes (create new ones)

Are the existing indexes used, if not why

Are some indexes need to be rebuild (e.g deleted entries

Force the optimizer to use an index
represent 20%)

View is a virtual table we can create view by using following query Create
view view_name as select * from table_name

We can select data from view by using following query.select * from
view_name;

Views have a useful role to play in any application and can be used to help
improve performance

Example



CREATE VIEW cur_mth_sales AS
SELECT isbn, sum(sales_val) total_sales
FROM cur_mth_transactions
GROUP BY ISBN;
SELECT name, total_sales FROM cur_mth_sales;



In its simplest form, "staging" involves putting something in a specific location to be
accessed at a later time
CREATE TABLE new_table
AS SELECT select_list
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
Example
To do an annual report of sales:
◦ Create a staging table containing the sales of a month
◦ Each month update this staging table, by a script or a procedure
◦ At the end of year the staging table will contain all necessary data for the report, and
the report we be very fast.

A denoramlized database is a way where you keeps redundant
frequently-accessed data, duplicated across several tables, by
grouping or partitioning data, making it possible to fulfill a user
request without having to look at more than a single table.

Add some calculated field in tables

Add constraints to insure that the redundant copies of information are
kept synchronized

speeding up reads (SELECT in SQL) while slowing down writes
(INSERT, UPDATE, and DELETE).
This example design is for a mail order
company that has 120,000,000 customers to
whom they must send catalogs
Horizontal
Partitioning
Vertical
Partitioning

Add a calculated field in a table.

Example


We want to provide an annual report for companies which
includes their outcomes
Our database includes the order data for 1000 companies.
OUTCOME