It is currently Sat Jan 23, 2021 1:38 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: PostgreSQL Database benchmark results: PB vs. Pelle's C
PostPosted: Tue Dec 31, 2019 3:37 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Nov 22, 2011 5:11 pm
Posts: 329
Location: Kelowna, BC, Canada
Hardware: i3, 8 GB, 256 SSD, Windows 10 home

PostgreSQL 12.1, database with 1 table, 5,000,000 rows, row width 250 chars in 9 columns, 1 ID column

PB 5.71 vs. Pelle's C 9.00.9 - all optimizations on for both (libs and .h files for Pelle's C taken from PostgreSQL 12.1 install)

Code does a scan of entire table and prints a '.' for every 1,000 records. Millisecond timers used in both cases.

In Pelle's C it made next to no difference if code was run in IDE or as EXE outside: Avg. of three runs after warm-up: 8.02 seconds
In PB there's a noticeable difference between code in IDE and externally: 8.4 vs. 7.8

EXE sizes: PB -> 158 KB vs Pelle's C -> 40 KB + 287 KB for libpq.lib. The small size of PB's exe has me believe that Postgres Lib in PB could use some upgrading...

I guess, one can be very happy with the results!

Code for the interested below:

Code:
EnableExplicit

UsePostgreSQLDatabase()     

#Delay      = 5000 ; 5 seconds
#DBHandle   = 10
#FHandle    = 20
#ConfigFile = "pg.config"

; defaults for postgresql server access
Global PG_Config.s   = "host=localhost dbname=Test port=5432 connect_timeout=5",
       PG_User.s     = "postgres",
       PG_Password.s = "postgres"
       
; -----------------------------------------------------------------------       
Procedure OverrideDefaultConfig()
  Protected conf.s, tmp.s, conf_file.s   ;;; file content -> single string:  host=localhost dbname=Test port=5432 connect_timeout=5,user,password  Note: NO quotes!     
         
  ; if we have 'pg.config' next to the app, read it and overwrite the settings of the PG_* variables with it
  conf_file = GetPathPart(ProgramFilename()) + #ConfigFile
  If FileSize(conf_file) > 0
    If ReadFile(#FHandle, conf_file)
      ReadStringFormat(#FHandle)              ; get over BOM should one be present
      conf = ReadString(#FHandle, #PB_UTF8)
      ;
      If FindString(conf, Chr(34))
        ConsoleError("No quotes allowed in " + #ConfigFile + #CRLF$ + "Server access overrides will be ignored!"): Delay(#Delay): ProcedureReturn
      EndIf 
      tmp  = Trim(StringField(conf, 1, ","))  ; first part of line up to first comma
      If FindString(tmp, "host", #PB_String_NoCase) And FindString(tmp, "dbname", #PB_String_NoCase)  ; these 2 definitely have to be in config string
        PG_Config = tmp
      EndIf 
      tmp  = Trim(StringField(conf, 2, ","))  ; second part of line from first comma up to second comma -> user name
      If tmp > ""   
        PG_User = tmp
      EndIf 
      tmp  = Trim(StringField(conf, 3, ","))  ; third part of line from second comma up to EoL (or third comma)
      If tmp > ""   
        PG_Password = tmp
      EndIf 
    EndIf ; ReadFile
  EndIf   ; FileSize
EndProcedure 
   
; -----------------------------------------------------------------------       
Procedure OpenDB()
  If OpenDatabase(#DBHandle, PG_Config, PG_User, PG_Password)
    PrintN("Connected to PostgreSQL server")
  Else
    ConsoleError("Connection failed: " + DatabaseError() + #CRLF$ + "Terminating ..."): Delay(#Delay)
    End
  EndIf 
EndProcedure 

; -----------------------------------------------------------------------       
; program starts here
; -----------------------------------------------------------------------
Define start.q, stop.q,   ; timing variables
       counter

If Not OpenConsole("PostgreSQL Exec") ; defaults to UTF-8 output
  Print("Failed to open console - will terminate now"): Delay(#Delay)
  End
EndIf
OverrideDefaultConfig()
;start = ElapsedMilliseconds()
OpenDB()
start = ElapsedMilliseconds()
counter = 0
If DatabaseQuery(#DBHandle, ~"select * from \"Contacts\"")   ;  ~"" = raw string so we can escape double-quotes - PostgreSQL likes DB object names quoted
  While NextDatabaseRow(#DBHandle)
    counter + 1
    ; PrintN(GetDatabaseString(#DBHandle, 1))
    ;Print(".")
    If Mod(counter, 1000) = 0
      Print(".")
    EndIf
  Wend
  FinishDatabaseQuery(#DBHandle)
Else
  ConsoleError("Query failed: " + DatabaseError()): Delay(#Delay)
EndIf 
stop = ElapsedMilliseconds()
PrintN("Scanning of 5 million records took: " + FormatNumber(stop - start) + " ms."): Delay(#Delay)
;
CloseDatabase(#DBHandle) 
CloseConsole()


Code:
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <time.h>


void do_exit(PGconn *conn) {
   
    PQfinish(conn);
    exit(1);
}

void delay(unsigned int mseconds)
{
    clock_t goal = mseconds + clock();
    while (goal > clock());
}

int main(void) {
   
    PGconn *conn = PQconnectdb("host=localhost dbname=Test port=5432 user=postgres password=iwed" );

    if (PQstatus(conn) == CONNECTION_BAD) {
       
        fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
        do_exit(conn);
    }
   else
        printf("Connected!\n\n");

    clock_t start = clock(), diff;
    PGresult *res = PQexec(conn, "SELECT * FROM \"Contacts\"");   
   
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {

        printf("No data retrieved\n");       
        PQclear(res);
        do_exit(conn);
    }   
   
    int rows = PQntuples(res);
    printf("Records: %d", rows);
   
    for (int i = 0; i < rows; i ++) {
       if (i % 1000 == 0) {
          printf("%s", ".");
       }
/*        printf("%s %s %s\n",
             PQgetvalue(res, i, 0),
               PQgetvalue(res, i, 1),
            PQgetvalue(res, i, 2));
*/
    }   

    PQclear(res);

    diff = clock() - start;
    int msec = diff * 1000 / CLOCKS_PER_SEC;
    printf("\nTime taken %d seconds %d milliseconds\n\n", msec / 1000, msec % 1000);
    delay(5000);
   
    PQfinish(conn);

    return 0;
}

_________________
PB 5.73 on Windows 10 & OS X High Sierra


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 21 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye