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
