AnalyticDB:Query scenarios

Scenario 1: Online report query

• Background information

  In online report query scenarios, typical analysis and computing requirements include a query of top N results from a single table or multiple joined table, predicate filtering, and aggregation. In this example, a decision analysis system for the retail industry is used to demonstrate the performance of AnalyticDB for MySQL. The data model is implemented based on TPC-H. For more information, see TPC-H test queries.

• Test environment

  AnalyticDB for MySQL Enterprise Edition clusters:

  • Single-node specifications of reserved resources: 8 AnalyticDB compute units (ACUs)

  • Number of reserved resource nodes: 12

The test is performed on 1 TB of data.

• Test results

  • Execute the following statement to query the information about the undelivered orders that are sorted by revenue. The report data can be used to track order delivery.

    select  
        l_orderkey,  
        sum(l_extendedprice*(1-l_discount)) as revenue,  -- Aggregation: Calculate the potential revenues. 
        o_orderdate,  
        o_shippriority  
    from  
        customer, orders, lineitem
    where  
        c_mktsegment = 'MACHINERY'     -- Perform predicate filtering.
        and c_custkey = o_custkey  
        and l_orderkey = o_orderkey  
        and o_orderdate < date '1995-03-23'
        and o_orderdate > date '1995-02-23' -- Predicate filtering: Specify a time range.
        and l_shipdate > date '1995-03-23'  
        and l_shipdate < date '1996-03-23'
    group by -- Perform grouping operations.  
        l_orderkey, -- Specify an order identifier.  
        o_orderdate, -- Specify an order date.  
        o_shippriority -- Specify a delivery priority.  
    order by 
        revenue desc, -- Sort the orders in descending order of potential revenues.  
        o_orderdate;
    limit 
        100;

    The following table describes the test result.

    Service	Time consumed
    AnalyticDB for MySQL	0.9s

  • Execute the following statement to query the revenues of component suppliers in a specific region. The report data can be used to determine whether a distribution center needs to be constructed in a specific region.

    select
            n_name,
            sum(l_extendedprice * (1 - l_discount)) as revenue  -- Aggregation: Calculate the potential revenues. 
    from
            customer,
            orders,
            lineitem,
            supplier,
            nation,
            region  -- Join the six tables.  
    where
            c_custkey = o_custkey
            and l_orderkey = o_orderkey
            and l_suppkey = s_suppkey
            and c_nationkey = s_nationkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = 'EUROPE'  -- Predicate filtering: Specify a region.
            and o_orderdate >= date '1996-01-01'  -- Specify a time range.
            and o_orderdate < date '1996-01-01' + interval '1' year
            and l_shipdate > date '1996-02-23'  
              and l_shipdate < date '1996-03-23'
    group by
            n_name
    order by  -- Sort the suppliers in descending order of potential revenues. Take note of the difference between the GROUP BY and ORDER BY clauses.  
            revenue desc
    limit 10

    The following table describes the test result.

    Service	Time consumed
    AnalyticDB for MySQL	1.0s

Scenario 2: Interactive analysis

• Background information

  In interactive analytics scenarios, business personnel perform exploratory data queries by using business intelligence (BI) tools or self-service query platforms. The query statements must support multi-dimensional combinations, multi-table joins, aggregate queries, subqueries, and filter conditions. The test in interactive analysis scenarios is implemented based on TPC-H. For more information, see TPC-H test queries.

• Test environment

  AnalyticDB for MySQL Enterprise Edition clusters:

  • Single-node specifications of reserved resources: 8 ACUs

  • Number of reserved resource nodes: 12

• Test result (test data: 1 TB)

  • TPC-H query	Time consumed
    Total	107.41s

Scenario 3: Data warehouse extract, transform, load (ETL)

• Background information

  • In ETL scenarios, data is cleansed, transformed, and processed in batches in data warehouses. ETL scenarios are characterized by large amounts of scanned data, large numbers of associated tables, complex computational logic, and long computing time. The test in ETL scenarios is implemented based on TPC-DS. For more information, see TPC-DS test queries.

• Test environment

  AnalyticDB for MySQL Enterprise Edition clusters:

  • Single-node specifications of reserved resources: 8 ACUs

  • Number of reserved resource nodes: 12

• Test result (test data: 1 TB)

  • TPC-DS query	Time consumed
    Total	397.19s