Databases Reference
In-Depth Information
Let's recap:
Disk I/O time before Profit_by_Job
= query frequency
×
I/O time per query
×
(478 sec/query)
= 4,780 seconds (or about 1 hour, 20 min)
Disk I/O time with Profit_by_Job
= creation cost + query frequency
= (10 queries/day)
×
I/O time per query
= 183 sec creation cost + (10 queries/day)
×
(5 sec/query)
= 233 seconds.
Utilizing materialized views can bring marked improvement in both total disk I/O
and query response. Some gain derives from taking advantage of a dedicated disk to
generate the view, but the lion's share comes from two factors. First, the materialized
view may be much smaller than the base tables, leading to large gains in disk I/O perfor-
mance per query. Second, frequent queries multiply the gain.
5.2
Exploiting Commonality
There are a number of frequent queries that look at profitability from different perspec-
tives. Trends over time can yield valuable insights, triggering decisions that curtail prob-
lems and provide a brighter future. Another useful analysis is to track profitability by
customer. For example, if we know who our most profitable customers are, we can
solicit more jobs by offering bulk discounts if they agree to bring some minimum num-
ber of jobs within a fixed timeframe. We examine the possibility of materializing two
more materialized views to quickly answer these queries. Then we look at an alternative
materialized view that takes advantage of the commonality of our three profitability
queries.
The trends in profitability by date are looked over briefly once a day by upper man-
agement and also the accounting manager. We determine that the query is typically run
five times a day. The profitability by invoice date query when posed against the base
tables is as follows:
SELECT i.invoice_date, sum(c.cost) AS cost, sum(li.sell)
AS sell,
sum(li.sell) - sum(c.cost) AS profit
FROM Invoice AS i, Job_Costing AS c, Line_Item AS li
WHERE i.invoice_id = li.invoice_id
AND c.job_id = li.job_id
GROUP BY i.invoice_date;
