Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Binary vs. Text Converted Postgresql queries test code

By: Teeleton on Dec 10th, 2012  |  syntax: C  |  size: 8.23 KB  |  views: 64  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. /*
  2.  
  3. Used with the following table and function definition:
  4.  
  5. CREATE TABLE IF NOT EXISTS mytable (
  6.     id integer PRIMARY KEY,
  7.     name text NOT NULL,
  8.     int1 integer NOT NULL DEFAULT 0,
  9.     int2 integer NOT NULL DEFAULT 0,
  10.     int3 integer NOT NULL DEFAULT 0,
  11.     int4 integer NOT NULL DEFAULT 0,
  12.     float1 real NOT NULL DEFAULT 0,
  13.     float2 real NOT NULL DEFAULT 0,
  14.     float3 real NOT NULL DEFAULT 0,
  15.     float4 real NOT NULL DEFAULT 0
  16. );
  17.  
  18. CREATE OR REPLACE FUNCTION getrecord(integer, OUT id integer, OUT name text, OUT int1 integer, OUT int2 integer, OUT int3 integer,
  19.     OUT int4 integer, OUT float1 real, OUT float2 real, OUT float3 real, OUT float4 real)
  20.         RETURNS SETOF record AS $$
  21.     SELECT id,name,int1,int2,int3,int4,float1,float2,float3,float4 FROM mytable WHERE id = $1
  22.         $$ LANGUAGE SQL;
  23.  
  24. */
  25.  
  26. #include <stdlib.h>
  27. #include <string.h>
  28. #include <postgresql/libpq-fe.h>
  29. #include <libpqtypes.h>
  30.  
  31. #define ITERATIONS 5
  32. #define QUERIES 50000
  33. #define RECORDS 1000
  34.  
  35. typedef struct {
  36.     int id;
  37.     char name[64];
  38.     int int1, int2, int3, int4;
  39.     float float1, float2, float3, float4;
  40. } MyRecord;
  41.  
  42. PGconn *connection;
  43.  
  44. void Populate(void) {
  45.     int i;
  46.     char insert[2048];
  47.     PGresult *result;
  48.  
  49.     // Start with an empty table
  50.     PQexec(connection, "DELETE FROM mytable;");
  51.  
  52.     printf("Inserting %d records...\n",RECORDS);
  53.     for (i=0; i<RECORDS; i++) {
  54.         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());
  55.         //printf("%s\n",insert);
  56.         if ((result = PQexec(connection, insert)) == NULL) {
  57.                 printf("ERROR inserting record #%d: %s\n",i,PQerrorMessage(connection));
  58.                 exit(1);
  59.         }
  60.         PQclear(result);
  61.         if (i%500 == 0) {
  62.                 printf("%d..\n",i);
  63.         }
  64.     }
  65.     printf("Done!\n");
  66. }
  67.  
  68. MyRecord *GetRecordViaParam(int id) {
  69.     PGresult *result;
  70.     PGparam *parameters;
  71.     MyRecord *record;
  72.  
  73.     parameters = PQparamCreate(connection);
  74.     PQputf(parameters, "%int4", id);
  75.  
  76.     if ((result = PQparamExec(connection, parameters, "SELECT * FROM getrecord($1)", 1)) == NULL) {
  77.     //if ((result = PQparamExec(connection, parameters, "SELECT * FROM mytable where id = $1", 1)) == NULL) {
  78.         printf("ERROR fetching record #%d: %s\n", id, PQerrorMessage(connection));
  79.         exit(1);
  80.     }
  81.     if(PQntuples(result) > 0) {
  82.         record = malloc(sizeof(MyRecord));
  83.         if ((PQgetf(result, 0, "%int4 %text %int4 %int4 %int4 %int4 %float4 %float4 %float4 %float4",
  84.                 0, &record->id, 1, record->name, 2, &record->int1, 3, &record->int2, 4, &record->int3,
  85.                 5, &record->int4, 6, &record->float1, 7, &record->float2, 8, &record->float3, 9, &record->float4)) == 0) {
  86.                         printf("ERROR getting record: %s\n", PQerrorMessage(connection));
  87.                         free(record);
  88.                         PQclear(result);
  89.                         return NULL;
  90.         }
  91.         return record;
  92.     }
  93.     return NULL;
  94. }
  95.  
  96. MyRecord *GetRecordViaExecf(int id) {
  97.     PGresult *result;
  98.     MyRecord *record;
  99.  
  100.     if ((result = PQexecf(connection, "SELECT * FROM getrecord(%int4)", id)) == NULL) {
  101.     //if ((result = PQexecf(connection, "SELECT * FROM mytable where id = %int4", id)) == NULL) {
  102.         printf("ERROR fetching record #%d: %s\n", id, PQerrorMessage(connection));
  103.         exit(1);
  104.     }
  105.     if(PQntuples(result) > 0) {
  106.         record = malloc(sizeof(MyRecord));
  107.         if ((PQgetf(result, 0, "%int4 %text %int4 %int4 %int4 %int4 %float4 %float4 %float4 %float4",
  108.                 0, &record->id, 1, record->name, 2, &record->int1, 3, &record->int2, 4, &record->int3,
  109.                 5, &record->int4, 6, &record->float1, 7, &record->float2, 8, &record->float3, 9, &record->float4)) == 0) {
  110.                         printf("ERROR getting record: %s\n", PQerrorMessage(connection));
  111.                         free(record);
  112.                         PQclear(result);
  113.                         return NULL;
  114.         }
  115.         return record;
  116.     }
  117.     return NULL;
  118. }
  119.  
  120. MyRecord *GetRecordViaText(int id) {
  121.     PGresult *result;
  122.     char queryString[2048];
  123.     MyRecord *record;
  124.  
  125.     snprintf(queryString, 2048, "SELECT * FROM getrecord(%d)", id);
  126.     //snprintf(queryString, 2048, "SELECT * FROM mytable where id = %d", id);
  127.     if ((result = PQexec(connection,queryString)) == NULL) {
  128.         PQclear(result);
  129.         return NULL;
  130.     }
  131.     if(PQntuples(result) > 0) {
  132.         record = malloc(sizeof(MyRecord));
  133.         record->id = atoi(PQgetvalue(result,0,0));
  134.         strncpy(record->name,PQgetvalue(result,0,1),64);
  135.         record->int1 = atoi(PQgetvalue(result,0,2));
  136.         record->int2 = atoi(PQgetvalue(result,0,3));
  137.         record->int3 = atoi(PQgetvalue(result,0,4));
  138.         record->int4 = atoi(PQgetvalue(result,0,5));
  139.         record->float1 = atof(PQgetvalue(result,0,6));
  140.         record->float2 = atof(PQgetvalue(result,0,7));
  141.         record->float3 = atof(PQgetvalue(result,0,8));
  142.         record->float4 = atof(PQgetvalue(result,0,9));
  143.         return record;
  144.     }
  145.     return NULL;
  146. }
  147.  
  148. int main(int argc, char **argv) {
  149.     int query,iteration,row;
  150.     time_t start,end;
  151.     MyRecord *record;
  152.  
  153.     connection = PQconnectdb("dbname = 'testdb'");
  154.     if (PQstatus(connection) == CONNECTION_BAD) {
  155.         PQerrorMessage(connection);
  156.         exit(1);
  157.     }
  158.     PQinitTypes(connection);
  159.     srand(time(NULL));
  160.     Populate();
  161.  
  162.     for (iteration=0; iteration<ITERATIONS; iteration++) {
  163.         printf("\nStarting iteration %d...\n", iteration);
  164.         start=time(NULL);
  165.         for(query=0; query<QUERIES; query++) {
  166.                 row = rand()%RECORDS;
  167. #ifdef DEBUG
  168.                 printf("Fetching row %d\n", row);
  169. #endif
  170.                 record = GetRecordViaParam(row);
  171.                 if (record != NULL) {
  172. #ifdef DEBUG
  173.                         printf("Returned row %5d: %6d %10d %10d %10d %10d %10.4f %10.4f %10.4f %10.4f\n",
  174.                                 row, record->id, record->int1, record->int2, record->int3, record->int4,
  175.                                 record->float1, record->float2, record->float3, record->float4);
  176. #endif
  177.                         free(record);
  178.                 }
  179.         }
  180.         end=time(NULL);
  181.         printf("Iteration %4d: fetched %d binary records via PQparamExec in %d seconds\n", iteration, QUERIES, (int)(end-start));
  182.         start=time(NULL);
  183.         for(query=0; query<QUERIES; query++) {
  184.                 row = rand()%RECORDS;
  185. #ifdef DEBUG
  186.                 printf("Fetching row %d\n", row);
  187. #endif
  188.                 record = GetRecordViaExecf(row);
  189.                 if (record != NULL) {
  190. #ifdef DEBUG
  191.                         printf("Returned row %5d: %6d %10d %10d %10d %10d %10.4f %10.4f %10.4f %10.4f\n",
  192.                                 row, record->id, record->int1, record->int2, record->int3, record->int4,
  193.                                 record->float1, record->float2, record->float3, record->float4);
  194. #endif
  195.                         free(record);
  196.                 }
  197.         }
  198.         end=time(NULL);
  199.         printf("Iteration %4d: fetched %d binary records via PQexecf in %d seconds\n", iteration, QUERIES, (int)(end-start));
  200.         start=time(NULL);
  201.         for(query=0; query<QUERIES; query++) {
  202.                 row = rand()%RECORDS;
  203. #ifdef DEBUG
  204.                 printf("Fetching row %d\n", row);
  205. #endif
  206.                 record = GetRecordViaText(row);
  207.                 if (record != NULL) {
  208. #ifdef DEBUG
  209.                         printf("Returned row %5d: %6d %10d %10d %10d %10d %10.4f %10.4f %10.4f %10.4f\n",
  210.                                 row, record->id, record->int1, record->int2, record->int3, record->int4,
  211.                                 record->float1, record->float2, record->float3, record->float4);
  212. #endif
  213.                         free(record);
  214.                 }
  215.         }
  216.         end=time(NULL);
  217.         printf("Iteration %4d: fetched %d text records via PQexec in %d seconds\n", iteration, QUERIES, (int)(end-start));
  218.     }
  219.  
  220.     PQfinish(connection);
  221.     return 0;
  222. }