benchmark/tpch.md - doris-website - Git at Google

 ---
 {
     "title": "TPC-H Benchmark",
     "language": "en"
 }
 ---

 <!--
 Licensed to the Apache Software Foundation (ASF) under one
 or more contributor license agreements.  See the NOTICE file
 distributed with this work for additional information
 regarding copyright ownership.  The ASF licenses this file
 to you under the Apache License, Version 2.0 (the
 "License"); you may not use this file except in compliance
 with the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing,
 software distributed under the License is distributed on an
 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 KIND, either express or implied.  See the License for the
 specific language governing permissions and limitations
 under the License.
 -->

 # TPC-H Benchmark

 TPC-H is a decision support benchmark (Decision Support Benchmark), which consists of a set of business-oriented special query and concurrent data modification. The data that is queried and populates the database has broad industry relevance. This benchmark demonstrates a decision support system that examines large amounts of data, executes highly complex queries, and answers key business questions. The performance index reported by TPC-H is called TPC-H composite query performance index per hour (QphH@Size), which reflects multiple aspects of the system's ability to process queries. These aspects include the database size chosen when executing the query, the query processing capability when the query is submitted by a single stream, and the query throughput when the query is submitted by many concurrent users.

 This document mainly introduces the performance of Doris on the TPC-H 1000G test set.

 On 22 queries on the TPC-H standard test data set, we conducted a comparison test based on Apache Doris 2.1.1-rc03 and Apache Doris 2.0.6 versions.

 ![image-20220614114351241](/images/tpch_2.1.png)

 ## 1. Hardware Environment

 | Hardware            | Configuration Instructions                |
 |---------------------|-------------------------------------------|
 | Number of mMachines | 4 Tencent Cloud Virtual Machine（1FE，3BEs） |
 | CPU                 | AMD EPYC™ Milan(2.55GHz/3.5GHz)  48C      |
 | Memory              | 192G                                      |
 | Network             | 21Gbps                                    |
 | Disk                | ESSD Cloud Hard Disk                      |

 ## 2. Software Environment

 - Doris Deployed 3BEs and 1FE
 - Kernel Version: Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
 - OS version: Ubuntu 20.04 LTS (Focal Fossa)
 - Doris software version: Apache Doris 2.1.1-rc03, Apache Doris 2.0.6.
 - JDK: openjdk version "1.8.0_131"

 ## 3. Test Data Volume

 The TPCH 1000G data generated by the simulation of the entire test are respectively imported into Apache Doris 2.1.1-rc03 and Apache Doris 2.0.6 for testing. The following is the relevant description and data volume of the table.

 | TPC-H Table Name | Rows          | Annotation    |
 |:-----------------|:--------------|:--------------|
 | REGION           | 5             | Region        |
 | NATION           | 25            | Nation        |
 | SUPPLIER         | 10,000,000    | Supplier      |
 | PART             | 200,000,000   | Parts         |
 | PARTSUPP         | 800,000,000   | Parts Supply  |
 | CUSTOMER         | 150,000,000   | Customer      |
 | ORDERS           | 1,500,000,000 | Orders        |
 | LINEITEM         | 5,999,989,709 | Order Details |

 ## 4. Test SQL

 TPCH 22 test query statements : [TPCH-Query-SQL](https://github.com/apache/doris/tree/master/tools/tpch-tools/queries/sf1000)


 ## 5. Test Results

 Here we use Apache Doris 2.1.1-rc03 and Apache Doris 2.0.6 for comparative testing. In the test, we use Query Time(ms) as the main performance indicator. The test results are as follows:

 | Query     | Apache Doris 2.1.1-rc03 (ms) | Apache Doris 2.0.6 (ms) |
 |-----------|------------------------------|-------------------------|
 | Q1        | 7240                         | 7638                    |
 | Q2        | 249                          | 377                     |
 | Q3        | 3528                         | 4389                    |
 | Q4        | 1534                         | 1903                    |
 | Q5        | 5457                         | 6425                    |
 | Q6        | 159                          | 150                     |
 | Q7        | 1598                         | 3141                    |
 | Q8        | 2958                         | 2792                    |
 | Q9        | 14803                        | 24732                   |
 | Q10       | 6743                         | 7315                    |
 | Q11       | 414                          | 395                     |
 | Q12       | 371                          | 417                     |
 | Q13       | 6203                         | 8095                    |
 | Q14       | 485                          | 681                     |
 | Q15       | 2246                         | 1459                    |
 | Q16       | 1252                         | 1382                    |
 | Q17       | 1461                         | 1204                    |
 | Q18       | 10428                        | 11386                   |
 | Q19       | 2329                         | 2172                    |
 | Q20       | 569                          | 1455                    |
 | Q21       | 5574                         | 6570                    |
 | Q22       | 1042                         | 1517                    |
 | **Total** | **76643**                    | **95595**               |


 ## 6. Environmental Preparation

 Please refer to the [official document](../install/cluster-deployment/standard-deployment.md) to install and deploy Doris to obtain a normal running Doris cluster (at least 1 FE 1 BE, 1 FE 3 BE is recommended).

 ## 7. Data Preparation

 ### 7.1 Download and Install TPC-H Data Generation Tool

 Execute the following script to download and compile the [tpch-tools](https://github.com/apache/doris/tree/master/tools/tpch-tools) tool.

 ```shell
 sh bin/build-tpch-dbgen.sh
 ```

 After successful installation, the `dbgen` binary will be generated under the `TPC-H_Tools_v3.0.0/` directory.

 ### 7.2 Generating the TPC-H Test Set

 Execute the following script to generate the TPC-H dataset:

 ```shell
 sh bin/gen-tpch-data.sh -s 1000
 ```

 > Note 1: Check the script help via `sh gen-tpch-data.sh -h`.
 >
 > Note 2: The data will be generated under the `tpch-data/` directory with the suffix `.tbl`. The total file size is about 1000GB and may need a few minutes to an hour to generate.
 >
 > Note 3: A standard test data set of 100G is generated by default.

 ### 7.3 Create Table

 #### 7.3.1 Prepare the `doris-cluster.conf` File

 Before import the script, you need to write the FE’s ip port and other information in the `doris-cluster.conf` file.

 The file is located under `${DORIS_HOME}/tools/tpch-tools/conf/` .

 The content of the file includes FE's ip, HTTP port, user name, password and the DB name of the data to be imported:

 ```shell
 # Any of FE host
 export FE_HOST='127.0.0.1'
 # http_port in fe.conf
 export FE_HTTP_PORT=8030
 # query_port in fe.conf
 export FE_QUERY_PORT=9030
 # Doris username
 export USER='root'
 # Doris password
 export PASSWORD=''
 # The database where TPC-H tables located
 export DB='tpch'
 ```

 #### Execute the Following Script to Generate and Create TPC-H Table

 ```shell
 sh bin/create-tpch-tables.sh -s 1000
 ```
 Or copy the table creation statement in [create-tpch-tables.sql](https://github.com/apache/doris/blob/master/tools/tpch-tools/ddl/create-tpch-tables-sf1000.sql) and excute it in Doris.


 ### 7.4 Import Data

 Please perform data import with the following command:

 ```shell
 sh bin/load-tpch-data.sh
 ```

 ### 7.5 Check Imported Data

 Execute the following SQL statement to check that the imported data is consistent with the above data.

 ```sql
 select count(*)  from  lineitem;
 select count(*)  from  orders;
 select count(*)  from  partsupp;
 select count(*)  from  part;
 select count(*)  from  customer;
 select count(*)  from  supplier;
 select count(*)  from  nation;
 select count(*)  from  region;
 select count(*)  from  revenue0;
 ```

 ### 7.6 Query Test

 #### 7.6.1 Executing Query Scripts

 Execute the above test SQL or execute the following command

 ```
 sh bin/run-tpch-queries.sh -s 1000
 ```

 #### 7.6.2 Single SQL Execution

 The following is the SQL statement used in the test, you can also get the latest SQL from the code base.

 ```sql
 --Q1
 select
     l_returnflag,
     l_linestatus,
     sum(l_quantity) as sum_qty,
     sum(l_extendedprice) as sum_base_price,
     sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
     sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
     avg(l_quantity) as avg_qty,
     avg(l_extendedprice) as avg_price,
     avg(l_discount) as avg_disc,
     count(*) as count_order
 from
     lineitem
 where
     l_shipdate <= date '1998-12-01' - interval '90' day
 group by
     l_returnflag,
     l_linestatus
 order by
     l_returnflag,
     l_linestatus;

 --Q2
 select
     s_acctbal,
     s_name,
     n_name,
     p_partkey,
     p_mfgr,
     s_address,
     s_phone,
     s_comment
 from
     part,
     supplier,
     partsupp,
     nation,
     region
 where
     p_partkey = ps_partkey
     and s_suppkey = ps_suppkey
     and p_size = 15
     and p_type like '%BRASS'
     and s_nationkey = n_nationkey
     and n_regionkey = r_regionkey
     and r_name = 'EUROPE'
     and ps_supplycost = (
         select
             min(ps_supplycost)
         from
             partsupp,
             supplier,
             nation,
             region
         where
         p_partkey = ps_partkey
         and s_suppkey = ps_suppkey
         and s_nationkey = n_nationkey
         and n_regionkey = r_regionkey
         and r_name = 'EUROPE'
 )
 order by
     s_acctbal desc,
     n_name,
     s_name,
     p_partkey
 limit 100;

 --Q3
 select
     l_orderkey,
     sum(l_extendedprice * (1 - l_discount)) as revenue,
     o_orderdate,
     o_shippriority
 from
     customer,
     orders,
     lineitem
 where
     c_mktsegment = 'BUILDING'
     and c_custkey = o_custkey
     and l_orderkey = o_orderkey
     and o_orderdate < date '1995-03-15'
     and l_shipdate > date '1995-03-15'
 group by
     l_orderkey,
     o_orderdate,
     o_shippriority
 order by
     revenue desc,
     o_orderdate
 limit 10;

 --Q4
 select
     o_orderpriority,
     count(*) as order_count
 from
     orders
 where
     o_orderdate >= date '1993-07-01'
     and o_orderdate < date '1993-07-01' + interval '3' month
     and exists (
         select
             *
         from
             lineitem
         where
                 l_orderkey = o_orderkey
           and l_commitdate < l_receiptdate
     )
 group by
     o_orderpriority
 order by
     o_orderpriority;

 --Q5
 select
     n_name,
     sum(l_extendedprice * (1 - l_discount)) as revenue
 from
     customer,
     orders,
     lineitem,
     supplier,
     nation,
     region
 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 = 'ASIA'
     and o_orderdate >= date '1994-01-01'
     and o_orderdate < date '1994-01-01' + interval '1' year
 group by
     n_name
 order by
     revenue desc;

 --Q6
 select
     sum(l_extendedprice * l_discount) as revenue
 from
     lineitem
 where
     l_shipdate >= date '1994-01-01'
     and l_shipdate < date '1994-01-01' + interval '1' year
     and l_discount between .06 - 0.01 and .06 + 0.01
     and l_quantity < 24;

 --Q7
 select
     supp_nation,
     cust_nation,
     l_year,
     sum(volume) as revenue
 from
     (
         select
             n1.n_name as supp_nation,
             n2.n_name as cust_nation,
             extract(year from l_shipdate) as l_year,
             l_extendedprice * (1 - l_discount) as volume
         from
             supplier,
             lineitem,
             orders,
             customer,
             nation n1,
             nation n2
         where
             s_suppkey = l_suppkey
             and o_orderkey = l_orderkey
             and c_custkey = o_custkey
             and s_nationkey = n1.n_nationkey
             and c_nationkey = n2.n_nationkey
             and (
                 (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
                 or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
             )
             and l_shipdate between date '1995-01-01' and date '1996-12-31'
     ) as shipping
 group by
     supp_nation,
     cust_nation,
     l_year
 order by
     supp_nation,
     cust_nation,
     l_year;

 --Q8

 select
     o_year,
     sum(case
         when nation = 'BRAZIL' then volume
         else 0
     end) / sum(volume) as mkt_share
 from
     (
         select
             extract(year from o_orderdate) as o_year,
             l_extendedprice * (1 - l_discount) as volume,
             n2.n_name as nation
         from
             part,
             supplier,
             lineitem,
             orders,
             customer,
             nation n1,
             nation n2,
             region
         where
             p_partkey = l_partkey
             and s_suppkey = l_suppkey
             and l_orderkey = o_orderkey
             and o_custkey = c_custkey
             and c_nationkey = n1.n_nationkey
             and n1.n_regionkey = r_regionkey
             and r_name = 'AMERICA'
             and s_nationkey = n2.n_nationkey
             and o_orderdate between date '1995-01-01' and date '1996-12-31'
             and p_type = 'ECONOMY ANODIZED STEEL'
     ) as all_nations
 group by
     o_year
 order by
     o_year;

 --Q9
 select
     nation,
     o_year,
     sum(amount) as sum_profit
 from
     (
         select
             n_name as nation,
             extract(year from o_orderdate) as o_year,
             l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
         from
             part,
             supplier,
             lineitem,
             partsupp,
             orders,
             nation
         where
             s_suppkey = l_suppkey
             and ps_suppkey = l_suppkey
             and ps_partkey = l_partkey
             and p_partkey = l_partkey
             and o_orderkey = l_orderkey
             and s_nationkey = n_nationkey
             and p_name like '%green%'
     ) as profit
 group by
     nation,
     o_year
 order by
     nation,
     o_year desc;

 --Q10
 select
     c_custkey,
     c_name,
     sum(l_extendedprice * (1 - l_discount)) as revenue,
     c_acctbal,
     n_name,
     c_address,
     c_phone,
     c_comment
 from
     customer,
     orders,
     lineitem,
     nation
 where
     c_custkey = o_custkey
     and l_orderkey = o_orderkey
     and o_orderdate >= date '1993-10-01'
     and o_orderdate < date '1993-10-01' + interval '3' month
     and l_returnflag = 'R'
     and c_nationkey = n_nationkey
 group by
     c_custkey,
     c_name,
     c_acctbal,
     c_phone,
     n_name,
     c_address,
     c_comment
 order by
     revenue desc
 limit 20;


 --Q11
 select
     ps_partkey,
     sum(ps_supplycost * ps_availqty) as value
 from
     partsupp,
     supplier,
     nation
 where
     ps_suppkey = s_suppkey
     and s_nationkey = n_nationkey
     and n_name = 'GERMANY'
 group by
     ps_partkey having
     sum(ps_supplycost * ps_availqty) > (
         select
         sum(ps_supplycost * ps_availqty) * 0.000002
         from
             partsupp,
             supplier,
             nation
         where
             ps_suppkey = s_suppkey
             and s_nationkey = n_nationkey
             and n_name = 'GERMANY'
     )
 order by
     value desc;

 --Q12
 select
     l_shipmode,
     sum(case
         when o_orderpriority = '1-URGENT'
             or o_orderpriority = '2-HIGH'
             then 1
         else 0
     end) as high_line_count,
     sum(case
         when o_orderpriority <> '1-URGENT'
             and o_orderpriority <> '2-HIGH'
             then 1
         else 0
     end) as low_line_count
 from
     orders,
     lineitem
 where
     o_orderkey = l_orderkey
     and l_shipmode in ('MAIL', 'SHIP')
     and l_commitdate < l_receiptdate
     and l_shipdate < l_commitdate
     and l_receiptdate >= date '1994-01-01'
     and l_receiptdate < date '1994-01-01' + interval '1' year
 group by
     l_shipmode
 order by
     l_shipmode;

 --Q13
 select
     c_count,
     count(*) as custdist
 from
     (
         select
             c_custkey,
             count(o_orderkey) as c_count
         from
             customer left outer join orders on
                 c_custkey = o_custkey
                 and o_comment not like '%special%requests%'
         group by
             c_custkey
     ) as c_orders
 group by
     c_count
 order by
     custdist desc,
     c_count desc;

 --Q14
 select
     100.00 * sum(case
         when p_type like 'PROMO%'
             then l_extendedprice * (1 - l_discount)
         else 0
     end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
 from
     lineitem,
     part
 where
     l_partkey = p_partkey
     and l_shipdate >= date '1995-09-01'
     and l_shipdate < date '1995-09-01' + interval '1' month;

 --Q15
 select
     s_suppkey,
     s_name,
     s_address,
     s_phone,
     total_revenue
 from
     supplier,
     revenue0
 where
     s_suppkey = supplier_no
     and total_revenue = (
         select
             max(total_revenue)
         from
             revenue0
     )
 order by
     s_suppkey;

 --Q16
 select
     p_brand,
     p_type,
     p_size,
     count(distinct ps_suppkey) as supplier_cnt
 from
     partsupp,
     part
 where
     p_partkey = ps_partkey
     and p_brand <> 'Brand#45'
     and p_type not like 'MEDIUM POLISHED%'
     and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
     and ps_suppkey not in (
         select
             s_suppkey
         from
             supplier
         where
             s_comment like '%Customer%Complaints%'
     )
 group by
     p_brand,
     p_type,
     p_size
 order by
     supplier_cnt desc,
     p_brand,
     p_type,
     p_size;

 --Q17
 select
     sum(l_extendedprice) / 7.0 as avg_yearly
 from
     lineitem,
     part
 where
     p_partkey = l_partkey
     and p_brand = 'Brand#23'
     and p_container = 'MED BOX'
     and l_quantity < (
         select
             0.2 * avg(l_quantity)
         from
             lineitem
         where
             l_partkey = p_partkey
     );

 --Q18
 select
     c_name,
     c_custkey,
     o_orderkey,
     o_orderdate,
     o_totalprice,
     sum(l_quantity)
 from
     customer,
     orders,
     lineitem
 where
     o_orderkey  in  (
         select
             l_orderkey
         from
             lineitem
         group  by
             l_orderkey  having
                 sum(l_quantity)  >  300
     )
     and  c_custkey  =  o_custkey
     and  o_orderkey  =  l_orderkey
 group  by
     c_name,
     c_custkey,
     o_orderkey,
     o_orderdate,
     o_totalprice
 order  by
     o_totalprice  desc,
     o_orderdate
 limit  100;


 --Q19
 select
     sum(l_extendedprice* (1 - l_discount)) as revenue
 from
     lineitem,
     part
 where
     (
         p_partkey = l_partkey
         and p_brand = 'Brand#12'
         and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
         and l_quantity >= 1 and l_quantity <= 1 + 10
         and p_size between 1 and 5
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
     )
     or
     (
         p_partkey = l_partkey
         and p_brand = 'Brand#23'
         and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
         and l_quantity >= 10 and l_quantity <= 10 + 10
         and p_size between 1 and 10
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
     )
     or
     (
         p_partkey = l_partkey
         and p_brand = 'Brand#34'
         and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
         and l_quantity >= 20 and l_quantity <= 20 + 10
         and p_size between 1 and 15
         and l_shipmode in ('AIR', 'AIR REG')
         and l_shipinstruct = 'DELIVER IN PERSON'
     );

 --Q20
 select
     s_name,
     s_address
 from
     supplier,
     nation
 where
     s_suppkey in (
         select
             ps_suppkey
         from
             partsupp
         where
             ps_partkey in (
                 select
                     p_partkey
                 from
                     part
                 where
                         p_name like 'forest%'
             )
             and ps_availqty > (
                 select
                     0.5 * sum(l_quantity)
                 from
                     lineitem
                 where
                     l_partkey = ps_partkey
                     and l_suppkey = ps_suppkey
                     and l_shipdate >= date '1994-01-01'
                     and l_shipdate < date '1994-01-01' + interval '1' year
             )
     )
     and s_nationkey = n_nationkey
     and n_name = 'CANADA'
 order by
     s_name;

 --Q21
 select
     s_name,
     count(*) as numwait
 from
     supplier,
     lineitem l1,
     orders,
     nation
 where
     s_suppkey = l1.l_suppkey
     and o_orderkey = l1.l_orderkey
     and o_orderstatus = 'F'
     and l1.l_receiptdate > l1.l_commitdate
     and exists (
         select
             *
         from
             lineitem l2
         where
                 l2.l_orderkey = l1.l_orderkey
           and l2.l_suppkey <> l1.l_suppkey
     )
     and not exists (
         select
             *
         from
             lineitem l3
         where
                 l3.l_orderkey = l1.l_orderkey
           and l3.l_suppkey <> l1.l_suppkey
           and l3.l_receiptdate > l3.l_commitdate
     )
     and s_nationkey = n_nationkey
     and n_name = 'SAUDI ARABIA'
 group by
     s_name
 order by
     numwait desc,
     s_name
 limit 100;

 --Q22
 select
     cntrycode,
     count(*) as numcust,
     sum(c_acctbal) as totacctbal
 from
     (
         select
             substring(c_phone, 1, 2) as cntrycode,
             c_acctbal
         from
             customer
         where
             substring(c_phone, 1, 2) in
             ('13', '31', '23', '29', '30', '18', '17')
             and c_acctbal > (
                 select
                     avg(c_acctbal)
                 from
                     customer
                 where
                     c_acctbal > 0.00
                     and substring(c_phone, 1, 2) in
                       ('13', '31', '23', '29', '30', '18', '17')
             )
             and not exists (
                 select
                     *
                 from
                     orders
                 where
                     o_custkey = c_custkey
             )
     ) as custsale
 group by
     cntrycode
 order by
     cntrycode;

 ```
	---
	{
	"title": "TPC-H Benchmark",
	"language": "en"
	}
	---

	<!--
	Licensed to the Apache Software Foundation (ASF) under one
	or more contributor license agreements. See the NOTICE file
	distributed with this work for additional information
	regarding copyright ownership. The ASF licenses this file
	to you under the Apache License, Version 2.0 (the
	"License"); you may not use this file except in compliance
	with the License. You may obtain a copy of the License at

	http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing,
	software distributed under the License is distributed on an
	"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	KIND, either express or implied. See the License for the
	specific language governing permissions and limitations
	under the License.
	-->

	# TPC-H Benchmark

	TPC-H is a decision support benchmark (Decision Support Benchmark), which consists of a set of business-oriented special query and concurrent data modification. The data that is queried and populates the database has broad industry relevance. This benchmark demonstrates a decision support system that examines large amounts of data, executes highly complex queries, and answers key business questions. The performance index reported by TPC-H is called TPC-H composite query performance index per hour (QphH@Size), which reflects multiple aspects of the system's ability to process queries. These aspects include the database size chosen when executing the query, the query processing capability when the query is submitted by a single stream, and the query throughput when the query is submitted by many concurrent users.

	This document mainly introduces the performance of Doris on the TPC-H 1000G test set.

	On 22 queries on the TPC-H standard test data set, we conducted a comparison test based on Apache Doris 2.1.1-rc03 and Apache Doris 2.0.6 versions.

	![image-20220614114351241](/images/tpch_2.1.png)

	## 1. Hardware Environment

	\| Hardware \| Configuration Instructions \|
	\|---------------------\|-------------------------------------------\|
	\| Number of mMachines \| 4 Tencent Cloud Virtual Machine（1FE，3BEs） \|
	\| CPU \| AMD EPYC™ Milan(2.55GHz/3.5GHz) 48C \|
	\| Memory \| 192G \|
	\| Network \| 21Gbps \|
	\| Disk \| ESSD Cloud Hard Disk \|

	## 2. Software Environment

	- Doris Deployed 3BEs and 1FE
	- Kernel Version: Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)
	- OS version: Ubuntu 20.04 LTS (Focal Fossa)
	- Doris software version: Apache Doris 2.1.1-rc03, Apache Doris 2.0.6.
	- JDK: openjdk version "1.8.0_131"

	## 3. Test Data Volume

	The TPCH 1000G data generated by the simulation of the entire test are respectively imported into Apache Doris 2.1.1-rc03 and Apache Doris 2.0.6 for testing. The following is the relevant description and data volume of the table.

	\| TPC-H Table Name \| Rows \| Annotation \|
	\|:-----------------\|:--------------\|:--------------\|
	\| REGION \| 5 \| Region \|
	\| NATION \| 25 \| Nation \|
	\| SUPPLIER \| 10,000,000 \| Supplier \|
	\| PART \| 200,000,000 \| Parts \|
	\| PARTSUPP \| 800,000,000 \| Parts Supply \|
	\| CUSTOMER \| 150,000,000 \| Customer \|
	\| ORDERS \| 1,500,000,000 \| Orders \|
	\| LINEITEM \| 5,999,989,709 \| Order Details \|

	## 4. Test SQL

	TPCH 22 test query statements : [TPCH-Query-SQL](https://github.com/apache/doris/tree/master/tools/tpch-tools/queries/sf1000)


	## 5. Test Results

	Here we use Apache Doris 2.1.1-rc03 and Apache Doris 2.0.6 for comparative testing. In the test, we use Query Time(ms) as the main performance indicator. The test results are as follows:

	\| Query \| Apache Doris 2.1.1-rc03 (ms) \| Apache Doris 2.0.6 (ms) \|
	\|-----------\|------------------------------\|-------------------------\|
	\| Q1 \| 7240 \| 7638 \|
	\| Q2 \| 249 \| 377 \|
	\| Q3 \| 3528 \| 4389 \|
	\| Q4 \| 1534 \| 1903 \|
	\| Q5 \| 5457 \| 6425 \|
	\| Q6 \| 159 \| 150 \|
	\| Q7 \| 1598 \| 3141 \|
	\| Q8 \| 2958 \| 2792 \|
	\| Q9 \| 14803 \| 24732 \|
	\| Q10 \| 6743 \| 7315 \|
	\| Q11 \| 414 \| 395 \|
	\| Q12 \| 371 \| 417 \|
	\| Q13 \| 6203 \| 8095 \|
	\| Q14 \| 485 \| 681 \|
	\| Q15 \| 2246 \| 1459 \|
	\| Q16 \| 1252 \| 1382 \|
	\| Q17 \| 1461 \| 1204 \|
	\| Q18 \| 10428 \| 11386 \|
	\| Q19 \| 2329 \| 2172 \|
	\| Q20 \| 569 \| 1455 \|
	\| Q21 \| 5574 \| 6570 \|
	\| Q22 \| 1042 \| 1517 \|
	\| Total \| 76643 \| 95595 \|


	## 6. Environmental Preparation

	Please refer to the [official document](../install/cluster-deployment/standard-deployment.md) to install and deploy Doris to obtain a normal running Doris cluster (at least 1 FE 1 BE, 1 FE 3 BE is recommended).

	## 7. Data Preparation

	### 7.1 Download and Install TPC-H Data Generation Tool

	Execute the following script to download and compile the [tpch-tools](https://github.com/apache/doris/tree/master/tools/tpch-tools) tool.

	```shell
	sh bin/build-tpch-dbgen.sh
	```

	After successful installation, the `dbgen` binary will be generated under the `TPC-H_Tools_v3.0.0/` directory.

	### 7.2 Generating the TPC-H Test Set

	Execute the following script to generate the TPC-H dataset:

	```shell
	sh bin/gen-tpch-data.sh -s 1000
	```

	> Note 1: Check the script help via `sh gen-tpch-data.sh -h`.
	>
	> Note 2: The data will be generated under the `tpch-data/` directory with the suffix `.tbl`. The total file size is about 1000GB and may need a few minutes to an hour to generate.
	>
	> Note 3: A standard test data set of 100G is generated by default.

	### 7.3 Create Table

	#### 7.3.1 Prepare the `doris-cluster.conf` File

	Before import the script, you need to write the FE’s ip port and other information in the `doris-cluster.conf` file.

	The file is located under `${DORIS_HOME}/tools/tpch-tools/conf/` .

	The content of the file includes FE's ip, HTTP port, user name, password and the DB name of the data to be imported:

	```shell
	# Any of FE host
	export FE_HOST='127.0.0.1'
	# http_port in fe.conf
	export FE_HTTP_PORT=8030
	# query_port in fe.conf
	export FE_QUERY_PORT=9030
	# Doris username
	export USER='root'
	# Doris password
	export PASSWORD=''
	# The database where TPC-H tables located
	export DB='tpch'
	```

	#### Execute the Following Script to Generate and Create TPC-H Table

	```shell
	sh bin/create-tpch-tables.sh -s 1000
	```
	Or copy the table creation statement in [create-tpch-tables.sql](https://github.com/apache/doris/blob/master/tools/tpch-tools/ddl/create-tpch-tables-sf1000.sql) and excute it in Doris.


	### 7.4 Import Data

	Please perform data import with the following command:

	```shell
	sh bin/load-tpch-data.sh
	```

	### 7.5 Check Imported Data

	Execute the following SQL statement to check that the imported data is consistent with the above data.

	```sql
	select count(*) from lineitem;
	select count(*) from orders;
	select count(*) from partsupp;
	select count(*) from part;
	select count(*) from customer;
	select count(*) from supplier;
	select count(*) from nation;
	select count(*) from region;
	select count(*) from revenue0;
	```

	### 7.6 Query Test

	#### 7.6.1 Executing Query Scripts

	Execute the above test SQL or execute the following command

	```
	sh bin/run-tpch-queries.sh -s 1000
	```

	#### 7.6.2 Single SQL Execution

	The following is the SQL statement used in the test, you can also get the latest SQL from the code base.

	```sql
	--Q1
	select
	l_returnflag,
	l_linestatus,
	sum(l_quantity) as sum_qty,
	sum(l_extendedprice) as sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
	avg(l_quantity) as avg_qty,
	avg(l_extendedprice) as avg_price,
	avg(l_discount) as avg_disc,
	count(*) as count_order
	from
	lineitem
	where
	l_shipdate <= date '1998-12-01' - interval '90' day
	group by
	l_returnflag,
	l_linestatus
	order by
	l_returnflag,
	l_linestatus;

	--Q2
	select
	s_acctbal,
	s_name,
	n_name,
	p_partkey,
	p_mfgr,
	s_address,
	s_phone,
	s_comment
	from
	part,
	supplier,
	partsupp,
	nation,
	region
	where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and p_size = 15
	and p_type like '%BRASS'
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'EUROPE'
	and ps_supplycost = (
	select
	min(ps_supplycost)
	from
	partsupp,
	supplier,
	nation,
	region
	where
	p_partkey = ps_partkey
	and s_suppkey = ps_suppkey
	and s_nationkey = n_nationkey
	and n_regionkey = r_regionkey
	and r_name = 'EUROPE'
	)
	order by
	s_acctbal desc,
	n_name,
	s_name,
	p_partkey
	limit 100;

	--Q3
	select
	l_orderkey,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	o_orderdate,
	o_shippriority
	from
	customer,
	orders,
	lineitem
	where
	c_mktsegment = 'BUILDING'
	and c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate < date '1995-03-15'
	and l_shipdate > date '1995-03-15'
	group by
	l_orderkey,
	o_orderdate,
	o_shippriority
	order by
	revenue desc,
	o_orderdate
	limit 10;

	--Q4
	select
	o_orderpriority,
	count(*) as order_count
	from
	orders
	where
	o_orderdate >= date '1993-07-01'
	and o_orderdate < date '1993-07-01' + interval '3' month
	and exists (
	select
	*
	from
	lineitem
	where
	l_orderkey = o_orderkey
	and l_commitdate < l_receiptdate
	)
	group by
	o_orderpriority
	order by
	o_orderpriority;

	--Q5
	select
	n_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue
	from
	customer,
	orders,
	lineitem,
	supplier,
	nation,
	region
	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 = 'ASIA'
	and o_orderdate >= date '1994-01-01'
	and o_orderdate < date '1994-01-01' + interval '1' year
	group by
	n_name
	order by
	revenue desc;

	--Q6
	select
	sum(l_extendedprice * l_discount) as revenue
	from
	lineitem
	where
	l_shipdate >= date '1994-01-01'
	and l_shipdate < date '1994-01-01' + interval '1' year
	and l_discount between .06 - 0.01 and .06 + 0.01
	and l_quantity < 24;

	--Q7
	select
	supp_nation,
	cust_nation,
	l_year,
	sum(volume) as revenue
	from
	(
	select
	n1.n_name as supp_nation,
	n2.n_name as cust_nation,
	extract(year from l_shipdate) as l_year,
	l_extendedprice * (1 - l_discount) as volume
	from
	supplier,
	lineitem,
	orders,
	customer,
	nation n1,
	nation n2
	where
	s_suppkey = l_suppkey
	and o_orderkey = l_orderkey
	and c_custkey = o_custkey
	and s_nationkey = n1.n_nationkey
	and c_nationkey = n2.n_nationkey
	and (
	(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
	or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
	)
	and l_shipdate between date '1995-01-01' and date '1996-12-31'
	) as shipping
	group by
	supp_nation,
	cust_nation,
	l_year
	order by
	supp_nation,
	cust_nation,
	l_year;

	--Q8

	select
	o_year,
	sum(case
	when nation = 'BRAZIL' then volume
	else 0
	end) / sum(volume) as mkt_share
	from
	(
	select
	extract(year from o_orderdate) as o_year,
	l_extendedprice * (1 - l_discount) as volume,
	n2.n_name as nation
	from
	part,
	supplier,
	lineitem,
	orders,
	customer,
	nation n1,
	nation n2,
	region
	where
	p_partkey = l_partkey
	and s_suppkey = l_suppkey
	and l_orderkey = o_orderkey
	and o_custkey = c_custkey
	and c_nationkey = n1.n_nationkey
	and n1.n_regionkey = r_regionkey
	and r_name = 'AMERICA'
	and s_nationkey = n2.n_nationkey
	and o_orderdate between date '1995-01-01' and date '1996-12-31'
	and p_type = 'ECONOMY ANODIZED STEEL'
	) as all_nations
	group by
	o_year
	order by
	o_year;

	--Q9
	select
	nation,
	o_year,
	sum(amount) as sum_profit
	from
	(
	select
	n_name as nation,
	extract(year from o_orderdate) as o_year,
	l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
	from
	part,
	supplier,
	lineitem,
	partsupp,
	orders,
	nation
	where
	s_suppkey = l_suppkey
	and ps_suppkey = l_suppkey
	and ps_partkey = l_partkey
	and p_partkey = l_partkey
	and o_orderkey = l_orderkey
	and s_nationkey = n_nationkey
	and p_name like '%green%'
	) as profit
	group by
	nation,
	o_year
	order by
	nation,
	o_year desc;

	--Q10
	select
	c_custkey,
	c_name,
	sum(l_extendedprice * (1 - l_discount)) as revenue,
	c_acctbal,
	n_name,
	c_address,
	c_phone,
	c_comment
	from
	customer,
	orders,
	lineitem,
	nation
	where
	c_custkey = o_custkey
	and l_orderkey = o_orderkey
	and o_orderdate >= date '1993-10-01'
	and o_orderdate < date '1993-10-01' + interval '3' month
	and l_returnflag = 'R'
	and c_nationkey = n_nationkey
	group by
	c_custkey,
	c_name,
	c_acctbal,
	c_phone,
	n_name,
	c_address,
	c_comment
	order by
	revenue desc
	limit 20;


	--Q11
	select
	ps_partkey,
	sum(ps_supplycost * ps_availqty) as value
	from
	partsupp,
	supplier,
	nation
	where
	ps_suppkey = s_suppkey
	and s_nationkey = n_nationkey
	and n_name = 'GERMANY'
	group by
	ps_partkey having
	sum(ps_supplycost * ps_availqty) > (
	select
	sum(ps_supplycost * ps_availqty) * 0.000002
	from
	partsupp,
	supplier,
	nation
	where
	ps_suppkey = s_suppkey
	and s_nationkey = n_nationkey
	and n_name = 'GERMANY'
	)
	order by
	value desc;

	--Q12
	select
	l_shipmode,
	sum(case
	when o_orderpriority = '1-URGENT'
	or o_orderpriority = '2-HIGH'
	then 1
	else 0
	end) as high_line_count,
	sum(case
	when o_orderpriority <> '1-URGENT'
	and o_orderpriority <> '2-HIGH'
	then 1
	else 0
	end) as low_line_count
	from
	orders,
	lineitem
	where
	o_orderkey = l_orderkey
	and l_shipmode in ('MAIL', 'SHIP')
	and l_commitdate < l_receiptdate
	and l_shipdate < l_commitdate
	and l_receiptdate >= date '1994-01-01'
	and l_receiptdate < date '1994-01-01' + interval '1' year
	group by
	l_shipmode
	order by
	l_shipmode;

	--Q13
	select
	c_count,
	count(*) as custdist
	from
	(
	select
	c_custkey,
	count(o_orderkey) as c_count
	from
	customer left outer join orders on
	c_custkey = o_custkey
	and o_comment not like '%special%requests%'
	group by
	c_custkey
	) as c_orders
	group by
	c_count
	order by
	custdist desc,
	c_count desc;

	--Q14
	select
	100.00 * sum(case
	when p_type like 'PROMO%'
	then l_extendedprice * (1 - l_discount)
	else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
	from
	lineitem,
	part
	where
	l_partkey = p_partkey
	and l_shipdate >= date '1995-09-01'
	and l_shipdate < date '1995-09-01' + interval '1' month;

	--Q15
	select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
	from
	supplier,
	revenue0
	where
	s_suppkey = supplier_no
	and total_revenue = (
	select
	max(total_revenue)
	from
	revenue0
	)
	order by
	s_suppkey;

	--Q16
	select
	p_brand,
	p_type,
	p_size,
	count(distinct ps_suppkey) as supplier_cnt
	from
	partsupp,
	part
	where
	p_partkey = ps_partkey
	and p_brand <> 'Brand#45'
	and p_type not like 'MEDIUM POLISHED%'
	and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
	and ps_suppkey not in (
	select
	s_suppkey
	from
	supplier
	where
	s_comment like '%Customer%Complaints%'
	)
	group by
	p_brand,
	p_type,
	p_size
	order by
	supplier_cnt desc,
	p_brand,
	p_type,
	p_size;

	--Q17
	select
	sum(l_extendedprice) / 7.0 as avg_yearly
	from
	lineitem,
	part
	where
	p_partkey = l_partkey
	and p_brand = 'Brand#23'
	and p_container = 'MED BOX'
	and l_quantity < (
	select
	0.2 * avg(l_quantity)
	from
	lineitem
	where
	l_partkey = p_partkey
	);

	--Q18
	select
	c_name,
	c_custkey,
	o_orderkey,
	o_orderdate,
	o_totalprice,
	sum(l_quantity)
	from
	customer,
	orders,
	lineitem
	where
	o_orderkey in (
	select
	l_orderkey
	from
	lineitem
	group by
	l_orderkey having
	sum(l_quantity) > 300
	)
	and c_custkey = o_custkey
	and o_orderkey = l_orderkey
	group by
	c_name,
	c_custkey,
	o_orderkey,
	o_orderdate,
	o_totalprice
	order by
	o_totalprice desc,
	o_orderdate
	limit 100;


	--Q19
	select
	sum(l_extendedprice* (1 - l_discount)) as revenue
	from
	lineitem,
	part
	where
	(
	p_partkey = l_partkey
	and p_brand = 'Brand#12'
	and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
	and l_quantity >= 1 and l_quantity <= 1 + 10
	and p_size between 1 and 5
	and l_shipmode in ('AIR', 'AIR REG')
	and l_shipinstruct = 'DELIVER IN PERSON'
	)
	or
	(
	p_partkey = l_partkey
	and p_brand = 'Brand#23'
	and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
	and l_quantity >= 10 and l_quantity <= 10 + 10
	and p_size between 1 and 10
	and l_shipmode in ('AIR', 'AIR REG')
	and l_shipinstruct = 'DELIVER IN PERSON'
	)
	or
	(
	p_partkey = l_partkey
	and p_brand = 'Brand#34'
	and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
	and l_quantity >= 20 and l_quantity <= 20 + 10
	and p_size between 1 and 15
	and l_shipmode in ('AIR', 'AIR REG')
	and l_shipinstruct = 'DELIVER IN PERSON'
	);

	--Q20
	select
	s_name,
	s_address
	from
	supplier,
	nation
	where
	s_suppkey in (
	select
	ps_suppkey
	from
	partsupp
	where
	ps_partkey in (
	select
	p_partkey
	from
	part
	where
	p_name like 'forest%'
	)
	and ps_availqty > (
	select
	0.5 * sum(l_quantity)
	from
	lineitem
	where
	l_partkey = ps_partkey
	and l_suppkey = ps_suppkey
	and l_shipdate >= date '1994-01-01'
	and l_shipdate < date '1994-01-01' + interval '1' year
	)
	)
	and s_nationkey = n_nationkey
	and n_name = 'CANADA'
	order by
	s_name;

	--Q21
	select
	s_name,
	count(*) as numwait
	from
	supplier,
	lineitem l1,
	orders,
	nation
	where
	s_suppkey = l1.l_suppkey
	and o_orderkey = l1.l_orderkey
	and o_orderstatus = 'F'
	and l1.l_receiptdate > l1.l_commitdate
	and exists (
	select
	*
	from
	lineitem l2
	where
	l2.l_orderkey = l1.l_orderkey
	and l2.l_suppkey <> l1.l_suppkey
	)
	and not exists (
	select
	*
	from
	lineitem l3
	where
	l3.l_orderkey = l1.l_orderkey
	and l3.l_suppkey <> l1.l_suppkey
	and l3.l_receiptdate > l3.l_commitdate
	)
	and s_nationkey = n_nationkey
	and n_name = 'SAUDI ARABIA'
	group by
	s_name
	order by
	numwait desc,
	s_name
	limit 100;

	--Q22
	select
	cntrycode,
	count(*) as numcust,
	sum(c_acctbal) as totacctbal
	from
	(
	select
	substring(c_phone, 1, 2) as cntrycode,
	c_acctbal
	from
	customer
	where
	substring(c_phone, 1, 2) in
	('13', '31', '23', '29', '30', '18', '17')
	and c_acctbal > (
	select
	avg(c_acctbal)
	from
	customer
	where
	c_acctbal > 0.00
	and substring(c_phone, 1, 2) in
	('13', '31', '23', '29', '30', '18', '17')
	)
	and not exists (
	select
	*
	from
	orders
	where
	o_custkey = c_custkey
	)
	) as custsale
	group by
	cntrycode
	order by
	cntrycode;

	```