Very strange. What do you think is causing this? On Wednesday, 3 September 2014 09:44:17 UTC-5, Mehmet A. wrote: > > *db.executesql()* takes 30 seconds to return a result, despite the fact > that same query takes 0.5-1 second if I try it on the MySQL console or with > the same code on the web2py debug console or on the web2py shell. > > > I tried the following cases: > > ... #Irrelevant part of the code > raw_data = dbs.executesql(query, as_dict=True) #Takes 30 seconds to return a > resultfrom gluon.debug import dbg > dbg.set_trace() #After this line, I'm on the debug console > raw_data = dbs.executesql(query, as_dict=True) #Takes <1 second to return a > result > dbs._timings #print > > dbs._timings #From the first test above > [('SELECT 1;', 0.016000032424926758), #web2py or driver stuff, I didn't > execute it. > ('SET FOREIGN_KEY_CHECKS=1;', 0.003999948501586914), #web2py or driver > stuff, I didn't execute it. > ("SET sql_mode='NO_BACKSLASH_ESCAPES';", 0.00800013542175293), #web2py or > driver stuff, I didn't execute it. > ("SELECT TIMESTAMPDIFF(...) AS 'duration',\n > TIMESTAMPDIFF(...)\n > - INTERVAL 1 HOUR, TIMESTAMP(...)\n > + INTERVAL CAST(...) AS 'timediff',\n > ris.ODATE as 'date',\n > CONCAT(...) as 'service'\n > FROM ... AS ris\n > JOIN ... as sd on ris.... = sd....\n > WHERE ris.... != '0000-00-00 00:00:00'\n > and ris.... != '0000-00-00 00:00:00'\n > and ris.... >= '2010-8-15'\n > and ris.... <= '2014-8-22'", 32.0460000038147), # Slow > query/code, executed on page view. > ("SELECT TIMESTAMPDIFF(...) AS 'duration',\n > TIMESTAMPDIFF(...)\n > - INTERVAL 1 HOUR, TIMESTAMP(...)\n > + INTERVAL CAST(...) AS 'timediff',\n > ris.ODATE as 'date',\n > CONCAT(...) as 'service'\n > FROM ... AS ris\n > JOIN ... as sd on ris.... = sd....\n > WHERE ris.... != '0000-00-00 00:00:00'\n > and ris.... != '0000-00-00 00:00:00'\n > and ris.... >= '2010-8-15'\n > and ris.... <= '2014-8-22'", 0.6069998741149902) #Same > query/code, exec. on the debug console] > > and > > ... #Irrelevant part of the codefrom gluon.debug import dbg > dbg.set_trace() #After this line, I'm on the debug console > raw_data = dbs.executesql(query, as_dict=True) #Takes <1 second to return a > result > > Things got interesting when I try to execute more queries consecutively: > > ... #Irrelevant part of the code > raw_data = dbs.executesql(query, as_dict=True) #Takes 7 seconds to return a > result > raw_data = dbs.executesql(query, as_dict=True) #Takes 7 seconds to return a > result > raw_data = dbs.executesql(query, as_dict=True) #Takes 7 seconds to return a > resultfrom gluon.debug import dbg > dbg.set_trace() #After this line, I'm on the debug console > raw_data = dbs.executesql(query, as_dict=True) #Takes <1 second to return a > result > > on HeidiSQL > > /* Affected rows: 0 Found rows: 7,391 Warnings: 0 Duration for 1 query: > 0.000 sec. (+ 0.078 sec. network) */ > > Also posted on stackoverflow > <http://stackoverflow.com/questions/25644441/extremely-slow-raw-sql-queries-on-web2py> > >
-- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.