Transcript Agenda - 中山大学软件学院

School of Software
SUN YAT-SEN UNIVERSITY
Mar, 27, 2011

The Procedure of Installing SQL Server 2005
•
Microsoft SQL Server 2005 Express
•
Microsoft SQL Server Management Studio Express

Introduction of TPC-H and Generate lineitem.tbl

Import Lineitem.tbl into SQL Server

Experiment about the Efficiency between Croup By
and Group By With Cube
•Configuration demands
•Install procedure for SQL server 2005 Express
•Install procedure for SQL server 2005 Express
•Install procedure for SQL server 2005 Express
•Install procedure for SQL server 2005 Express
•Install procedure for SQL server 2005 Express
This situation only for installing VS2005 already
•Install procedure for SQL server 2005 Express
•Install procedure for SQL server 2005 Express
•Connect to SQL Server
•Connect to SQL Server
•The interface of SQL Server
•The interface of SQL Server
The TPC Benchmark™H (TPC-H) is a decision
support benchmark.
The components of the TPC-H database are defined
to consist of eight separate and individual tables.
•Get the tpch_2_14_0
• The DBGEN program can be downloaded at the
following URL:
http://www.tpc.org/tpch/spec/tpch_2_14_0.zip
• The schema of LINEITEM can be found at page 12
in the tpch2.14.0.doc, which can be downloaded at the
following URL:
http://www.tpc.org/tpch/spec/tpch2.14.0_cb.doc
•Create lineitem.tbl (Linux)
•Create a new query
•Create database dbTPC
•Use graphical interfaces
•Use graphical interfaces
•Create the table use SQL
use dbTPC
create table lineitem
( orderkey int,
partkey int,
suppkey int,
linenumber int,
quantity int,
extendedprice decimal,
discount decimal,
tax decimal,
returnflag nchar(1),
linestatus nchar(1),
shipdate datetime,
commitdate datetime,
receiptdate datetime,
shipinstruct nchar(25),
shipmode nchar(10),
comment varchar(44)
)
•Create the table use interface
•Step 1 Import file into SQL Server Using Bulk Insert.
BULK INSERT Tablename
FROM 'D: \lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\r'
)
•When GROUP BY and Aggregate Functions are used together,
the practical meaning is significant.
• The Aggregate Functions generate a value for each group when
used together with GROUP BY, other than for the whole table.
•Example:
Display the how many lineitems are at each
returning status.
SQL:
SELECT returnflag, COUNT(*)
FROM lineitem
GROUP BY returnflag
•Example:
Display the quantity of lineitems which come from
the same order and at the same returning status.
order and they.
SQL:
SELECT returnflag, orderkey, SUM(quantity)
FROM lineitem
GROUP BY returnflag, orderkey
 The CUBE operator generates a result set that is a
multidimensional cube.
A multidimensional cube is an expansion of fact data,
The expansion is based on columns that the user wants to analyze
The cube is a result set that contains all the possible combinations
of the dimensions.
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
•These four rows report the the original sum, in another words
this time we get four groups with their sum value.
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item,Color
•These two rows report the subtotals for the Item dimension.
both have null in the Color dimension to show that aggregate
date came from rows having any value for the Color dimension.
SELECT Item, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item
•These two rows report the subtotals for the Color dimension.
both have null in the Item dimension to show that aggregate date
came from rows having any value for the item dimension.
SELECT Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Color
•This row reports the grand total for the cube. All values of both
dimensions are summarized in the row.
SELECT SUM(Quantity) AS QtySum
FROM Inventory
•Then we can extend this situation to n dimensions.
2n different combinations of the dimensions should be
considered.
•Analysis the column orderkey, partkey, suppkey, linenumber of Table
LineItem using WITH CUBE.
•Using 16 GROUP BY clauses simulate the result set of GROUP BY
WITH CUBE.
GROUP BY
No grouping(1)
Group with 4 column(1)
Group with 3 column(4)
Group with 2 column(6)
Group with 1 column(4)
orderkey, partkey, suppkey, linenumber
orderkey, partkey, suppkey
orderkey, partkey,linenumber
orderkey, suppkey, linenumber
partkey, suppkey, linenumber
orderkey, partkey
orderkey, linenumber
orderkey, suppkey
suppkey, linenumber
partkey, suppkey
partkey, linenumber
orderkey
partkey
suppkey
linenumber
Total
GROUP BY WITH CUBE
orderkey,partkey,suppkey,linenumber
millisecond
16
31
31
16
31
16
16
15
16
15
15
15
16
16
31
16
302
140
A. Use the DBGEN program of the TPC-H Benchmark to
generate all the eight tables of the TPC-H schema, with the
Scale Factor set to 1.
B. Create a database with eight tables including possible
constrains(You can refer to tpch2.14.0.doc), and then import
the generated data.
Submit all the nine queries and the time cost for importing data.
THANK YOU!