[PyQt] QSqlDatabase connection parameters

[Scott Frankel]

On Mar 19, 2010, at 12:59 PM, Matt Newell wrote:

> 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.

I use prepared statements sparingly and only for inserts back to the db.


> 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.

Good idea.


> 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.

My initial forays into multithreading yielded warnings about pixmaps  
when I attempted to thread form construction as a whole.  I'll look  
into threading just the data load portions.


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

Yes.  Most of my recent effort has been replacing queries with data  
model operations.


> 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.

Thanks for the info!


>
> Matt
>