MonetDB: default - use column_id instead of schema,table,column ...

Niels Nes Niels.Nes at cwi.nl
Sat Nov 29 10:41:17 CET 2014


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 at 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 at sip.cwi.nl
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 181 bytes
Desc: not available
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20141129/11ed83ce/attachment.sig>


More information about the developers-list mailing list