How many records does it return?
There is a difference. If you execute the query using DAL, web2py
loops over the output and converts the raw output into a database
independent formatting (for example if the database returns a date as
a string, web2py converts that into a datatime.date object) and store
them into a Storage object.
There is no overhead in generating the query from DAL but there is
overhead in normalizing the output.
This is necessary because every database engine will return records in
a different format but web2py needs to make the response database
independent.
If you run with profiler you will find that most of the time is spend
in the function parse.
On Jun 14, 4:34 am, Manuele Pesenti manuele.pese...@gmail.com wrote:
Hi *,
I have a quite complex database with a lot of records distributed in
some tables, I noticed that some query seams too heavy so I tryied to
understand where is the problem. Looking with the top bash command the
CPU occupation I saw that after a few seconds (~10 or 15) of mysqld
occupation starts a long python session that occupy the biggest part of
the resources (CPU 100%)... so I realized this few code to test what
happens.
It's possible to see that the same query executed via executesql is
considerably faster than via DAL... here you can see the results of two
different query in which I duplicate the time interval...
t0 = datetime.datetime.today()
sql_query = db(join where)._select(*what, left=left_join, orderby=order)
print 'query constraction: ', datetime.datetime.today() - t0
- query constraction: 0:00:00.002458 (second try: query constraction:
0:00:00.002571)
print sql_query
- SELECT run.date, x_wind.validity, m_pow.power, x_wind.power,
x_wind.run FROM source, fc_model, run, x_temp, site, meta_data, x_wind
LEFT JOIN m_pow ON ((x_wind.validity = m_pow.validity) AND (x_wind.site
= m_pow.site)) WHERE ((x_wind.validity = x_temp.validity) AND
(x_wind.site = x_temp.site)) AND (x_wind.run = x_temp.run)) AND
(x_wind.source = x_temp.source)) AND (x_wind.site = site.id)) AND
(x_wind.run = run.id)) AND (x_wind.source = source.id)) AND
(source.model = fc_model.id)) AND (x_wind.meta_data = meta_data.id)) AND
(x_wind.validity = '2010-11-30 23:00:00') AND (x_wind.validity
'2010-12-08 22:59:00')) AND (x_wind.source IN (2))) AND
(x_wind.meta_data IN (1))) AND (x_wind.site IN (1 ORDER BY
x_wind.meta_data, x_wind.run, x_wind.source, x_wind.site,
x_wind.validity; (second try query not reported)
t0 = datetime.datetime.today()
prova = db.executesql(sql_query)
print len(prova)
- 576 (second try: 1080)
print 'query executed via raw mysql: ', datetime.datetime.today() - t0
- query executed via raw mysql: 0:00:07.264365 (second try: query
executed via raw mysql: 0:00:13.251476)
t0 = datetime.datetime.today()
data_source = db(join where).select(*what, left=left_join, orderby=order)
print 'query executed via DAL ', datetime.datetime.today() - t0
- query executed via DAL 0:03:23.115262 (second try: query executed
via DAL 0:12:19.628098)
Is there something wrong in the usage of the DAL query?
thank you very mutch in advance
Cheers
Manuele
