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: Select all
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: Select all
#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;
}