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