LucidDbSqbm

From Eigenpedia

1 Overview
2 Create Schema
3 Create Data Source
4 Load Data (Unindexed)
5 Index and Analyze
6 Run Queries
- 6.1 Q1
- 6.2 Q2A
- 6.3 Q2B
- 6.4 Q3A
- 6.5 Q3B
- 6.6 Q4
- 6.7 Q5
- 6.8 Q6A
- 6.9 Q6B

Overview

This page is under construction.

This page provides the LucidDB-specific setup needed to run the classic Set Query Benchmark. These are not formal benchmark execution and reporting compliance steps; for those, see the benchmark spec. For some informal comparative results, see LucidDbSqbmComparativeResults.

[edit]

Create Schema

First, create the schema and table which will hold the loaded data:

CREATE SCHEMA SQBM;
SET SCHEMA 'SQBM';
CREATE TABLE BENCH1M (
  KSEQ  INTEGER  PRIMARY KEY,
  K2    INTEGER,
  K4    INTEGER,
  K5    INTEGER,
  K10   INTEGER,
  K25   INTEGER,
  K100  INTEGER,
  K1K   INTEGER,
  K10K  INTEGER,
  K40K  INTEGER,
  K100K INTEGER,
  K250K INTEGER,
  K500K INTEGER,
  S1    VARCHAR(8),
  S2    VARCHAR(20),
  S3    VARCHAR(20),
  S4    VARCHAR(20),
  S5    VARCHAR(20),
  S6    VARCHAR(20),
  S7    VARCHAR(20),
  S8    VARCHAR(20)
);

[edit]

Create Data Source

Download the pregenerated dataset tarball and unpack it, resulting in a directory named luciddb-sqbm-testdata containing a single file named bench1M.csv. Execute the following SQL to register the flatfile contents as a foreign table, but first change the DIRECTORY option to the location where you unpacked it.

CREATE SERVER FF_SERVER
FOREIGN DATA WRAPPER SYS_FILE_WRAPPER
OPTIONS(
    DIRECTORY '/path/to/luciddb-sqbm-testdata',
    FILE_EXTENSION '.csv',
    CTRL_FILE_EXTENSION '.bcp',
    FIELD_DELIMITER ',',
    LINE_DELIMITER '\n',
    QUOTE_CHAR '"',
    ESCAPE_CHAR '',
    WITH_HEADER 'yes',
    NUM_ROWS_SCAN '3'
);
CREATE FOREIGN TABLE BENCH_SOURCE (
    C1 INTEGER,
    C2 INTEGER,
    C4 INTEGER,
    C5 INTEGER,
    C10 INTEGER,
    C25 INTEGER,
    C100 INTEGER,
    C1K INTEGER,
    C10K INTEGER,
    C40K  INTEGER,
    C100K INTEGER,
    C250K INTEGER,
    C500K INTEGER
)
SERVER FF_SERVER
OPTIONS(
    SCHEMA_NAME 'BCP',
    FILENAME 'bench1M'
);

[edit]

Load Data (Unindexed)

Now, execute the following SQL to load the data from the flatfile into the LucidDB table. This will load actual data for the columns whose names start with K, whereas per the benchmark specs, it will synthesize constant data for the columns whose names starts with S.

INSERT INTO BENCH1M (
    KSEQ,K2,K4,K5,K10,K25,K100,K1K,K10K,K40K,K100K,K250K,K500K, S1, S2, S3, S4, S5, S6, S7, S8)
SELECT C1,C2,C4,C5,C10,C25,C100,C1K,C10K,C40K,C100K,C250K,C500K,
'12345678', '12345678900987654321', '12345678900987654321', 
'12345678900987654321', '12345678900987654321',
'12345678900987654321', 
'12345678900987654321', '12345678900987654321'
FROM BENCH_SOURCE;

[edit]

Index and Analyze

Run the following SQL to create indexes and compute histograms for all of the indexed columns:

CREATE INDEX B1M_K2_IDX ON BENCH1M(K2);
CREATE INDEX B1M_K4_IDX ON BENCH1M(K4);
CREATE INDEX B1M_K5_IDX ON BENCH1M(K5);
CREATE INDEX B1M_K10_IDX ON BENCH1M(K10);
CREATE INDEX B1M_K25_IDX ON BENCH1M(K25);
CREATE INDEX B1M_K100_IDX ON BENCH1M(K100);
CREATE INDEX B1M_K1K_IDX ON BENCH1M(K1K);
CREATE INDEX B1M_K10K_IDX ON BENCH1M(K10K);
CREATE INDEX B1M_K40K_IDX ON BENCH1M(K40K);
CREATE INDEX B1M_K100K_IDX ON BENCH1M(K100K);
CREATE INDEX B1M_K250K_IDX ON BENCH1M(K250K);
CREATE INDEX B1M_K500K_IDX ON BENCH1M(K500K);
CREATE INDEX B1M_K2_K100_IDX ON BENCH1M(K2,K100);
CREATE INDEX B1M_K4_K25_IDX ON BENCH1M(K4,K25);
CREATE INDEX B1M_K10_K25_IDX ON BENCH1M(K10,K25);
ANALYZE TABLE BENCH1M COMPUTE STATISTICS FOR COLUMNS(KSEQ,K2,K4,K5,K10,K25,K100,K1K,K10K,K40K,K100K,K250K,K500K);

[edit]

Run Queries

Here are the benchmark SQL queries. If you run them through sqlline, it will report the execution time, but for queries which return a large result set, this can be misleading (a lot of the time may be spent on printing results), so using a JDBC program to time the fetch is more reliable.

[edit]

Q1

SELECT COUNT(*) FROM BENCH1M WHERE KSEQ = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K100K = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K10K = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K1K = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K100 = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K25 = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K10 = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K5 = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K4 = 2;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2;

[edit]

Q2A

SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND KSEQ = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K100K = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K10K = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K1K = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K100 = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K25 = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K10 = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K5 = 3;
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND K4 = 3;

[edit]

Q2B

SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (KSEQ = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K100K = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K10K = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K1K = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K100 = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K25 = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K10 = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K5 = 3);
SELECT COUNT(*) FROM BENCH1M WHERE K2 = 2 AND NOT (K4 = 3);

[edit]

Q3A

SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K100K = 3;
SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K10K = 3;
SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K100 = 3;
SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K25 = 3;
SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K10 = 3;
SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K5 = 3;
SELECT SUM(K1K) FROM BENCH1M
WHERE KSEQ BETWEEN 400000 AND 500000 AND K4 = 3;

[edit]

Q3B

SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K100K = 3;
SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K10K = 3;
SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K100 = 3;
SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K25 = 3;
SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K10 = 3;
SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K5 = 3;
SELECT COUNT(*) FROM BENCH1M
WHERE
      (KSEQ BETWEEN 40000 AND 41000
  OR   KSEQ BETWEEN 42000 AND 43000
  OR   KSEQ BETWEEN 44000 AND 45000
  OR   KSEQ BETWEEN 46000 AND 47000
  OR   KSEQ BETWEEN 48000 AND 50000)
  AND  K4 = 3;

[edit]

Q4

SELECT KSEQ, K500K FROM BENCH1M
WHERE
       K2 = 1
  AND  K100 > 80
  AND  K10K BETWEEN 2000 AND 3000;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
       K100 > 80
  AND  K10K BETWEEN 2000 AND 3000
  AND  K5 = 3;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
       K10K BETWEEN 2000 AND 3000
  AND  K5 = 3
  AND  (K25 = 11 OR K25 = 19);
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K5 = 3
  AND (K25 = 11 OR K25 = 19)
  AND K4 = 3;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
     (K25 = 11 OR K25 = 19)
 AND K4 = 3
 AND K100 < 41;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
     K4 = 3
 AND K100 < 41
 AND K1K BETWEEN 850 AND 950;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
     K100 < 41
 AND K1K BETWEEN 850 AND 950
 AND K10 = 7;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K1K BETWEEN 850 AND 950
  AND K10 = 7
  AND K25 BETWEEN 3 AND 4;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K2 = 1
 AND  K100 > 80
 AND  K10K BETWEEN 2000 AND 3000
 AND  K5 = 3
 AND  (K25 = 11 OR K25 = 19);
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K100 > 80
 AND  K10K BETWEEN 2000 AND 3000
 AND  K5 = 3
 AND  (K25 = 11 OR K25 = 19)
 AND  K4 = 3;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K10K BETWEEN 2000 AND 3000
 AND  K5 = 3
 AND  (K25 = 11 OR K25 = 19)
 AND  K4 = 3
 AND  K100 < 41;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K5 = 3
 AND  (K25 = 11 OR K25 = 19)
 AND  K4 = 3
 AND  K100 < 41
 AND  K1K BETWEEN 850 AND 950;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      (K25 = 11 OR K25 = 19)
 AND  K4 = 3
 AND  K100 < 41
 AND  K1K BETWEEN 850 AND 950
 AND  K10 = 7;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
      K4 = 3
 AND  K100 < 41
 AND  K1K BETWEEN 850 AND 950
 AND  K10 = 7
 AND  K25 BETWEEN 3 AND 4;
SELECT KSEQ, K500K FROM BENCH1M
WHERE
       K100 < 41
 AND   K1K BETWEEN 850 AND 950
 AND   K10 = 7
 AND   K25 BETWEEN 3 AND 4
 AND   K2 = 1;

[edit]

Q5

SELECT K2, K100, COUNT(*) FROM BENCH1M
GROUP BY K2, K100;
SELECT K4, K25, COUNT(*) FROM BENCH1M
GROUP BY K4, K25;
SELECT K10, K25, COUNT(*) FROM BENCH1M
GROUP BY K10, K25;

[edit]

Q6A

SELECT COUNT(*) FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K100K = 49
AND  B1.K250K = B2.K500K;
SELECT COUNT(*) FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K40K = 49
AND  B1.K250K = B2.K500K;
SELECT COUNT(*) FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K10K = 49
AND  B1.K250K = B2.K500K;
SELECT COUNT(*) FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K1K = 49
AND  B1.K250K = B2.K500K;
SELECT COUNT(*) FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K100 = 49
AND  B1.K250K = B2.K500K;

[edit]

Q6B

SELECT B1.KSEQ, B2.KSEQ
FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K40K = 99
AND  B1.K250K = B2.K500K
AND  B2.K25 = 19;
SELECT B1.KSEQ, B2.KSEQ
FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K10K = 99
AND  B1.K250K = B2.K500K
AND  B2.K25 = 19;
SELECT B1.KSEQ, B2.KSEQ
FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K1K = 99
AND  B1.K250K = B2.K500K
AND  B2.K25 = 19;
SELECT B1.KSEQ, B2.KSEQ
FROM BENCH1M B2, BENCH1M B1
WHERE
     B1.K100 = 99
AND  B1.K250K = B2.K500K
AND  B2.K25 = 19;

Retrieved from "http://pub.eigenbase.org/wiki/LucidDbSqbm"

LucidDbSqbm

From Eigenpedia

Contents

Overview

Create Schema

Create Data Source

Load Data (Unindexed)

Index and Analyze

Run Queries

Q1

Q2A

Q2B

Q3A

Q3B

Q4

Q5

Q6A

Q6B

Views

Personal tools

Navigation

Search

Toolbox