so here is his code...
SELECT TIMESTAMPDIFF(...) AS 'duration',
TIMESTAMPDIFF(...)
- INTERVAL 1 HOUR, TIMESTAMP(...)
+ INTERVAL CAST(...) AS 'timediff',
ris.ODATE as 'date',
CONCAT(...) as 'service'
FROM ... AS ris
JOIN ... as sd on ris.... = sd....
WHERE ris.... != '0000-00-00 00:00:00'
and ris.... != '0000-00-00 00:00:00'
and ris.... >= '2010-8-15'
and ris.... <= '2014-8-22'
so the timing for that is: 32.0460000038147
as far as what are the differences, well, web2py does open a transaction
and then close the transaction...
On Monday, August 25, 2014 9:24:41 AM UTC-7, Cliff Kachinske wrote:
>
> Why is '\n', the newline character, sprinkled throughout your query?
>
> See what happens if you get rid of them.
>
> On Monday, August 25, 2014 4:54:53 AM UTC-4, Mehmet A. wrote:
>>
>> Hi,
>> db.executesql() takes 30 seconds to return a result while page-loading,
>> despite the fact that same query takes 0.5-1 second if I try it on MySQL
>> console or web2py debug console or web2py shell.
>>
>> [4] dbs._timings
>>> [('SELECT 1;', 0.016000032424926758),
>>> ('SET FOREIGN_KEY_CHECKS=1;', 0.003999948501586914),
>>> ("SET sql_mode='NO_BACKSLASH_ESCAPES';", 0.00800013542175293),
>>> ("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),
>>
>> ("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)]
>>
>>
>> The query that took 32 seconds was executed with the page request. The
>> controller was falling to the debug console after db.executesql(), so the
>> second query which took 0.6 second was executed by me on the same session.
>>
>>
--
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.
