Changeset: f49a132fc195 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f49a132fc195 Modified Files: sql/backends/monet5/sql_statistics.c sql/scripts/80_statistics.sql sql/storage/store.c Branch: default Log Message:

use column_id instead of schema,table,column names to speed up insert and lookup's beware the schema for the statistics table changed. Do a drop table statistics and recreate using the create statement in

On Sat, Nov 29, 2014 at 10:37:24AM +0100, Niels Nes wrote: the 80_statistics.sql script. Niels

diffs (69 lines):

diff --git a/sql/backends/monet5/sql_statistics.c b/sql/backends/monet5/sql_statistics.c --- a/sql/backends/monet5/sql_statistics.c +++ b/sql/backends/monet5/sql_statistics.c @@ -103,7 +103,7 @@ sql_analyze(Client cntxt, MalBlkPtr mb,

if (col && strcmp(bc->name, col)) continue; - snprintf(dquery, 8192, "delete from sys.statistics where \"schema\" ='%s' and \"table\"='%s' and \"column\"='%s';", b->name, bt->name, bc->name); + snprintf(dquery, 8192, "delete from sys.statistics where \"column_id\" = %d;", c->base.id); if (samplesize > 0) { bsample = BATsample(bn, (BUN) 25000); } else @@ -147,8 +147,7 @@ sql_analyze(Client cntxt, MalBlkPtr mb, snprintf(maxval, 4, "nil"); snprintf(minval, 4, "nil"); } - snprintf(query, 8192, "insert into sys.statistics values('%s','%s','%s','%s',%d,now()," LLFMT "," LLFMT "," LLFMT "," LLFMT ",'%s','%s',%s);", b->name, bt->name, bc->name, c->type.type->sqlname, width, - (samplesize ? samplesize : sz), sz, uniq, nils, minval, maxval, sorted ? "true" : "false"); + snprintf(query, 8192, "insert into sys.statistics values(%d,'%s',%d,now()," LLFMT "," LLFMT "," LLFMT "," LLFMT ",'%s','%s',%s);", c->base.id, c->type.type->sqlname, width, (samplesize ? samplesize : sz), sz, uniq, nils, minval, maxval, sorted ? "true" : "false"); #ifdef DEBUG_SQL_STATISTICS mnstr_printf(cntxt->fdout, "%s\n", dquery); mnstr_printf(cntxt->fdout, "%s\n", query); diff --git a/sql/scripts/80_statistics.sql b/sql/scripts/80_statistics.sql --- a/sql/scripts/80_statistics.sql +++ b/sql/scripts/80_statistics.sql @@ -21,9 +21,7 @@

CREATE TABLE sys.statistics( - "schema" string, - "table" string, - "column" string, + "column_id" integer, "type" string, width integer, stamp timestamp, diff --git a/sql/storage/store.c b/sql/storage/store.c --- a/sql/storage/store.c +++ b/sql/storage/store.c @@ -4317,12 +4317,8 @@ sql_trans_dist_count( sql_trans *tr, sql sql_schema *sys = find_sql_schema(tr, "sys"); sql_table *stats = find_sql_table(sys, "statistics"); if (stats) { - sql_column *stats_schema = find_sql_column(stats, "schema"); - sql_column *stats_table = find_sql_column(stats, "table"); - sql_column *stats_column = find_sql_column(stats, "column"); - oid rid = table_funcs.column_find_row(tr, stats_column, col->base.name, NULL, - stats_table, col->t->base.name, NULL, - stats_schema, col->t->s->base.name, NULL, NULL); + sql_column *stats_column_id = find_sql_column(stats, "column_id"); + oid rid = table_funcs.column_find_row(tr, stats_column_id, &col->base.id, NULL); if (rid != oid_nil) { sql_column *stats_unique = find_sql_column(stats, "unique"); void *v = table_funcs.column_find_value(tr, stats_unique, rid); @@ -4344,12 +4340,8 @@ sql_trans_ranges( sql_trans *tr, sql_col sql_schema *sys = find_sql_schema(tr, "sys"); sql_table *stats = find_sql_table(sys, "statistics"); if (stats) { - sql_column *stats_schema = find_sql_column(stats, "schema"); - sql_column *stats_table = find_sql_column(stats, "table"); - sql_column *stats_column = find_sql_column(stats, "column"); - oid rid = table_funcs.column_find_row(tr, stats_column, col->base.name, NULL, - stats_table, col->t->base.name, NULL, - stats_schema, col->t->s->base.name, NULL, NULL); + sql_column *stats_column_id = find_sql_column(stats, "column_id"); + oid rid = table_funcs.column_find_row(tr, stats_column_id, &col->base.id, NULL); if (rid != oid_nil) { sql_column *stats_min = find_sql_column(stats, "minval"); sql_column *stats_max = find_sql_column(stats, "maxval"); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl