[PyQt] QSqlDatabase connection parameters

[Russell Valentine]

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?

You say similar dataset, 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?


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