Databases Reference
In-Depth Information
and the cost calculated from the elapsed time and the hourly rate. This information is
useful for comparing the actual cost versus the sell value of each job, which can lead to
process improvement and better profits.
For the calculations that follow, let's assume the following statistics about our data-
base:
•
Average jobs per day = 10,000.
•
Average invoices per day = 5,000.
•
Average Job_Costing rows per job = 50.
•
Days on record = 200.
•
Customers = 5,000.
•
The rows in all tables are clustered in primary key order.
•
Anything 200 MB (i.e., 50,000 blocks) or less can be sorted in memory.
We continue to utilize the I/O time formulas introduced in Appendix A. Our cal-
culations in this chapter are based on the IBM U320 146 GB hard drive.
•
I/O time (4 KB block in a dedicated disk) = 2.0 ms.
•
I/O time (64 KB buffer in a dedicated disk) = 2.2 ms.
•
I/O time (4 KB block in a shared disk) = 5.6 ms.
•
I/O time (64 KB block in a shared disk) = 5.8 ms.
Table scans should assume the use of prefetch buffers. Even though the individual
I/O time is slightly longer for 64 KB than 4 KB I/O transfers, the 64 KB transfer only
has to be executed 1/16 as many times, so the overall efficiency is almost 16 times better.
The management of the company likes to keep track of job profitability. Informa-
tion Services has provided an excellent profit analysis application. Typically the applica-
tion is run by 10 managers per day. The application requires the cost, sell, and profit for
each job. The query to obtain the data from the existing database follows:
SELECT c.job_id, sum(c.cost) AS cost, sum(li.sell)
AS sell,
sum(li.sell) - sum(c.cost) AS profit
FROM Job_Costing AS c, Line_Item AS li
WHERE c.job_id = li.job_id
GROUP BY c.job_id;
Let's calculate the time for this query, and then we'll improve the performance
using a materialized view. The query involves the Line_Item and the Job_Costing
tables. We need to calculate the row quantity, blocking factor, and number of blocks for
