Optimizing Aggregate Query Processing in Cloud Data Warehouses - Data Management in Cloud, Grid and P2P Systems - page 9

using the same partition key that was used to partition the data. We then dis-
tributed these partitions into all 50 machines that contained corresponding data.
All the map structures are loaded into the main memory before each experiment
begins. Here we can take advantage of main memory databases if the data size
is huge (in Petabyte or Exabyte) [1] [10] [11].
We used 5 queries for the performance analysis as shown in Table 3. Com-
parison of time taken by these queries is shown in Figure 5a to 5e. In graphs of
these figures, PlanetLab machines are on the x-axis and time taken by the query
(in seconds) is on the y-axis.
On each graph, top line shows the result of processing aggregate queries on
MySQL, and the bottom line shows the result of our framework. For simplicity
of explanation, we do not consider pipelined approach to count the number of
communications between the machines. Instead we use inter query operation
communications.
Figure 5a compares the time taken for processing Query 1. As explained in
Section 3.3.1, Query 1 has an aggregate operation on primary key of table OR-
DERS, and a Non-Primary Key/Non-Foreign Key (NPK/NFK) constraint on
table NATION. Hence, it is sucient to scan NATION table, NationKey-map,
and CustomerKey-map to answer this query.
Figure 5b compares the time taken for processing Query 2. In Query 2, we
have an aggregate operation COUNT(*), and a NPK constraint on attribute
Tabl e 3. Performance Evaluation Queries
Query 1 : (COUNT) Find the total number of orders placed by the 'UNITED STATES' and 'IN-
DIA' customers (aggregation on primary key attribute)
select N.name, count(*)
from ORDERS O, CUSTOMER C, NATION N
where N.name = ”USA” or N.name = ”INDIA” and
N.nationkey = C.nationkey and C.custkey = O.custkey
group by N.name;
Query2 : (COUNT) Find the total number of failed orders placed by customers of each nation.
I.e, orders whose ORDERSTATUS = 'F' (aggregation on NON - Primary Key attribute)
select N.name, count(*)
from ORDERS O, CUSTOMER C, NATION N
where N.nationkey = C.nationkey and C.custkey = O.custkey and O.orderstatus = 'F'
group by N.name;
Query 3 : (MIN) Find the minimum supply cost for all the parts supplied by 'UNITED STATES'
suppliers
select PS.partkey, min(PS.supplycost)
from PARTSUPP PS, SUPPLIER S, NATION N
where N.name = ”USA” and N.nationkey = S.nationkey and S.suppkey = PS.suppkey
group by PS.partkey;
Query 4 : (SUM) Find the revenue generated by customers of each nation in the year 1995. I.e.,
revenue is equal to the totalprice from the orders table
select N.name, sum(O.totalprice)
from ORDERS O, CUSTOMER C, NATION N
where N.nationkey = C.nationkey and C.custkey = O.custkey and O.orderdate like '1995%'
group by N.name;
Query5 : (AVG) Find the average revenue generated by customers of each nation in year 1995.
I.e., revenue is equal to the totalprice from the orders table
select N.name, avg(O.totalprice)
from ORDERS O, CUSTOMER C, NATION N
where N.nationkey = C.nationkey and C.custkey = O.custkey and O.orderdate like '1995%'
group by N.name;