TPC-H SF100 Non-parallel Plans, SQL Server 2008
SQL Server 2008 RTM and SP1, SF100 non-parallel plans
There is the command ALTER DATABASE tpch SET DATE_CORRELATION_OPTIMIZATION ON
The DATE_CORRELATION_OPTIMIZATION database SET option improves the performance of queries that perform an equi-join between two tables whose date or datetime columns are correlated, and which specify a date restriction in the query predicate.
When the DATE_CORRELATION_OPTIMIZATION database option is set to ON, SQL Server maintains correlation statistics between any two tables in the database that have date or datetime columns and are linked by a one-column foreign key constraint. By default, this option is set to OFF.
SQL Server uses these correlation statistics together with the date restriction specified in the query predicate to infer that additional restrictions can be added to the query without changing the result set. The query optimizer uses these inferred conditions when it chooses a query plan. A faster query plan may result, because the added restrictions let SQL Server read less data when it is processing the query. Performance is also improved when both tables have clustered indexes defined on them, and their date or datetime columns for which correlation statistics are maintained are the first or only key of the clustered index.
/* TPC_H Query 1 - Pricing Summary Report */
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 <=
dateadd(dd,
-90,
cast('1998-12-01'
as date))
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG,L_LINESTATUS
TPC-H Query 1 full size, non-parallel, parallel
/* TPC_H Query 2 - Minimum Cost Supplier */
SELECT TOP 100 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
/* TPC_H Query 3 - Shipping Priority */
SELECT TOP 10 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 < '1995-03-15'
AND L_SHIPDATE > '1995-03-15'
GROUP BY L_ORDERKEY,
O_ORDERDATE, O_SHIPPRIORITY
ORDER BY REVENUE DESC, O_ORDERDATE
In the TPC-H benchmark, the maximum difference between O_ORDERDATE and SHIPDATE is 121 days, so technically for query 3 we could have appended the additional SARG below.
AND L_SHIPDATE <= dateadd(dd, 122, cast('1995-03-15' as date)
Below is TPC-H Query 3, the top plan points to the database without date correlation. The second plan points to a database with correlation enabled.
/* TPC_H Query 4 - Order Priority Checking */
SELECT O_ORDERPRIORITY,
COUNT(*) AS ORDER_COUNT
FROM ORDERS
WHERE O_ORDERDATE >= '1993-07-01'
AND O_ORDERDATE <
dateadd(mm,3,
cast('1993-07-01'
as date))
AND EXISTS
(SELECT *
FROM LINEITEM
WHERE L_ORDERKEY = O_ORDERKEY
AND L_COMMITDATE < L_RECEIPTDATE)
GROUP BY O_ORDERPRIORITY
ORDER BY O_ORDERPRIORITY
Below is TPC-H Query 4, the top plan points to the database without date correlation. The second plan points to a database with correlation enabled. Notice the execution plan changes from a scan on LINEITEM to a seek
/* TPC_H Query 5 - Local Supplier Volume */
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 >= '1994-01-01'
AND O_ORDERDATE <
DATEADD(YY, 1,
cast('1994-01-01'
as date))
GROUP BY N_NAME
ORDER BY REVENUE DESC
Below is TPC-H Query 5, the top plan points to the database without date correlation. The second plan points to a database with correlation enabled. Notice the execution plan changes from a scan on LINEITEM to a seek
/* TPC_H Query 6 - Forecasting Revenue Change */
SELECT
SUM(L_EXTENDEDPRICE*L_DISCOUNT)
AS REVENUE
FROM LINEITEM
WHERE L_SHIPDATE >= '1994-01-01'
AND L_SHIPDATE <
dateadd(yy,
1, cast('1994-01-01'
as date))
AND L_DISCOUNT
BETWEEN .06 - 0.01
AND .06 + 0.01
AND L_QUANTITY < 24
/* TPC_H Query 7 - Volume Shipping */
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,
datepart(yy, 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
'1995-01-01' AND
'1996-12-31' )
AS SHIPPING
GROUP BY SUPP_NATION,
CUST_NATION, L_YEAR
ORDER BY SUPP_NATION,
CUST_NATION, L_YEAR
/* TPC_H Query 8 - National Market Share */
SELECT O_YEAR,
SUM(CASE
WHEN NATION = 'BRAZIL'
THEN VOLUME ELSE 0
END)/SUM(VOLUME)
AS MKT_SHARE
FROM (SELECT
datepart(yy,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
'1995-01-01' AND '1996-12-31'
AND P_TYPE= 'ECONOMY ANODIZED STEEL')
AS ALL_NATIONS
GROUP BY O_YEAR
ORDER BY O_YEAR
/* TPC_H Query 9 - Product Type Profit Measure */
SELECT NATION, O_YEAR,
SUM(AMOUNT)
AS SUM_PROFIT
FROM
(SELECT
N_NAME AS NATION,
datepart(yy,
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
/* TPC_H Query 10 - Returned Item Reporting */
SELECT TOP 20 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>= '1993-10-01' AND
O_ORDERDATE <
dateadd(mm, 3,
cast('1993-10-01'
as date))
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
/* TPC_H Query 11 - Important Stock Identification */
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.0001000000
FROM PARTSUPP, SUPPLIER, NATION
WHERE PS_SUPPKEY = S_SUPPKEY
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME =
'GERMANY')
ORDER BY VALUE DESC
/* TPC_H Query 12 - Shipping Modes and Order Priority */
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 >= '1994-01-01'
AND L_RECEIPTDATE <
dateadd(mm, 1,
cast('1995-09-01'
as date))
GROUP BY L_SHIPMODE
ORDER BY L_SHIPMODE
/* TPC_H Query 13 - Customer Distribution */
SELECT C_COUNT,
COUNT(*) AS CUSTDIST
FROM (SELECT
C_CUSTKEY,
COUNT(O_ORDERKEY)
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
(C_CUSTKEY, C_COUNT)
GROUP BY C_COUNT
ORDER BY CUSTDIST DESC,
C_COUNT DESC
/* TPC_H Query 14 - Promotion Effect */
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 >= '1995-09-01'
AND L_SHIPDATE <
dateadd(mm, 1,
'1995-09-01')
/* TPC_H Query 15 - Create View for Top Supplier Query */
CREATE VIEW REVENUE0
(SUPPLIER_NO, TOTAL_REVENUE)
AS
SELECT L_SUPPKEY,
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))
FROM LINEITEM
WHERE L_SHIPDATE
>= '1996-01-01'
AND L_SHIPDATE <
dateadd(mm, 3,
cast('1996-01-01'
as date))
GROUP BY L_SUPPKEY
GO
/* TPC_H Query 15 - Top Supplier */
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
DROP VIEW REVENUE0
/* TPC_H Query 16 - Parts/Supplier Relationship */
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
/* TPC_H Query 17 - Small-Quantity-Order Revenue */
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)
/* TPC_H Query 18 - Large Volume Customer */
SELECT
TOP 100 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
/* TPC_H Query 19 - Discounted Revenue */
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')
/* TPC_H Query 20 - Potential Part Promotion */
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 >=
'1994-01-01' AND
L_SHIPDATE <
dateadd(yy,1,'1994-01-01')))
AND S_NATIONKEY = N_NATIONKEY
AND N_NAME = 'CANADA'
ORDER BY S_NAME
/* TPC_H Query 21 - Suppliers Who Kept Orders Waiting */
SELECT TOP 100 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
/* TPC_H Query 22 - Global Sales Opportunity */
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
