[PyQt] QSqlDatabase connection parameters

Fri Mar 19 22:04:32 GMT 2010

On Mar 19, 2010, at 2:27 PM, Russell Valentine wrote:

> So if you did the exact same statements your program is doing with  
> psql
> to the remote server, they take a similar time as the local?

No, the statements to the remote server take 20x longer!
2.5 (remote) vs. 0.12 (local) seconds.

> You say similar dataset,

Identical.  I created a new database locally and on the remote server,  
then imported the same schema and test data sql files to both.

> just wondering if maybe the remote was missing
> a index or hasn't been vacuumed in a while. Can you do a vacuum  
> yourself
> just to double check?

I follow the same steps for my local db and the remote server:
- dropdb
- createdb
- import schema.sql
- import testData.sql
- launch app ...

Furthermore, monitoring usage on the remote machine shows minuscule  
load during my app's launch and operations.

I'm going to switch gears and look into multi-threading my data model  
loads and select() statements.

Thanks for the suggestions!  Much appreciated.
Scott

>
>
> Russell Valentine
>
> On 03/19/10 15:29, Scott Frankel wrote:
>>
>> 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
>>>>>
>>>>>
> _______________________________________________
> PyQt mailing list    PyQt at riverbankcomputing.com
> http://www.riverbankcomputing.com/mailman/listinfo/pyqt
>