[Gambas-user] Hopefully, an optimisation for gb.db.postgresql

Bruce Bruen bbruen at ...2308...
Mon Dec 12 09:01:26 CET 2011


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;
}








More information about the User mailing list