Re: Sql command sequence

[Marco Ciaschini]

Ok I made some tests.

I tested 3 DBMS: MySQL, PostgreSQL and SQLServer with the same data bulk file. This file contains about 120000 rows and a total dimension of 50 MB, not very large for a business database table, but I think it's quite big to appreciate difference between the two approaches described.


Sequence 1: CREATE TABLE - LOAD BULK - CREATE INDEXES
MYSQL
- create table (s): 7
- load bulk (s): 26
- create indexes(s): 3667
- total (s): 3700

POSTGRES
- create table (s): 5
- load bulk (s): 50
- create indexes(s): 83
- total (s): 509

SQLSERVER
- create table (s): 12
- load bulk (s): 21
- create indexes(s): 24
- total (s): 426

----------------
Sequence 2: CREATE TABLE - CREATE INDEXES - LOAD BULK
MYSQL
- create table (s): 1
- load bulk (s): 0
- create indexes(s): 565
- total (s): 893

POSTGRES
- create table (s): 1
- load bulk (s): 1
- create indexes(s): 499
- total (s): 883

SQLSERVER
- create table (s): 11
- load bulk (s): 2
- create indexes(s): 100
- total (s): 462


So, it seems that creating indexes after the bulk load is better for Postgres and SQLServer (Postgres takes more advantages from this solution, while SQLServer performances are almost the same in both cases).
But this approach in MySQL is totally wrong! The time needed for create indexes after the bulk load grows enormously! For MySQL is better to create indexes first, and then load data. And the difference between the two approaches is huge!
This seems strange if compared to the behaviour of Postgres and SqlServer.

Overall, SqlServer is the fastest DBMS in both cases, while MySQL is the slowest one.

What do you think about?