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 <[email protected]> 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
