[fpc-pascal] SqlDB connection speed to Firebird vs PostgreSQL

Graeme Geldenhuys graemeg at opensoft.homeip.net
Fri Aug 7 14:55:04 CEST 2009

Michael Van Canneyt wrote:
> Most likely the SqlDB+PostgreSQL components.

:-(  I'll try and do other tests maybe via different components to see
if there is really such a huge speed penalty on my PostgreSQL setup. I
have heard that the default PostgreSQL installation is not tuned for
performance - but those where slightly old posts I found on the internet.

A magnitude of x15 slower is rather huge for a default install! Hence
the reason I thought I would ask here about the SqlDB components.

> My guess is that for each separate transaction component, a new
> connection is set up. So if you create/free a lot of transaction

My test suite only creates one DB connection for all db related tests. 
Test hierarchy is as follows.

   + Non-db tests
   |  + Suite1
   |  + Suite2
   + DB tests
      + Suite10
      + Suite11

"DB test" is a test decorator that sets up the DB connection. Suite10 
and Suite11 both run by using the same connection which was setup before.

Create/Destroying a DB connection per test suite (Suite10, Suite11,...) 
is *very* slow, even under Firebird.

I hope I understood your comments correctly? As you might guess, I use 
tiOPF, but I double checked the tiQuerySqlDBxx units and I don't see 
anything majorly different between the Firebird and PostgreSQL code. 
What I use is currently available in SubVersion (r1587). For others, 
tiOPF is available at: http://tiopf.sourceforge.net.

Below is the last commit log I had to make to get SqlDB + PostgreSQL to 
r1587 | graemeg | 2009-08-07 14:41:29 +0200 (Fri, 07 Aug 2009) | 13 lines
Changed paths:
     M /tiOPF2/Trunk/Options/tiQueryDataset.pas

Disabled CheckPrepared in tiQueryDataset

Prepared is automatically called for all TDataset descendants, so
this should not really have any impact. The reason why I explicitly
disabled it here is because of SqlDB & PostgreSQL. For some reason
PostgreSQL raises an error "Unknown fieldtype for parameter <>" when
prepare or CheckPrepared is called before the Parameter is retrieved
or set.

I'll investigate this further to see if this is a timing problem in
tiQueryDataset or is the SqlDB code for PostgreSQL.

If I don't do what I did above, then I get the errors as shown below. 
Any thoughts? As you can see from the error numbers (56, 57,...) I get 
errors on pretty much every sql statement. My sql is all in the normal 

   SELECT field1, field2, field3 FROM tablename

As I mentioned in the commit log, I'm not sure if this is an issue it 
SqlDB+PostgreSQL or in tiOPF SqlDB persistence layer?

  56) textrunner.SQL Database 
      at $0813D89C
  : Unknown fieldtype for parameter "OID".

  57) textrunner.SQL Database 
tests.TTestNextIDGenerator.TestGetNextID_1: EDatabaseError
      at $0813D89C
  : Unknown fieldtype for parameter "NEXT_GENERAL_ID".

   - Graeme -

fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal

More information about the fpc-pascal mailing list