[PyQt] QSqlDatabase connection parameters

[Matt Newell]

On Friday 19 March 2010 12:06:12 Scott Frankel wrote:
> Hi all,
>
> Would anyone have any suggestions for improving QSqlDatabase PSQL
> connection performance?
>
>
> The Qt docs refer to PSQL connection options and demonstrate the
> "requiressl=1" example.  Are there other options that can be set?
> (eg:  the docs' PostgreSQL "options" bullet point)  Google searches
> are coming up short.
>
> I note that my local postgresql.conf file has a "work_mem" statement.
> (Usage:  work_mem = 1MB)  That could be germane, but including the
> term in my setConnectOptions() method (as part of a semi-colon
> separated list) yields PSQL errors.
>
> I've optimized my working code further.  Running from a local data
> set, launch times are down to 4 seconds.  Running from an identical
> data set hosted remotely, my application launches in 40+ seconds!
>
> Thanks in advance!
> Scott
>
I always disable prepared connection inside the qsqlpsql driver, just add a 
return false; line after the PreparedQueries case statement in the 
hasFeatures function(that's all from memory, names may be off).  This will 
only help if you are using QSqlQuery.prepare, which i use for the api 
convenience, not because i actually want prepared queries.  This will reduce 
the roundtrip count and could make a difference if roundtrip time is an 
issue.

On the server you should enable logging of queries that take over a certain 
amount of time.  This will help you narrow down places where you need indexes 
or better designed queries.

Also it's possible to use multiple connection each from it's own thread.  I 
haven't done this in pyqt alone, but have done this quite a lot in c++/qt 
apps.

Other than that just reducing the total number of queries, which can be done 
by more intelligent/complex queries, or by stored procedures.

The qsqlpsql driver is a fairly thin wrapper around psql so I don't really 
think there is a lot of performance gains to be had by changing any settings.

Matt