Benchmark envolvendo junções de tabelas para SGBDs Relacionais

Este benchmark utiliza junções sobre 9 tabelas da Base de Dados Winestore (Hugh Williams, Web Database Applications, O'Reilly, 2002).
A consulta SQL que realiza as junções foi feita através de um aplicativo escrito em PHP para diversos SGBDs (Postgres, Firebird/Interbase, Oracle e Mysql). Em cada teste foram retiradas 0 ou uma das 9 junções expressas nas condições da cláusula where do comando select. Todas as tabelas possuem índices nas colunas de junção. Cada teste retorna apenas o número total de linhas resultantes da consulta, a fim de minimizar a saída no browser. Os tempos medidos representam, portanto, o tempo de execução da consulta no SGBD. Em alguns testes o número de linhas resultantes supera 12 milhões, onde cada linha contém todos os atributos das tabelas envolvidas na junção. (cerca de 56). O tempo de cada teste foi medido através da função microtime() do PHP (precisão de 10 ms no Linux).
Os tempos do Postgres melhoraram substancialmente após executar o comando analyze, que estabelece um query plan otimizado para todas as tabelas da BD (mas não especificamente para as consultas do benchmark - o comando é recomendado após atualizações grandes na BD).

Os testes na máquina CPU1 Pentium 4, 1.7 Ghz envolveram Firebird, Postgres e MySql e na máquina CPU2 Pentium 4, 2.0 Ghz os três primeiros mais Oracle 9i. Os testes com Oracle 8.0.5 e Oracle 7 não são comparáveis com os demais por terem sido feitos em máquinas substancialmente mais lentas.

As máquinas dos testes possuem as seguintes características:

Equipamentos dos testes
S. O. CPU1 Ram CPU2 Ram
Linux RH 7.3 Pentium 4 1.7 Ghz 256 MB Pentium 4 2.0 Ghz 512 MB
Linux RH 6.2 Pentium 2 333 Mhz 160MB    
Windows NT 4.0 Pentium 2 200 Mhz 128MB    

As tabelas da BD Winestore têm as seguintes caracteristicas:

nomelinhascolunas
customer 65017
grape_variety 212
inventory 10496
items 77807
orders 22546
region 94
wine 10486
winery 3006
wine_variety 15533

A seguir a consulta SQL onde as junções foram numeradas para facilitar a visualização na tabela de resultados.

   -- joins all tables in winestore DB             Celio G. Nov 2002
   select count(*) from
   wine w, inventory i, region r, grape_variety gv,
   wine_variety wv, winery wy, customer c, items it, orders o
1  where i.wine_id= w.wine_id
2  and w.winery_id = wy.winery_id
3  and wy.region_id = r. region_id
4  and wv.wine_id = w.wine_id
5  and wv.variety_id= gv.variety_id
6  and it.cust_id= c.cust_id
7  and it.order_id = o.order_id
8  and it.wine_id = w.wine_id
9  and o.cust_id= c.cust_id

Resultados do benchmark
tempos em segundos - menor tempo em negrito
Máquinas do grupo CPU 1
 
condição
retirada
linhas
resultantes
Fbird 1.0
1.7 Ghz
Postgres 7.2
1.7 Ghz
Mysql 3.2
1.7 Ghz
Oracle 8.0.5
333 Mhz
Oracle 7
200 Mhz
nenhuma 11.516 0.85 0.20 1.0 4.5 17.0
1 12.069.794 66 21.3 147 25 620
2 3.454.800 45 6.4 7.5 204 674
3 103.644 1.5 0.4 2.7 14 16
4 12.097.870 95 21 57.5 345 1812
5 483.672 3.4 0.7 2.4 16.2 15.4
6 5.280.144 25 9.6 32.5 1938 3479
7 49.741 1.1 0.25 0.5 15 43.7
8 12.090.120 368 15 16.8 1797 6560
9 5.303.176 5.6 6.8 26.7 1867 4822

 

 

Resultados do benchmark (tempos em segundos)
  Máquina CPU 2:   Pentium 4 - 2.0 Ghz
 
condição
retirada
linhas
resultantes
Fbird 1.0 Postgres 7.2 Mysql 3.2 Oracle 9.2.0
nenhuma 11.516 0.4 0.2 0.9 0.4
1 12.069.794 22 17.7 129 1.8
2 3.454.800 18 5.2 6.9 10
3 103.644 0.8 0.3 2.4 0.7
4 12.097.870 81 18 52 19
5 241836 0.8 0.5 2.1 0.7
6 5.280.144 6.6 5.3 31.3 118
7 49.741 0.6 0.2 0.4 1.2
8 12.090.120 371 12.1 15.5 165
9 5.303.176 3.9 5.5 25.0 116

 

Esquema da Base de Dados Winestore

-- create_tables.sql            BD Winestore
-- versão Postgres  Obs: bytea equivale a blob

CREATE TABLE users (
  cust_id int DEFAULT '0' NOT NULL,
  user_name varchar(50) DEFAULT '' NOT NULL,
  password varchar(15) DEFAULT '' NOT NULL,
  PRIMARY KEY (user_name)
);
   CREATE INDEX password on users(password);

CREATE TABLE customer (
  cust_id  serial,
  surname varchar(50) DEFAULT '' NOT NULL,
  firstname varchar(50) DEFAULT '' NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) DEFAULT '' NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) DEFAULT '' NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20),
  phone varchar(15),
  fax varchar(15),
  email varchar(30) DEFAULT '' NOT NULL,
  birth_date date DEFAULT '0000-01-01' NOT NULL,
  salary int DEFAULT '0' NOT NULL,
  PRIMARY KEY (cust_id)
);

  CREATE INDEX fullname on customer(surname,firstname);

CREATE TABLE grape_variety (
  variety_id serial,
  variety varchar(50) DEFAULT '' NOT NULL,
  PRIMARY KEY (variety_id)
);

  CREATE INDEX var on grape_variety(variety);

CREATE TABLE inventory (
  wine_id int DEFAULT '0' NOT NULL,
  inventory_id int DEFAULT '0' NOT NULL,
  on_hand int DEFAULT '0' NOT NULL,
  cost numeric(5,2) DEFAULT '0.00' NOT NULL,
  case_cost numeric(5,2) DEFAULT '0.00' NOT NULL,
  date_added timestamp,
  PRIMARY KEY (wine_id,inventory_id)
);

CREATE TABLE items (
  cust_id int DEFAULT '0' NOT NULL,
  order_id int DEFAULT '0' NOT NULL,
  item_id int DEFAULT '1' NOT NULL,
  wine_id int DEFAULT '0' NOT NULL,
  qty int,
  price numeric(5,2),
  date timestamp,
  PRIMARY KEY (cust_id,order_id,item_id)
);

CREATE TABLE orders (
  cust_id int DEFAULT '0' NOT NULL,
  order_id int DEFAULT '0' NOT NULL,
  date timestamp,
  discount numeric(3,1) DEFAULT '0.0',
  delivery numeric(4,2) DEFAULT '0.00',
  note varchar(120),
  PRIMARY KEY (cust_id,order_id)
);

CREATE TABLE region (
  region_id serial,
  region_name varchar(100) DEFAULT '' NOT NULL,
  description bytea,
  map bytea,
  PRIMARY KEY (region_id)
);

  CREATE INDEX region_name on region(region_name);

CREATE TABLE wine (
  wine_id serial,
  wine_name varchar(50) DEFAULT '' NOT NULL,
  type varchar(10) DEFAULT '' NOT NULL,
  year int DEFAULT '0' NOT NULL,
  winery_id int DEFAULT '0' NOT NULL,
  description bytea,
  PRIMARY KEY (wine_id)
);

  CREATE INDEX wine_name on wine(wine_name);
  CREATE INDEX winery_id on wine(winery_id);

CREATE TABLE wine_variety (
  wine_id int DEFAULT '0' NOT NULL,
  variety_id int DEFAULT '0' NOT NULL,
  id int DEFAULT '0' NOT NULL,
  PRIMARY KEY (wine_id,variety_id)
);

  CREATE INDEX wine_id on wine_variety(wine_id,variety_id);

CREATE TABLE winery (
  winery_id serial,
  winery_name varchar(100) DEFAULT '' NOT NULL,
  region_id int DEFAULT '0' NOT NULL,
  description bytea,
  phone varchar(15),
  fax varchar(15),
  PRIMARY KEY (winery_id)
);

  CREATE INDEX winery_name on winery(winery_name);
  CREATE INDEX region_id on winery(region_id);