[PyQt] QSqlDatabase connection parameters

Fri Mar 19 20:29:40 GMT 2010

On Mar 19, 2010, at 12:24 PM, Russell Valentine wrote:

> Perhaps time each part to see what exactly is taking up the time?

Thanks for the suggestion.  I was hoping to find a global parameter  
(buffer_size, or similar) that could be tweaked to improve overall  
performance.

My app loads a dozen QSqlRelationalTableModel objects, in addition to  
various support data.  Remotely, each table model requires roughly 2  
to 3 seconds to load.  Locally, each requires 0.12 seconds to load!

I'm already printing timing statements to help with my optimization  
efforts to date.  Complete output is long.  Here are some highlights:

#-------------------------------------------------------------------------------
# remote data launch
#-------------------------------------------------------------------------------
2010-03-19 12:56:02.580540 Application launch

2010-03-19 12:56:05.538501 connecting...
2010-03-19 12:56:06.564915 database connection opened
[ ~1 second to open the connection ]

2010-03-19 12:56:11.091056 loading application support data...
2010-03-19 12:56:16.912604 user data loaded...
[ ~6 seconds to load QSqlRelationalTableModel objects, using  
unfiltered select() methods ]

...
2010-03-19 12:56:24.943313 form 3/12 set
2010-03-19 12:56:27.320105 form 4/12 set
...
[ ~2.5 seconds for each of 12 forms to load additional data model  
objects and set widgets accordingly ]

2010-03-19 12:56:45.861150 ready...
[ ~43 seconds to launch ]

#-------------------------------------------------------------------------------
# local data launch
#-------------------------------------------------------------------------------
2010-03-19 13:13:02.606677 Application launch
...
2010-03-19 13:13:05.733303 form 3/12 set
2010-03-19 13:13:05.854210 form 4/12 set
[ ~0.12 seconds for each of 12 forms to load additional data model  
objects and set widgets accordingly ]
...
2010-03-19 13:13:06.710319 ready...
[ ~4 seconds to launch, start to finish ]

> One
> way to do so is below:
>
> t=time.time()
> dostuff()
> print "dostuff() time = "+str(time.time()-t)+" s"
>
> Is it really the initial connection that takes a long time to get,  
> or is
> it something else on that remote machine. work_mem has nothing to do
> with making a connection for example.
>
> On 03/19/10 14:06, 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
>>
>>
>>
>> On Mar 16, 2010, at 9:09 PM, Scott Frankel wrote:
>>
>>>
>>> Hi all,
>>>
>>> Is there a buffer size or similar optimization parameter that can be
>>> set for QSqlDatabase PSQL connections?  The docs refer to PostgreSQL
>>> "options" without specifying what they may be.
>>>
>>> I have a PG database cluster of about 7MB, spread over a couple  
>>> dozen
>>> tables.  Locally, it takes about 6 seconds to launch my app, loading
>>> the model data into my forms.
>>>
>>> When I pull an identical data-set from a remote location, my
>>> application launch time increases to over 60 seconds.  Yet resource
>>> utilization on the remote server is miniscule.
>>>
>>> For comparison, I'm able to download the results of a 21MB SELECT
>>> statement in less than 8 seconds via cmd-line psql.  That's orders  
>>> of
>>> magnitude more data in an order of magnitude less time!
>>>
>>> I'm creating my db connection as follows.  (Sample code attached  
>>> also.)
>>>
>>>    db = QtSql.QSqlDatabase.addDatabase("QPSQL")
>>>    db.setDatabaseName("fubar")
>>>    db.setHostName("localhost")
>>>    db.setUserName("admin")
>>>    db.setPassword("abc123")
>>>
>>>
>>> Thanks in advance!
>>> Scott
>>>
>>>
>>> <dbTest.002.py>
>>>
>>>
>>> _______________________________________________
>>> PyQt mailing list    PyQt at riverbankcomputing.com
>>> http://www.riverbankcomputing.com/mailman/listinfo/pyqt
>>
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> PyQt mailing list    PyQt at riverbankcomputing.com
>> http://www.riverbankcomputing.com/mailman/listinfo/pyqt
>
>

Scott Frankel
President
Circle-S Studios

510-339-7477 (o)
510-332-2990 (c)

www.circlesfx.com
www.sequoyaworkflow.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.riverbankcomputing.com/pipermail/pyqt/attachments/20100319/208aaed4/attachment-0001.html>