Hi,

I'm using SqlDB (via tiOPF framework) in our applications. Our software has a lot of db and non-db unit tests.

When I run a section of our db unit tests via SqlDB + Firebird, they complete in about 6-7 seconds. When I run that exact same set of db unit tests via SqlDB + PostgreSQL, they take about 70-90 seconds to complete!

Why is the connection to PostgreSQL so VERY slow? Is it the SqlDB+PostgreSQL components or is it something I need to tune in the PostgreSQL config settings?

Sorry if this is a dumb question, by knowledge of PostgreSQL is very limited (a week to be exact).

Regards,
- Graeme -

Most likely the SqlDB+PostgreSQL components.

Sorry if this is a dumb question, by knowledge of PostgreSQL is very limited (a week to be exact).

My guess is that for each separate transaction component, a new connection is set up. So if you create/free a lot of transaction components, this could be the cause. Instead, try to use 1 transaction component which you commit/commitretaining/rollback.

Michael.

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 -

My test suite uses DUnit2 instead of FPCUnit, so the test decorators actually work as expected, and is executed only once for all tests under the decorator.

Regards,
- Graeme -

The connection is not relevant.

You can have 1 connection component and 15 transaction components.

This will result in 15 (!) connections to the postgresql database, not 1.

Michael.

The usual way I deal with postgres - in java - is to have a connection pool. I believe this is more or less standard in pg for performance and it does work extremely well. I don't i'm afraid, have a pascal db connection pool.

bd

black dog wrote

The usual way I deal with postgres - in java - is to have a connection pool. I believe this is more or less standard in pg for performance and it does work extremely well. I don't i'm afraid, have a pascal db connection pool.

tiOPF already has a connection pool, but I don't think I'm using in our test suite - which I used to do the speed comparison. Thanks for mentioning it, I'll take another stab at it, making sure I use the tiOPF connection pool.

Regards,
- Graeme -

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