Hi,
I'm new to web2py - first of all, thanks for such an excellent
framework. It's certainly a breath of fresh air after writing J2EE
webapps.
Anyway, I'm building an application, part of which plots large
datasets (using the JS Highstocks library). I've defined a table like
this:
db.define_table('Underway_Data',
Field('UTCtime', 'datetime'),
Field('waterTemp', 'double'),
Field('waterTempQC', 'integer'),
Field('latitude', 'double'),
Field('latitudeQC', 'integer'),
Field('longitude', 'double'),
Field('longitudeQC', 'integer'))
and populated it with data, about 175k rows (postgres on ubuntu,
web2py is residing on the same server and setup using the 'one step
production deployment' script)
Running the database query:
{{resolution=5}}
{{query = (db.Underway_Data.id%resolution==0)}}
{{dataSet = db(query)}}
{{tic = time.clock()}}
{{rows = dataSet.select()}}
{{toc = time.clock()}}
will take about 4.5 seconds (the resolution variable subsamples the
data for performance reasons).
While it is a fair amount of data to be pushing around, 4.5 seconds
seems slow to me. I wrote a simple benchmark in pure python:
import psycopg2, time
tic = time.clock()
conn = psycopg2.connect("host=ubuntu-geospatial-server user=postgres
password= dbname=geospatial")
cur = conn.cursor()
cur.execute("SELECT Underway_Data.id, Underway_Data.UTCtime,
Underway_Data.waterTemp, Underway_Data.waterTempQC,
Underway_Data.latitude, Underway_Data.latitudeQC,
Underway_Data.longitude, Underway_Data.longitudeQC FROM Underway_Data
WHERE ((Underway_Data.id % 5) = 0);")
rows = cur.fetchall()
toc = time.clock()
print toc-tic
cur.close()
conn.close()
which runs in ~0.13 seconds.
(the query I use is the output of db(query)._select() )
Am I missing something here? Is there anything I can do to increase
performance?
Regards,
Hugh.
