Binary vs. Text Converted Postgresql queries test code

/*

Used with the following table and function definition:

CREATE TABLE IF NOT EXISTS mytable (
    id integer PRIMARY KEY,
    name text NOT NULL,
    int1 integer NOT NULL DEFAULT 0,
    int2 integer NOT NULL DEFAULT 0,
    int3 integer NOT NULL DEFAULT 0,
    int4 integer NOT NULL DEFAULT 0,
    float1 real NOT NULL DEFAULT 0,
    float2 real NOT NULL DEFAULT 0,
    float3 real NOT NULL DEFAULT 0,
    float4 real NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION getrecord(integer, OUT id integer, OUT name text, OUT int1 integer, OUT int2 integer, OUT int3 integer,
    OUT int4 integer, OUT float1 real, OUT float2 real, OUT float3 real, OUT float4 real)
        RETURNS SETOF record AS $$
    SELECT id,name,int1,int2,int3,int4,float1,float2,float3,float4 FROM mytable WHERE id = $1
        $$ LANGUAGE SQL;

*/

#include <stdlib.h>
#include <string.h>
#include <postgresql/libpq-fe.h>
#include <libpqtypes.h>

#define ITERATIONS 5
#define QUERIES 50000
#define RECORDS 1000

typedef struct {
    int id;
    char name[64];
    int int1, int2, int3, int4;
    float float1, float2, float3, float4;
} MyRecord;

PGconn *connection;

void Populate(void) {
    int i;
    char insert[2048];
    PGresult *result;

    // Start with an empty table
    PQexec(connection, "DELETE FROM mytable;");

    printf("Inserting %d records...\n",RECORDS);
    for (i=0; i<RECORDS; i++) {
        snprintf(insert, 2048, "INSERT INTO mytable (id,name,int1,int2,int3,int4,float1,float2,float3,float4) VALUES (%d,'%s',%d,%d,%d,%d,%f,%f,%f,%f)", i,"mystring", rand(),rand(),rand(),rand(), (float)rand()/rand(),(float)rand()/rand(), (float)rand()/rand(),(float)rand()/rand());
        //printf("%s\n",insert);
        if ((result = PQexec(connection, insert)) == NULL) {
                printf("ERROR inserting record #%d: %s\n",i,PQerrorMessage(connection));
                exit(1);
        }
        PQclear(result);
        if (i%500 == 0) {
                printf("%d..\n",i);
        }
    }
    printf("Done!\n");
}

MyRecord *GetRecordViaParam(int id) {
    PGresult *result;
    PGparam *parameters;
    MyRecord *record;

    parameters = PQparamCreate(connection);
    PQputf(parameters, "%int4", id);

    if ((result = PQparamExec(connection, parameters, "SELECT * FROM getrecord($1)", 1)) == NULL) {
    //if ((result = PQparamExec(connection, parameters, "SELECT * FROM mytable where id = $1", 1)) == NULL) {
        printf("ERROR fetching record #%d: %s\n", id, PQerrorMessage(connection));
        exit(1);
    }
    if(PQntuples(result) > 0) {
        record = malloc(sizeof(MyRecord));
        if ((PQgetf(result, 0, "%int4 %text %int4 %int4 %int4 %int4 %float4 %float4 %float4 %float4",
                0, &record->id, 1, record->name, 2, &record->int1, 3, &record->int2, 4, &record->int3,
                5, &record->int4, 6, &record->float1, 7, &record->float2, 8, &record->float3, 9, &record->float4)) == 0) {
                        printf("ERROR getting record: %s\n", PQerrorMessage(connection));
                        free(record);
                        PQclear(result);
                        return NULL;
        }
        return record;
    }
    return NULL;
}

MyRecord *GetRecordViaExecf(int id) {
    PGresult *result;
    MyRecord *record;

    if ((result = PQexecf(connection, "SELECT * FROM getrecord(%int4)", id)) == NULL) {
    //if ((result = PQexecf(connection, "SELECT * FROM mytable where id = %int4", id)) == NULL) {
        printf("ERROR fetching record #%d: %s\n", id, PQerrorMessage(connection));
        exit(1);
    }
    if(PQntuples(result) > 0) {
        record = malloc(sizeof(MyRecord));
        if ((PQgetf(result, 0, "%int4 %text %int4 %int4 %int4 %int4 %float4 %float4 %float4 %float4",
                0, &record->id, 1, record->name, 2, &record->int1, 3, &record->int2, 4, &record->int3,
                5, &record->int4, 6, &record->float1, 7, &record->float2, 8, &record->float3, 9, &record->float4)) == 0) {
                        printf("ERROR getting record: %s\n", PQerrorMessage(connection));
                        free(record);
                        PQclear(result);
                        return NULL;
        }
        return record;
    }
    return NULL;
}

MyRecord *GetRecordViaText(int id) {
    PGresult *result;
    char queryString[2048];
    MyRecord *record;

    snprintf(queryString, 2048, "SELECT * FROM getrecord(%d)", id);
    //snprintf(queryString, 2048, "SELECT * FROM mytable where id = %d", id);
    if ((result = PQexec(connection,queryString)) == NULL) {
        PQclear(result);
        return NULL;
    }
    if(PQntuples(result) > 0) {
        record = malloc(sizeof(MyRecord));
        record->id = atoi(PQgetvalue(result,0,0));
        strncpy(record->name,PQgetvalue(result,0,1),64);
        record->int1 = atoi(PQgetvalue(result,0,2));
        record->int2 = atoi(PQgetvalue(result,0,3));
        record->int3 = atoi(PQgetvalue(result,0,4));
        record->int4 = atoi(PQgetvalue(result,0,5));
        record->float1 = atof(PQgetvalue(result,0,6));
        record->float2 = atof(PQgetvalue(result,0,7));
        record->float3 = atof(PQgetvalue(result,0,8));
        record->float4 = atof(PQgetvalue(result,0,9));
        return record;
    }
    return NULL;
}

int main(int argc, char **argv) {
    int query,iteration,row;
    time_t start,end;
    MyRecord *record;

    connection = PQconnectdb("dbname = 'testdb'");
    if (PQstatus(connection) == CONNECTION_BAD) {
        PQerrorMessage(connection);
        exit(1);
    }
    PQinitTypes(connection);
    srand(time(NULL));
    Populate();

    for (iteration=0; iteration<ITERATIONS; iteration++) {
        printf("\nStarting iteration %d...\n", iteration);
        start=time(NULL);
        for(query=0; query<QUERIES; query++) {
                row = rand()%RECORDS;
#ifdef DEBUG
                printf("Fetching row %d\n", row);
#endif
                record = GetRecordViaParam(row);
                if (record != NULL) {
#ifdef DEBUG
                        printf("Returned row %5d: %6d %10d %10d %10d %10d %10.4f %10.4f %10.4f %10.4f\n",
                                row, record->id, record->int1, record->int2, record->int3, record->int4,
                                record->float1, record->float2, record->float3, record->float4);
#endif
                        free(record);
                }
        }
        end=time(NULL);
        printf("Iteration %4d: fetched %d binary records via PQparamExec in %d seconds\n", iteration, QUERIES, (int)(end-start));
        start=time(NULL);
        for(query=0; query<QUERIES; query++) {
                row = rand()%RECORDS;
#ifdef DEBUG
                printf("Fetching row %d\n", row);
#endif
                record = GetRecordViaExecf(row);
                if (record != NULL) {
#ifdef DEBUG
                        printf("Returned row %5d: %6d %10d %10d %10d %10d %10.4f %10.4f %10.4f %10.4f\n",
                                row, record->id, record->int1, record->int2, record->int3, record->int4,
                                record->float1, record->float2, record->float3, record->float4);
#endif
                        free(record);
                }
        }
        end=time(NULL);
        printf("Iteration %4d: fetched %d binary records via PQexecf in %d seconds\n", iteration, QUERIES, (int)(end-start));
        start=time(NULL);
        for(query=0; query<QUERIES; query++) {
                row = rand()%RECORDS;
#ifdef DEBUG
                printf("Fetching row %d\n", row);
#endif
                record = GetRecordViaText(row);
                if (record != NULL) {
#ifdef DEBUG
                        printf("Returned row %5d: %6d %10d %10d %10d %10d %10.4f %10.4f %10.4f %10.4f\n",
                                row, record->id, record->int1, record->int2, record->int3, record->int4,
                                record->float1, record->float2, record->float3, record->float4);
#endif
                        free(record);
                }
        }
        end=time(NULL);
        printf("Iteration %4d: fetched %d text records via PQexec in %d seconds\n", iteration, QUERIES, (int)(end-start));
    }

    PQfinish(connection);
    return 0;
}