Hi Benoit, I was tracing through the postgresql library trying to figure out why some tables would add, update or delete single rows in milliseconds and others took "seconds". I found that the table_init function in gb.db.postgresql Main.c is calling the field_info function for each and every column in the table. Which in turn is querying the database catalogue for each column. Thus in a table with a large number of columns, calls to .Create and .Edit in gambas were taking close on half a second each (with a test table of 46 columns and the database on another server).
With my LIMITED C knowledge I have hacked the table_init queries so that the required catalogue columns are returned in the intial query and moved what I hope is the relevant code from field_info into table_init. My limited testing shows that .Create and .Edit call timings have now dropped to the order of .02 seconds, and the overall add, update and delete times drop to around .05 seconds. That is an order of 10 times faster. Would you please take a look at the code for table_init below and see if it makes sense to go this way. (The code copies and pastes OK into an editor like geany, even though the mail may wrap lines ... so I hope you can read it OK). regards Bruce CODE: /***************************************************************************** table_init() Initialize an info structure from table fields. <db> is the database handle. <table> is the table name. <info> points at the info structure. This function must initialize the following info fields: - info->nfield must contain the number of fields in the table. - info->fields is a char*[] pointing at the name of each field. This function returns TRUE if the command has failed, and FALSE if everything was OK. *****************************************************************************/ static int field_info(DB_DATABASE *db, const char *table, const char *field, DB_FIELD *info); static int table_init(DB_DATABASE *db, const char *table, DB_INFO *info) { // Contains some changes (**BB**) to see where we can speed up the Create method. /* char *qfield = "select pg_attribute.attname, pg_attribute.atttypid::int,pg_attribute.atttypmod " "from pg_class, pg_attribute " "where pg_class.relname = '&1' " "and (pg_class.relnamespace not in (select oid from pg_namespace where nspname = 'information_schema')) " "and pg_attribute.attnum > 0 and not pg_attribute.attisdropped " "and pg_attribute.attrelid = pg_class.oid "; char *qfield_schema = "select pg_attribute.attname, pg_attribute.atttypid::int,pg_attribute.atttypmod " "from pg_class, pg_attribute " "where pg_class.relname = '&1' " "and (pg_class.relnamespace in (select oid from pg_namespace where nspname = '&2')) " "and pg_attribute.attnum > 0 and not pg_attribute.attisdropped " "and pg_attribute.attrelid = pg_class.oid "; */ const char *qfield_all= "SELECT col.attname, col.atttypid::int, col.atttypmod, " "col.attnotnull, def.adsrc, col.atthasdef " "FROM pg_catalog.pg_class tbl, pg_catalog.pg_attribute col " "LEFT JOIN pg_catalog.pg_attrdef def ON (def.adnum = col.attnum AND def.adrelid = col.attrelid) " "WHERE tbl.relname = '&1' AND " "col.attrelid = tbl.oid AND " "col.attnum > 0 AND " "not col.attisdropped " "ORDER BY col.attnum ASC;"; char *qfield_schema_all = "select pg_attribute.attname, pg_attribute.atttypid::int,pg_attribute.atttypmod, " "pg_attribute.attnotnull, pg_attrdef.adsrc, pg_attribute.atthasdef " "from pg_class, pg_attribute " "LEFT JOIN pg_catalog.pg_attrdef ON (pg_attrdef.adnum = pg_attribute.attnum AND pg_attrdef.adrelid = pg_attribute.attrelid) " "where pg_class.relname = '&1' " "and (pg_class.relnamespace in (select oid from pg_namespace where nspname = '&2')) " "and pg_attribute.attnum > 0 and not pg_attribute.attisdropped " "and pg_attribute.attrelid = pg_class.oid "; #if DEBUG_BB fprintf(stderr, "==>main.c table_init\n"); #endif PGresult *res; int i, n; DB_FIELD *f; char *schema; // **BB** Oid type; GB_VARIANT def; char *val; /* Nom de la table */ info->table = GB.NewZeroString(table); if (get_table_schema(&table, &schema)) { fprintf(stderr,"Method A\n"); if (do_query(db,"Unable to get table fields: &1", &res, qfield_all, 1, table)) // WAS: if (do_query(db, "Unable to get table fields: &1", &res, qfield, 1, table)) return TRUE; } else { fprintf(stderr,"Method B\n"); if (do_query(db, "Unable to get table fields: &1", &res, qfield_schema_all, 2, table, schema)) //WAS: if (do_query(db, "Unable to get table fields: &1", &res, qfield_schema, 2, table, schema)) return TRUE; } info->nfield = n = PQntuples(res); if (n == 0) { PQclear(res); return TRUE; } GB.Alloc(POINTER(&info->field), sizeof(DB_FIELD) * n); for (i = 0; i < n; i++) { f = &info->field[i]; /* **BB** don't use the step by step method any more if (field_info(db, info->table, PQgetvalue(res, i, 0), f)) { PQclear(res); return TRUE; } */ f->name = GB.NewZeroString(PQgetvalue(res, i, 0)); // **BB** // determine the column data type type = atoi(PQgetvalue(res, i, 1)); f->type = conv_type(type); // if the column is a string then get the max length f->length = 0; if (f->type == GB_T_STRING) { f->length = atoi(PQgetvalue(res, i, 2)); if (f->length < 0) f->length = 0; else f->length -= 4; } // determine default vales and other stuff f->def.type = GB_T_NULL; if (conv_boolean(PQgetvalue(res, i, 3))) // if attnotnull { def.type = GB_T_VARIANT; def.value.type = GB_T_NULL; val = PQgetvalue(res, i, 4); // val=pg_attrdef.adsrc if (val && *val) { if (strncmp(val, "nextval(", 8) == 0) // nextval func so its a serial { if (f->type == GB_T_LONG) f->type = DB_T_SERIAL; } else { switch(f->type) { case GB_T_BOOLEAN: def.value.type = GB_T_BOOLEAN; def.value.value._boolean = (val[1] == 't'); break; default: DB.Query.Init(); if (!unquote_string(val, PQgetlength(res, i, 4), DB.Query.AddLength)) val = DB.Query.Get(); conv_data(val, -1, &def.value, type); } GB.StoreVariant(&def, &f->def); } } } // End **BB** /* BM's originally commented out code just moved here out of the way f->type = conv_type(atol(PQgetvalue(res, i, 1))); f->length = 0; if (f->type == GB_T_STRING) { f->length = atoi(PQgetvalue(res, i, 2)); if (f->length < 0) f->length = 0; else f->length -= 4; } */ } PQclear(res); return FALSE; } ------------------------------------------------------------------------------ Learn Windows Azure Live! Tuesday, Dec 13, 2011 Microsoft is holding a special Learn Windows Azure training event for developers. It will provide a great way to learn Windows Azure and what it provides. You can attend the event by watching it streamed LIVE online. Learn more at http://p.sf.net/sfu/ms-windowsazure _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user