On Sat, Nov 29, 2014 at 10:37:24AM +0100, Niels Nes wrote:
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 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