This topic describes how to use a single-table dataset based on the Star Schema Benchmark (SSB) to perform performance testing on Hologres and ClickHouse. This topic also compares the test results.

Background information

The SSB is a star schema test set widely used in academia and industries. This test set is used to compare the basic performance metrics of various online analytical processing (OLAP) systems. ClickHouse converts the star schema of the SSB to the flat schema and creates a wide table to generate a single-table test set. For more information, see Star Schema Benchmark on the ClickHouse official website.

This topic describes how to use the single-table dataset based on the SSB to perform performance testing on Hologres and ClickHouse. The following figure shows the test results.

Test results
  • Among the 13 queries on a single table, Hologres is faster than ClickHouse in 11 queries.
  • In the 13 queries on a single table, the total amount of time used in ClickHouse is 1.35 times that in Hologres.

Test environments

To eliminate the impact of the network bandwidth, this performance testing uses the same Elastic Compute Service (ECS) instance to send query requests to Hologres and ClickHouse in a virtual private cloud (VPC). The result caching feature is disabled in Hologres. The following tables describe the specific test environments.

  • Test environment in ClickHouse
    Item Description
    Server An ECS instance
    CPU Intel Xeon (Ice Lake) Platinum 8369B, 64 vCPUs
    Memory 256 GiB
    Internal bandwidth 32 Gbit/s
    Disk Enhanced SSD (ESSD), 200 GB, PL1, the maximum IOPS of 50,000 per disk
    Operating system CentOS 8.4 64-bit
    Hardware fee (excluding the Internet bandwidth) CNY 9,032 per month
    Internet bandwidth over IPv4 200 Mbit/s
    Network traffic fee CNY 15,725 per month
    ClickHouse version V21.8.3.44
  • Test environment in Hologres
    Item Description
    Computing resources 64 CPU cores and 256 GB of memory
    Storage resources Logical storage: 200 GB
    Internet bandwidth Greater than 5 Gbit/s
    Total fee CNY 11,080 per month
    Hologres version V0.10.33

Test data

Table name Rows of data Description
lineorder 0.6 billion The commodity order table of the SSB.
customer 3 million The customer table of the SSB.
part 1.4 million The part table of the SSB.
supplier 0.2 million The supplier table of the SSB.
dates 2,556 The date table of the SSB.
lineorder_flat 0.6 billion The wide table of the flat schema based on the tables of the SSB.

SQL statements

  • SQL statements in ClickHouse

    The DDL statements and query statements used are the same as those provided on the ClickHouse official website. For more information, see Star Schema Benchmark on the ClickHouse official website.

  • SQL statements in Hologres
    • DDL statements
      DROP TABLE IF EXISTS lineorder_flat;
      
      BEGIN;
      CREATE TABLE IF NOT EXISTS lineorder_flat (
        lo_orderdate     date NOT NULL ,
        lo_orderkey      int NOT NULL ,
        lo_linenumber    int NOT NULL ,
        lo_custkey       int NOT NULL ,
        lo_partkey       int NOT NULL ,
        lo_suppkey       int NOT NULL ,
        lo_orderpriority text NOT NULL ,
        lo_shippriority  int NOT NULL ,
        lo_quantity      int NOT NULL ,
        lo_extendedprice int NOT NULL ,
        lo_ordtotalprice int NOT NULL ,
        lo_discount      int NOT NULL ,
        lo_revenue       int NOT NULL ,
        lo_supplycost    int NOT NULL ,
        lo_tax           int NOT NULL ,
        lo_commitdate    date NOT NULL ,
        lo_shipmode      text NOT NULL ,
        c_name           text NOT NULL ,
        c_address text NOT NULL ,
        c_city text NOT NULL ,
        c_nation text NOT NULL ,
        c_region text NOT NULL ,
        c_phone text NOT NULL ,
        c_mktsegment text NOT NULL ,
        s_region text NOT NULL ,
        s_nation text NOT NULL ,
        s_city text NOT NULL ,
        s_name text NOT NULL ,
        s_address text NOT NULL ,
        s_phone text NOT NULL ,
        p_name text NOT NULL ,
        p_mfgr text NOT NULL ,
        p_category text NOT NULL ,
        p_brand text NOT NULL ,
        p_color text NOT NULL ,
        p_type text NOT NULL ,
        p_size int NOT NULL ,
        p_container text NOT NULL,
        PRIMARY KEY (lo_orderkey,lo_linenumber)
      );
      CALL set_table_property('lineorder_flat', 'distribution_key', 'lo_orderkey');
      CALL set_table_property('lineorder_flat', 'segment_key', 'lo_orderdate');
      CALL set_table_property('lineorder_flat', 'clustering_key', 'lo_orderdate');
      CALL set_table_property('lineorder_flat', 'bitmap_columns', 'p_category,s_region,c_region,c_nation,s_nation,c_city,s_city,p_mfgr,p_brand');
      CALL set_table_property('lineorder_flat', 'time_to_live_in_seconds', '31536000');
      COMMIT;
    • Query statements
      • Q1.1
        SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
        FROM LINEORDER_FLAT 
        WHERE LO_ORDERDATE >= DATE '1993-01-01' 
        AND LO_ORDERDATE <= DATE '1993-12-31' 
        AND LO_DISCOUNT BETWEEN 1 AND 3 
        AND LO_QUANTITY < 25;
      • Q1.2
        SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
        FROM LINEORDER_FLAT  
        WHERE LO_ORDERDATE >= DATE '1994-01-01' 
        AND LO_ORDERDATE <= DATE '1994-01-31' 
        AND LO_DISCOUNT BETWEEN 4 AND 6 
        AND LO_QUANTITY BETWEEN 26 AND 35;
      • Q1.3
        SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS REVENUE 
        FROM LINEORDER_FLAT 
        WHERE EXTRACT(WEEK FROM LO_ORDERDATE ) = 6 
        AND LO_ORDERDATE >= DATE '1994-01-01' 
        AND LO_ORDERDATE <= DATE '1994-12-31' 
        AND LO_DISCOUNT BETWEEN 5 AND 7 
        AND LO_QUANTITY BETWEEN 26 AND 35;
      • Q2.1
        SELECT SUM(LO_REVENUE),
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
        P_BRAND 
        FROM LINEORDER_FLAT 
        WHERE P_CATEGORY = 'MFGR#12' 
        AND S_REGION = 'AMERICA' 
        GROUP BY YEAR, P_BRAND 
        ORDER BY YEAR, P_BRAND;
      • Q2.2
        SELECT SUM(LO_REVENUE),
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR,
        P_BRAND 
        FROM LINEORDER_FLAT 
        WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' 
        AND S_REGION = 'ASIA' 
        GROUP BY YEAR, P_BRAND 
        ORDER BY YEAR, P_BRAND;
      • Q2.3
        SELECT SUM(LO_REVENUE),
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        P_BRAND 
        FROM LINEORDER_FLAT 
        WHERE P_BRAND = 'MFGR#2239' 
        AND S_REGION = 'EUROPE' 
        GROUP BY YEAR, P_BRAND 
        ORDER BY YEAR, P_BRAND;
      • Q3.1
        SELECT C_NATION,
        S_NATION, 
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        SUM(LO_REVENUE) AS REVENUE 
        FROM LINEORDER_FLAT 
        WHERE C_REGION = 'ASIA' 
        AND S_REGION = 'ASIA' 
        AND LO_ORDERDATE  >= DATE '1992-01-01' 
        AND LO_ORDERDATE  <= DATE '1997-12-31' 
        GROUP BY C_NATION,S_NATION,YEAR 
        ORDER BY  YEAR ASC,REVENUE DESC;
      • Q3.2
        SELECT C_CITY, 
        S_CITY, 
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        SUM(LO_REVENUE) AS REVENUE
        FROM LINEORDER_FLAT 
        WHERE C_NATION = 'UNITED STATES' 
        AND S_NATION = 'UNITED STATES' 
        AND LO_ORDERDATE >= DATE '1992-01-01' 
        AND LO_ORDERDATE <= DATE '1997-12-31' 
        GROUP BY C_CITY, S_CITY, YEAR 
        ORDER BY YEAR ASC, REVENUE DESC;
      • Q3.3
        SELECT C_CITY,
        S_CITY, 
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        SUM(LO_REVENUE) AS REVENUE 
        FROM LINEORDER_FLAT 
        WHERE C_CITY IN ( 'UNITED KI1' ,'UNITED KI5') 
        AND S_CITY IN ( 'UNITED KI1' ,'UNITED KI5') 
        AND LO_ORDERDATE >= DATE '1992-01-01' 
        AND LO_ORDERDATE <= DATE '1997-12-31' 
        GROUP BY C_CITY, S_CITY, YEAR 
        ORDER BY YEAR ASC, REVENUE DESC;
      • Q3.4
        SELECT C_CITY,
        S_CITY, 
        EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        SUM(LO_REVENUE) AS REVENUE 
        FROM LINEORDER_FLAT 
        WHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') 
        AND S_CITY IN ( 'UNITED KI1',  'UNITED KI5') 
        AND LO_ORDERDATE >= DATE '1997-12-01' 
        AND LO_ORDERDATE <= DATE '1997-12-31' 
        GROUP BY C_CITY, S_CITY, YEAR 
        ORDER BY YEAR ASC, REVENUE DESC;
      • Q4.1
        SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        C_NATION,  
        SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
        FROM LINEORDER_FLAT 
        WHERE C_REGION = 'AMERICA' 
        AND S_REGION = 'AMERICA' 
        AND P_MFGR IN ( 'MFGR#1' , 'MFGR#2') 
        GROUP BY YEAR, C_NATION 
        ORDER BY YEAR ASC, C_NATION ASC;
      • Q4.2
        SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        S_NATION, 
        P_CATEGORY, 
        SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
        FROM LINEORDER_FLAT 
        WHERE C_REGION = 'AMERICA' 
        AND S_REGION = 'AMERICA' 
        AND LO_ORDERDATE >= DATE '1997-01-01' 
        AND LO_ORDERDATE <= DATE '1998-12-31' 
        AND  P_MFGR IN ( 'MFGR#1' , 'MFGR#2') 
        GROUP BY YEAR, S_NATION, P_CATEGORY 
        ORDER BY  YEAR ASC, S_NATION ASC, P_CATEGORY ASC;
      • Q4.3
        SELECT EXTRACT(YEAR FROM LO_ORDERDATE) AS YEAR, 
        S_CITY, 
        P_BRAND, 
        SUM(LO_REVENUE - LO_SUPPLYCOST) AS PROFIT 
        FROM LINEORDER_FLAT 
        WHERE S_NATION = 'UNITED STATES' 
        AND LO_ORDERDATE >= DATE '1997-01-01' 
        AND LO_ORDERDATE <= DATE '1998-12-31' 
        AND P_CATEGORY = 'MFGR#14' 
        GROUP BY  YEAR,  S_CITY, P_BRAND 
        ORDER BY YEAR ASC,  S_CITY ASC,  P_BRAND ASC;

Test results

Query statement Amount of time used in Hologres (Unit: ms) Amount of time used in ClickHouse (Unit: ms) Amount of time used in ClickHouse/Amount of time used in Hologres (Unit: times)
Q1.1 43.66 59.00 1.35
Q1.2 20.68 21.00 1.02
Q1.3 57.98 22.00 0.38
Q2.1 247.63 254.00 1.03
Q2.2 251.90 281.00 1.12
Q2.3 165.73 214.00 1.29
Q3.1 332.84 434.00 1.30
Q3.2 247.79 348.00 1.40
Q3.3 117.46 299.00 2.55
Q3.4 30.05 25.00 0.83
Q4.1 298.48 456.00 1.53
Q4.2 116.47 171.00 1.47
Q4.3 97.68 146.00 1.49
Total 2,028.35 2,730.00 1.35