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

[Graeme Geldenhuys]

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.

TestProject
   |
   + 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 
work.
------------------------------------------------------------------------
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 
format:

   SELECT field1, field2, field3 FROM tablename
     WHERE OID = :OID

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 
tests.TTestModuleFlyweightFactory.TestModuleFlyweightFactory_GetModuleException: 
EDatabaseError
      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".
==============================



Regards,
   - Graeme -

-- 
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://opensoft.homeip.net/fpgui/