[Gambas-user] Hopefully, an optimisation for gb.db.postgresql
Benoît Minisini
gambas at ...1...
Tue Dec 13 06:08:15 CET 2011
Le 12/12/2011 09:01, Bruce Bruen a écrit :
> 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;
> }
>
>
Cool optimization apparently...
Can you send me your code as an attachment so that I can open it
directly in an editor to check it?
Thanks in advance!
--
Benoît Minisini
More information about the User
mailing list