No clue. I tried everything I can think of. I'm trying to resolve this over
a week now. Apparently the 'FOREIGN_KEY_CHECKS' and 'NO_BACKSLASH_ESCAPES'
queries
was due to a pymysql bug, I read your issue report on github, but still no
clue on what's happening. It's really frustrating.
And the test with consecutive queries, I probably made mistake while doing
it, because I can't reproduce that one. Also, I remember, me, double
checking it.
Now, I was trying to change the MySQL driver but for some reason web2py
seems to not recognizing 'MySQLdb' library installation, now I'm trying to
fix that.
On Friday, September 5, 2014 7:58:43 AM UTC+2, Massimo Di Pierro wrote:
>
> 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 [email protected].
For more options, visit https://groups.google.com/d/optout.
