SingleStore Notebooks
New
Safe Reset for
Learn how to Optimize Performance with TPCH 100
Notebook
Note
This tutorial is meant for Standard & Premium Workspaces. You can't run this with a Free Starter Workspace due to restrictions on Storage. Create a Workspace using +group in the left nav & select Standard for this notebook. Gallery notebooks tagged with "Starter" are suitable to run on a Free Starter Workspace
Context
This notebook will help you with four core key principles for getting performance out of SingleStoreDB using TPCH Benchmark. SingleStoreDB is a distributed database, so you should think of using shard keys, database partitions, primary keys and indexes for getting the best performance out of it.
About database partitions
The generalized recommendation for most clusters is to have 4 CPU cores per database partition on each leaf. This means if you had a cluster with 16 cores on each of 4 leaves (64 CPU cores total across all leaf hosts), you would want to have 4 partitions on each leaf (16 partitions throughout the cluster). If you are using a S00 workspace, you will have 2 partitions per database. Note that increasing partitions will have additional memory and caching overheads, which can be expensive if you have thousands of tables
About shard keys
Data is distributed across the SingleStoreDB Cloud workspace into a number of partitions on the leaf nodes. The shard key is a collection of the columns in a table that are used to control how the rows of that table are distributed. To determine the partition responsible for a given row, SingleStoreDB Cloud computes a hash from all the columns in the shard key to the partition ID. Therefore, rows with the same shard key will reside on the same partition.
About hash indexes
They are highly efficient for exact-match lookups (point-reads). Because hash indexes store rows in a sparse array of buckets indexed through a hash function on the relevant columns, queries can quickly retrieve data by examining only the corresponding bucket rather than searching the entire dataset. This enables significant reduction in lookup time and hence, increased performance for specific query types.
For that tutorial, we recommend using a workspace of size S4 to ingest data faster and also see the difference and gain you can get from a distributed architecture.
Note
For that tutorial, we recommend using workspace of size S4 to ingest data faster and also see the difference and gain you can get from a distributed architecture.
Let's first create the unoptimized database
In [1]:
1# plotly and ipywidgets are pre-installed in SingleStore Helios2# No additional pip installs required for this notebook
In [2]:
1%%sql2CREATE DATABASE IF NOT EXISTS s2_tpch_unoptimized3 4# To create a database with custom partitions use the following syntax: CREATE DATABASE YourDatabaseName PARTITIONS=X;5# You cannot change after creation the number of partitions
If using a S00, the database will have 2 partitions, if using S1, it will have 8 partitions
In [3]:
1%%sql2SELECT num_partitions FROM information_schema.DISTRIBUTED_DATABASES WHERE database_name = 's2_tpch_unoptimized';
Let's create all the tables in that database with no index, shard key or primary key
Action Required
Make sure to select the s2_tpch_unoptimized database from the drop-down menu at the top of this notebook. It updates the connection_url to connect to that database.
In [4]:
1%%sql2CREATE TABLE IF NOT EXISTS `customer` (3 `c_custkey` int(11) NOT NULL,4 `c_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,5 `c_address` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,6 `c_nationkey` int(11) NOT NULL,7 `c_phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,8 `c_acctbal` decimal(15,2) NOT NULL,9 `c_mktsegment` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,10 `c_comment` varchar(117) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL11);12 13CREATE TABLE IF NOT EXISTS `lineitem` (14 `l_orderkey` bigint(11) NOT NULL,15 `l_partkey` int(11) NOT NULL,16 `l_suppkey` int(11) NOT NULL,17 `l_linenumber` int(11) NOT NULL,18 `l_quantity` decimal(15,2) NOT NULL,19 `l_extendedprice` decimal(15,2) NOT NULL,20 `l_discount` decimal(15,2) NOT NULL,21 `l_tax` decimal(15,2) NOT NULL,22 `l_returnflag` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,23 `l_linestatus` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,24 `l_shipdate` date NOT NULL,25 `l_commitdate` date NOT NULL,26 `l_receiptdate` date NOT NULL,27 `l_shipinstruct` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,28 `l_shipmode` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,29 `l_comment` varchar(44) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL30);31 32CREATE TABLE IF NOT EXISTS `nation` (33 `n_nationkey` int(11) NOT NULL,34 `n_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,35 `n_regionkey` int(11) NOT NULL,36 `n_comment` varchar(152) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL37);38 39CREATE TABLE IF NOT EXISTS `orders` (40 `o_orderkey` bigint(11) NOT NULL,41 `o_custkey` int(11) NOT NULL,42 `o_orderstatus` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,43 `o_totalprice` decimal(15,2) NOT NULL,44 `o_orderdate` date NOT NULL,45 `o_orderpriority` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,46 `o_clerk` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,47 `o_shippriority` int(11) NOT NULL,48 `o_comment` varchar(79) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL49);50 51CREATE TABLE IF NOT EXISTS `part` (52 `p_partkey` int(11) NOT NULL,53 `p_name` varchar(55) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,54 `p_mfgr` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,55 `p_brand` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,56 `p_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,57 `p_size` int(11) NOT NULL,58 `p_container` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,59 `p_retailprice` decimal(15,2) NOT NULL,60 `p_comment` varchar(23) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL61);62 63CREATE TABLE IF NOT EXISTS `partsupp` (64 `ps_partkey` int(11) NOT NULL,65 `ps_suppkey` int(11) NOT NULL,66 `ps_availqty` int(11) NOT NULL,67 `ps_supplycost` decimal(15,2) NOT NULL,68 `ps_comment` varchar(199) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL69);70 71CREATE TABLE IF NOT EXISTS `region` (72 `r_regionkey` int(11) NOT NULL,73 `r_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,74 `r_comment` varchar(152) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL75);76 77CREATE TABLE IF NOT EXISTS `supplier` (78 `s_suppkey` int(11) NOT NULL,79 `s_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,80 `s_address` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,81 `s_nationkey` int(11) NOT NULL,82 `s_phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,83 `s_acctbal` decimal(15,2) NOT NULL,84 `s_comment` varchar(101) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL85);
Now let's create the pipelines and run them to ingest data
In [5]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `customer_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/customer'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `customer`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [6]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `lineitem_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/lineitem/lineitem.'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `lineitem`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [7]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `nation_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/nation'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `nation`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [8]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `orders_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/orders'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `orders`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [9]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `partsupp_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/partsupp'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `partsupp`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [10]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `part_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/part'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `part`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [11]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `region_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/region'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `region`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [12]:
1%%sql2CREATE PIPELINE IF NOT EXISTS `supplier_pipeline`3 AS LOAD DATA S3 'memsql-tpch-dataset/sf_100/supplier'4 CONFIG '{\"region\":\"us-east-1\", \"disable_gunzip\": false}'5 BATCH_INTERVAL 25006 DISABLE OUT_OF_ORDER OPTIMIZATION7 DISABLE OFFSETS METADATA GC8 SKIP DUPLICATE KEY ERRORS9 INTO TABLE `supplier`10 FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\'11 LINES TERMINATED BY '|\n' STARTING BY '';
In [13]:
1%%sql2START PIPELINE customer_pipeline;3START PIPELINE lineitem_pipeline;4START PIPELINE nation_pipeline;5START PIPELINE orders_pipeline;6START PIPELINE partsupp_pipeline;7START PIPELINE part_pipeline;8START PIPELINE region_pipeline;9START PIPELINE supplier_pipeline;
[Optional Step] Check data ingestion in real-time
The cells below use a plain plotly + ipywidgets live chart that works in SingleStore Helios without any extra JupyterLab extensions.
In [14]:
1import threading2import time3import warnings4import sqlalchemy as sa5import plotly.graph_objs as go6import ipywidgets as widgets7from IPython.display import display8warnings.filterwarnings('ignore')
In [15]:
1_stop_event = threading.Event()2_rows_history = []3 4def _loop():5 from singlestoredb import create_engine6 engine = create_engine(database='s2_tpch_unoptimized')7 conn = None8 try:9 while not _stop_event.is_set():10 try:11 if conn is None:12 conn = engine.connect()13 val = conn.execute(sa.text(14 'SELECT COALESCE(SUM(rows_streamed),0) '15 'FROM information_schema.pipelines_batches_summary '16 "WHERE database_name='s2_tpch_unoptimized'"17 )).scalar()18 _rows_history.append(int(val or 0))19 _chart.data[0].y = _rows_history20 _chart.data[0].x = list(range(len(_rows_history)))21 except Exception as e:22 print(f'Poll error: {e}')23 if conn is not None:24 conn.close(); conn = None25 _stop_event.wait(1)26 finally:27 if conn is not None: conn.close()28 engine.dispose()
In [16]:
1# Run this cell to START the live chart (safe to re-run)2_stop_event.set() # signal any running thread to exit3_stop_event.wait(2) # wait up to 2 s for it to actually stop4_stop_event.clear()5_rows_history.clear()6_chart = go.FigureWidget(7 data=[go.Scatter(x=[], y=[], mode='lines+markers', name='rows_streamed')],8 layout=go.Layout(9 title='Track Row Ingestion',10 xaxis_title='Poll tick',11 yaxis_title='rows_streamed (cumulative)',12 )13)14display(_chart)15threading.Thread(target=_loop, daemon=True).start()
In [17]:
1# Run this cell to STOP the background thread2_stop_event.set()
Now, let's see the performance of a few queries
In [18]:
1%%sql2# TPC-H Query 1: Pricing Summary Report3SELECT4 l_returnflag,5 l_linestatus,6 SUM(l_quantity) AS sum_qty,7 SUM(l_extendedprice) AS sum_base_price,8 SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,9 SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,10 AVG(l_quantity) AS avg_qty, AVG(l_extendedprice) AS avg_price,11 AVG(l_discount) AS avg_disc,12 COUNT(*) AS count_order13FROM s2_tpch_unoptimized.lineitem14WHERE l_shipdate <= DATE('1998-12-01') - INTERVAL '90' DAY15GROUP BY l_returnflag, l_linestatus16ORDER BY l_returnflag, l_linestatus;
In [19]:
1%%sql2# TPC-H Query 4: Order Priority Checking3SELECT4 o_orderpriority,5 COUNT(*) AS order_count6FROM7 s2_tpch_unoptimized.orders8WHERE9 o_orderdate >= DATE('1993-07-01')10 AND o_orderdate < DATE('1993-10-01')11 AND EXISTS (12 SELECT *13 FROM s2_tpch_unoptimized.lineitem14 WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate15 )16GROUP BY o_orderpriority17ORDER BY o_orderpriority;
In [20]:
1%%sql2# TPC-H Query 21: Suppliers Who Kept Orders Waiting3SELECT4 s_name,5 COUNT(*) AS numwait6FROM7 s2_tpch_unoptimized.supplier,8 s2_tpch_unoptimized.lineitem l1,9 s2_tpch_unoptimized.orders,10 s2_tpch_unoptimized.nation11WHERE12 s_suppkey = l1.l_suppkey13 AND o_orderkey = l1.l_orderkey14 AND o_orderstatus = 'F'15 AND l1.l_receiptdate > l1.l_commitdate16 AND EXISTS (17 SELECT18 *19 FROM20 s2_tpch_unoptimized.lineitem l221 WHERE22 l2.l_orderkey = l1.l_orderkey23 AND l2.l_suppkey <> l1.l_suppkey24 )25 AND NOT EXISTS (26 SELECT27 *28 FROM29 s2_tpch_unoptimized.lineitem l330 WHERE31 l3.l_orderkey = l1.l_orderkey32 AND l3.l_suppkey <> l1.l_suppkey33 AND l3.l_receiptdate > l3.l_commitdate34 )35 AND s_nationkey = n_nationkey36 AND n_name = 'EGYPT'37GROUP BY38 s_name39ORDER BY40 numwait DESC,41 s_name42LIMIT 100;
Now, let's first focus on optimizing the performance
Safe Reset for s2_tpch_optimized (re-run safe)
Run the cell below only if you need to restart the optimized load from scratch (e.g. after a partial failure or kernel restart). It kills any active queries on the database, drops it, and recreates it empty. Skip it on a clean first run.
In [21]:
1import singlestoredb, sqlalchemy as sa2 3with singlestoredb.connect() as _c:4 _c.autocommit(True)5 cur = _c.cursor()6 cur.execute("""7 SELECT Id FROM information_schema.PROCESSLIST8 WHERE db = 's2_tpch_optimized' AND Id <> CONNECTION_ID()9 """)10 for (pid,) in cur.fetchall():11 try:12 cur.execute(f'KILL QUERY {pid}')13 print(f'Killed query {pid}')14 except Exception:15 pass16 cur.execute('DROP DATABASE IF EXISTS s2_tpch_optimized')17 cur.execute('CREATE DATABASE s2_tpch_optimized')18 print('s2_tpch_optimized reset complete.')
In [22]:
1%%sql2-- On a clean first run use this cell; skip if you ran the Safe Reset above.3CREATE DATABASE IF NOT EXISTS s2_tpch_optimized
Action Required
Make sure to select the s2_tpch_optimized database from the drop-down menu at the top of this notebook. It updates the connection_url to connect to that database.
Now, let's create each table with optimized data structure:
We create a unique key through primary key. For example lineitem table needs both the orderkey and linenumber to identify rows by uniqueness
We create a shard key which will distribute data in an efficient way to perform fast join and filtering. For lineitem table since we perform joins and calculation based on the orderkey we create a shardkey with orderkey
In [23]:
1%%sql2CREATE TABLE IF NOT EXISTS `lineitem` (3 `l_orderkey` bigint(11) NOT NULL,4 `l_partkey` int(11) NOT NULL,5 `l_suppkey` int(11) NOT NULL,6 `l_linenumber` int(11) NOT NULL,7 `l_quantity` decimal(15,2) NOT NULL,8 `l_extendedprice` decimal(15,2) NOT NULL,9 `l_discount` decimal(15,2) NOT NULL,10 `l_tax` decimal(15,2) NOT NULL,11 `l_returnflag` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,12 `l_linestatus` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,13 `l_shipdate` date NOT NULL,14 `l_commitdate` date NOT NULL,15 `l_receiptdate` date NOT NULL,16 `l_shipinstruct` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,17 `l_shipmode` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,18 `l_comment` varchar(44) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,19 UNIQUE KEY `pk` (`l_orderkey`,`l_linenumber`) USING HASH,20 SHARD KEY `__SHARDKEY` (`l_orderkey`),21 KEY `l_orderkey` (`l_orderkey`) USING CLUSTERED COLUMNSTORE22);23 24CREATE TABLE IF NOT EXISTS `customer` (25 `c_custkey` int(11) NOT NULL,26 `c_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,27 `c_address` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,28 `c_nationkey` int(11) NOT NULL,29 `c_phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,30 `c_acctbal` decimal(15,2) NOT NULL,31 `c_mktsegment` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,32 `c_comment` varchar(117) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,33 UNIQUE KEY `pk` (`c_custkey`) USING HASH,34 SHARD KEY `__SHARDKEY` (`c_custkey`),35 KEY `c_custkey` (`c_custkey`) USING CLUSTERED COLUMNSTORE36);37 38CREATE TABLE IF NOT EXISTS `nation` (39 `n_nationkey` int(11) NOT NULL,40 `n_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,41 `n_regionkey` int(11) NOT NULL,42 `n_comment` varchar(152) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,43 UNIQUE KEY `pk` (`n_nationkey`) USING HASH,44 SHARD KEY `__SHARDKEY` (`n_nationkey`),45 KEY `n_nationkey` (`n_nationkey`) USING CLUSTERED COLUMNSTORE46);47 48CREATE TABLE IF NOT EXISTS `orders` (49 `o_orderkey` bigint(11) NOT NULL,50 `o_custkey` int(11) NOT NULL,51 `o_orderstatus` char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,52 `o_totalprice` decimal(15,2) NOT NULL,53 `o_orderdate` date NOT NULL,54 `o_orderpriority` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,55 `o_clerk` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,56 `o_shippriority` int(11) NOT NULL,57 `o_comment` varchar(79) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,58 UNIQUE KEY `pk` (`o_orderkey`) USING HASH,59 SHARD KEY `__SHARDKEY` (`o_orderkey`),60 KEY `o_orderkey` (`o_orderkey`) USING CLUSTERED COLUMNSTORE61);62 63CREATE TABLE IF NOT EXISTS `part` (64 `p_partkey` int(11) NOT NULL,65 `p_name` varchar(55) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,66 `p_mfgr` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,67 `p_brand` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,68 `p_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,69 `p_size` int(11) NOT NULL,70 `p_container` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,71 `p_retailprice` decimal(15,2) NOT NULL,72 `p_comment` varchar(23) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,73 UNIQUE KEY `pk` (`p_partkey`) USING HASH,74 SHARD KEY `__SHARDKEY` (`p_partkey`),75 KEY `p_partkey` (`p_partkey`) USING CLUSTERED COLUMNSTORE76);77 78CREATE TABLE IF NOT EXISTS `partsupp` (79 `ps_partkey` int(11) NOT NULL,80 `ps_suppkey` int(11) NOT NULL,81 `ps_availqty` int(11) NOT NULL,82 `ps_supplycost` decimal(15,2) NOT NULL,83 `ps_comment` varchar(199) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,84 UNIQUE KEY `pk` (`ps_partkey`,`ps_suppkey`) USING HASH,85 SHARD KEY `__SHARDKEY` (`ps_partkey`),86 KEY `ps_partkey` (`ps_partkey`,`ps_suppkey`) USING CLUSTERED COLUMNSTORE87);88 89CREATE TABLE IF NOT EXISTS `region` (90 `r_regionkey` int(11) NOT NULL,91 `r_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,92 `r_comment` varchar(152) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,93 UNIQUE KEY `pk` (`r_regionkey`) USING HASH,94 SHARD KEY `__SHARDKEY` (`r_regionkey`),95 KEY `r_regionkey` (`r_regionkey`) USING CLUSTERED COLUMNSTORE96);97 98CREATE TABLE IF NOT EXISTS `supplier` (99 `s_suppkey` int(11) NOT NULL,100 `s_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,101 `s_address` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,102 `s_nationkey` int(11) NOT NULL,103 `s_phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,104 `s_acctbal` decimal(15,2) NOT NULL,105 `s_comment` varchar(101) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,106 UNIQUE KEY `pk` (`s_suppkey`) USING HASH,107 SHARD KEY `__SHARDKEY` (`s_suppkey`),108 KEY `s_suppkey` (`s_suppkey`) USING CLUSTERED COLUMNSTORE109);
Copy data from unoptimized → optimized (one table per cell)
Each INSERT uses INSERT IGNORE (skips duplicate-key rows) and a WHERE NOT EXISTS guard so rerunning a cell that already completed is a no-op. Run them in order; lineitem is last because it is the largest table. After each insert a validation cell confirms source and target row counts match.
In [24]:
1%%sql2-- nation: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`nation`4SELECT src.n_nationkey, src.n_name, src.n_regionkey, src.n_comment5FROM s2_tpch_unoptimized.`nation` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`nation` dst8 WHERE dst.n_nationkey = src.n_nationkey9);
In [25]:
1%%sql2-- Validate nation3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`nation`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`nation`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`nation`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`nation`) AS gap;
In [26]:
1%%sql2-- region: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`region`4SELECT src.r_regionkey, src.r_name, src.r_comment5FROM s2_tpch_unoptimized.`region` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`region` dst8 WHERE dst.r_regionkey = src.r_regionkey9);
In [27]:
1%%sql2-- Validate region3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`region`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`region`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`region`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`region`) AS gap;
In [28]:
1%%sql2-- supplier: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`supplier`4SELECT src.s_suppkey, src.s_name, src.s_address, src.s_nationkey, src.s_phone, src.s_acctbal, src.s_comment5FROM s2_tpch_unoptimized.`supplier` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`supplier` dst8 WHERE dst.s_suppkey = src.s_suppkey9);
In [29]:
1%%sql2-- Validate supplier3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`supplier`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`supplier`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`supplier`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`supplier`) AS gap;
In [30]:
1%%sql2-- customer: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`customer`4SELECT src.c_custkey, src.c_name, src.c_address, src.c_nationkey, src.c_phone, src.c_acctbal, src.c_mktsegment, src.c_comment5FROM s2_tpch_unoptimized.`customer` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`customer` dst8 WHERE dst.c_custkey = src.c_custkey9);
In [31]:
1%%sql2-- Validate customer3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`customer`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`customer`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`customer`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`customer`) AS gap;
In [32]:
1%%sql2-- part: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`part`4SELECT src.p_partkey, src.p_name, src.p_mfgr, src.p_brand, src.p_type, src.p_size, src.p_container, src.p_retailprice, src.p_comment5FROM s2_tpch_unoptimized.`part` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`part` dst8 WHERE dst.p_partkey = src.p_partkey9);
In [33]:
1%%sql2-- Validate part3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`part`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`part`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`part`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`part`) AS gap;
In [34]:
1%%sql2-- partsupp: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`partsupp`4SELECT src.ps_partkey, src.ps_suppkey, src.ps_availqty, src.ps_supplycost, src.ps_comment5FROM s2_tpch_unoptimized.`partsupp` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`partsupp` dst8 WHERE dst.ps_partkey = src.ps_partkey AND dst.ps_suppkey = src.ps_suppkey9);
In [35]:
1%%sql2-- Validate partsupp3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`partsupp`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`partsupp`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`partsupp`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`partsupp`) AS gap;
In [36]:
1%%sql2-- orders: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`orders`4SELECT src.o_orderkey, src.o_custkey, src.o_orderstatus, src.o_totalprice, src.o_orderdate, src.o_orderpriority, src.o_clerk, src.o_shippriority, src.o_comment5FROM s2_tpch_unoptimized.`orders` src6WHERE NOT EXISTS (7 SELECT 1 FROM s2_tpch_optimized.`orders` dst8 WHERE dst.o_orderkey = src.o_orderkey9);
In [37]:
1%%sql2-- Validate orders3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`orders`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`orders`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`orders`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`orders`) AS gap;
In [38]:
1%%sql2-- lineitem: idempotent load — skips rows already present in optimized table3INSERT IGNORE INTO s2_tpch_optimized.`lineitem`4 (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity,5 l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus,6 l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment)7SELECT8 src.l_orderkey, src.l_partkey, src.l_suppkey, src.l_linenumber, src.l_quantity,9 src.l_extendedprice, src.l_discount, src.l_tax, src.l_returnflag, src.l_linestatus,10 src.l_shipdate, src.l_commitdate, src.l_receiptdate, src.l_shipinstruct, src.l_shipmode, src.l_comment11FROM s2_tpch_unoptimized.`lineitem` src12WHERE NOT EXISTS (13 SELECT 1 FROM s2_tpch_optimized.`lineitem` dst14 WHERE dst.l_orderkey = src.l_orderkey AND dst.l_linenumber = src.l_linenumber15);
In [39]:
1%%sql2-- Validate lineitem3SELECT4 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`lineitem`) AS source_rows,5 (SELECT COUNT(*) FROM s2_tpch_optimized.`lineitem`) AS optimized_rows,6 (SELECT COUNT(*) FROM s2_tpch_unoptimized.`lineitem`) -7 (SELECT COUNT(*) FROM s2_tpch_optimized.`lineitem`) AS gap;
In [40]:
1%%sql2# TPC-H Query 1: Pricing Summary Report3SELECT4 l_returnflag,5 l_linestatus,6 SUM(l_quantity) AS sum_qty,7 SUM(l_extendedprice) AS sum_base_price,8 SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,9 SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,10 AVG(l_quantity) AS avg_qty, AVG(l_extendedprice) AS avg_price,11 AVG(l_discount) AS avg_disc,12 COUNT(*) AS count_order13FROM lineitem14WHERE l_shipdate <= DATE('1998-12-01') - INTERVAL '90' DAY15GROUP BY l_returnflag, l_linestatus16ORDER BY l_returnflag, l_linestatus;
In [41]:
1%%sql2# TPC-H Query 4: Order Priority Checking3SELECT4 o_orderpriority,5 COUNT(*) AS order_count6FROM7 s2_tpch_optimized.orders8WHERE9 o_orderdate >= DATE('1993-07-01')10 AND o_orderdate < DATE('1993-10-01')11 AND EXISTS (12 SELECT *13 FROM s2_tpch_optimized.lineitem14 WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate15 )16GROUP BY o_orderpriority17ORDER BY o_orderpriority;
In [42]:
1%%sql2# TPC-H Query 21: Suppliers Who Kept Orders Waiting3SELECT4 s_name,5 COUNT(*) AS numwait6FROM7 s2_tpch_optimized.supplier,8 s2_tpch_optimized.lineitem l1,9 s2_tpch_optimized.orders,10 s2_tpch_optimized.nation11WHERE12 s_suppkey = l1.l_suppkey13 AND o_orderkey = l1.l_orderkey14 AND o_orderstatus = 'F'15 AND l1.l_receiptdate > l1.l_commitdate16 AND EXISTS (17 SELECT18 *19 FROM20 s2_tpch_optimized.lineitem l221 WHERE22 l2.l_orderkey = l1.l_orderkey23 AND l2.l_suppkey <> l1.l_suppkey24 )25 AND NOT EXISTS (26 SELECT27 *28 FROM29 s2_tpch_optimized.lineitem l330 WHERE31 l3.l_orderkey = l1.l_orderkey32 AND l3.l_suppkey <> l1.l_suppkey33 AND l3.l_receiptdate > l3.l_commitdate34 )35 AND s_nationkey = n_nationkey36 AND n_name = 'EGYPT'37GROUP BY38 s_name39ORDER BY40 numwait desc,41 s_name42LIMIT 100;
Finally, let's do a side by side comparison between the optimized and unoptimized database
In [43]:
1from singlestoredb import create_engine2import sqlalchemy as sa3 4db_connection_unoptimized = create_engine(database='s2_tpch_unoptimized').connect()5db_connection_optimized = create_engine(database='s2_tpch_optimized').connect()
Here are a few queries that you can test side by side against. Overall you will notice an average of 4x improvement in performance
In [44]:
1sql_query4 = sa.text('''2SELECT3 o_orderpriority,4 COUNT(*) AS order_count5FROM6 s2_tpch_unoptimized.orders7WHERE8 o_orderdate >= DATE('1993-07-01')9 AND o_orderdate < DATE('1993-10-01')10 AND EXISTS (11 SELECT *12 FROM s2_tpch_unoptimized.lineitem13 WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate14 )15GROUP BY o_orderpriority16ORDER BY o_orderpriority;17''')
In [45]:
1sql_query21 = sa.text('''2SELECT3 s_name,4 COUNT(*) AS numwait5FROM6 supplier,7 lineitem l1,8 orders,9 nation10WHERE11 s_suppkey = l1.l_suppkey12 AND o_orderkey = l1.l_orderkey13 AND o_orderstatus = 'F'14 AND l1.l_receiptdate > l1.l_commitdate15 AND EXISTS (16 SELECT17 *18 FROM19 lineitem l220 WHERE21 l2.l_orderkey = l1.l_orderkey22 AND l2.l_suppkey <> l1.l_suppkey23 )24 AND NOT EXISTS (25 SELECT26 *27 FROM28 lineitem l329 WHERE30 l3.l_orderkey = l1.l_orderkey31 AND l3.l_suppkey <> l1.l_suppkey32 AND l3.l_receiptdate > l3.l_commitdate33 )34 AND s_nationkey = n_nationkey35 AND n_name = 'EGYPT'36GROUP BY37 s_name38ORDER BY39 numwait desc,40 s_name41LIMIT 100;42''')
In [46]:
1result = db_connection_optimized.execute(sql_query21)
In [47]:
1import time2import pandas as pd3import plotly.graph_objs as go4 5num_iterations = 106opt_times = []7 8for i in range(num_iterations):9 opt_start_time = time.time()10 opt_result = db_connection_optimized.execute(sql_query21)11 opt_stop_time = time.time()12 opt_times.append(opt_stop_time - opt_start_time)13 14unopt_times = []15for i in range(num_iterations):16 unopt_start_time = time.time()17 unopt_result = db_connection_unoptimized.execute(sql_query21)18 unopt_stop_time = time.time()19 unopt_times.append(unopt_stop_time - unopt_start_time)20 21x_axis = list(range(1, num_iterations + 1))22data = {23 'iteration': x_axis,24 'opt_times': opt_times,25 'unopt_times': unopt_times,26}27df = pd.DataFrame.from_dict(data)28 29fig = go.Figure()30 31# Adding optimized times to the plot32fig.add_trace(go.Scatter(x=df['iteration'], y=df['opt_times'], mode='lines+markers', name='Optimized Database'))33 34# Adding unoptimized times to the plot35fig.add_trace(go.Scatter(x=df['iteration'], y=df['unopt_times'], mode='lines+markers', name='Unoptimized Database'))36 37# Update y-axis and x-axis properties38fig.update_layout(39 title="Execution Time Comparison",40 xaxis_title="Iteration",41 yaxis_title="Time in Seconds",42 xaxis=dict(tickmode='array', tickvals=list(range(1, num_iterations + 1)))43)44 45# Show the plot46fig.show()
Details
About this Template
This notebook will help you understand how you can take advantage of SingleStoreDB distributed capability using TPCH-100.
This Notebook can be run in Standard and Enterprise deployments.
Tags
advancedperformanceturningshardkeyingesttpch
See Notebook in action
Launch this notebook in SingleStore and start executing queries instantly.
License
This Notebook has been released under the Apache 2.0 open source license.