MonetDB create 100.000 columns

Question

I am trying to create a MonetDB database that shall hold 100k columns and approximately 2M rows of smallint type.

To generate 100k columns I am using a C code, i.e., a loop that performs the following sql request:

ALTER TABLE test ADD COLUMN s%d SMALLINT;

where %d is a number from 1 till 100000.

I observed that after 80000 sql requests each transaction takes about 15s, meaning that I need a lot of time to complete the table creation.

Could you tell me if there is a simple way of creating 100k columns?

Also, do you know what exactly what is going on with MonetDB?

Answer 1

You should use only one create table

in script shell (bash) :

#!/bin/bash

fic="/tmp/100k.sql"


col=1

echo "CREATE TABLE bigcol (" > $fic

while [[ $col -lt 100000 ]]
do
    echo "field$col SMALLINT," >> $fic

    col=$(($col + 1))

done

echo "field$col SMALLINT);" >> $fic

And in command line :

sh 100k.sh
mclient yourbdd < /tmp/100k.sql

wait about 2 minutes :D

mclient yourbdd
> \d bigcol
[ ... ... ...]
    "field99997"  SMALLINT,
    "field99998"  SMALLINT,
    "field99999"  SMALLINT,
    "field100000" SMALLINT
);

DROP TABLE bigcol is against very very long. I do not know why.

I also think it is not a good idea, but it answer your question.

Pierre