Greetings. db.executesql() is taking 10 times longer than it should to run
a query in Web2py, and I can’t figure out why. This is causing a huge
bottleneck in my application.
In the example below, the Web2py times are all over the map, ranging from
10ms to 500ms, whereas the query runs consistenty in about 30ms in pgAdmin
III on the same server.
Yes, there’s 27 million rows in the table Word2Word, but so what? There’s
an index on every column, and as I said, the query runs consistently in
pgAdmin III. See also the query plan below.
Four Core 2 CPUs, 16 GB RAM, nobody on this server but me.
I need help. I’m completely stumped. If this requires a paid expert we
can probably accommodate that if we can get an answer.
Love Web2py. Thanks,
John D. Underhill
Senior Web Developer
Vocabulary Systems, Inc.
-------------------------------------------------
Web2py 2.14.3-stable+timestamp.2016.03.26.23.02.02
Running on Apache/2.4.7 (Ubuntu), Python 2.7.6
Postgres 9.3.13
-------------------------------------------------
. . .
rows = db(db.Word.normalform == aword.normalform).select(db.Word.id)
. . .
sql = "SELECT Word.normalform, Word.frequency, Word2Word.relscore "
sql = sql + "FROM Word2Word INNER JOIN Word ON (Word2Word.word2 = Word.id) "
sql = sql + "WHERE ((Word2Word.word1 = " + str(rows[0].id) + ") AND "
sql = sql + "(Word.frequency >= " + str(freqlower) + ") AND (Word.frequency
<= " + str(frequpper) + ")) "
sql = sql + "ORDER BY Word2Word.relscore DESC LIMIT " + str(howmany * 3) +
";"
rows = db.executesql(sql, as_dict=True, colnames=['Word.normalform',
'Word.frequency', 'Word2Word.relscore'])
. . .
for x in db._timings:
logger.debug(str(x))
-------------------------------------------------
2016-08-09 01:25:13,072 - DEBUG - (u"SELECT Word.id FROM Word WHERE
(Word.normalform = 'american');", 0.002079010009765625)
2016-08-09 01:25:13,072 - DEBUG - (u'SELECT Word.normalform,
Word.frequency, Word2Word.relscore FROM Word2Word INNER JOIN Word ON
(Word2Word.word2 = Word.id) WHERE ((Word2Word.word1 = 132) AND
(Word.frequency >= 45) AND (Word.frequency <= 65)) ORDER BY
Word2Word.relscore DESC LIMIT 15;', 0.23161005973815918)
2016-08-09 01:25:13,073 - DEBUG - (u"SELECT Word.id FROM Word WHERE
(Word.normalform = 'prospect');", 0.0020258426666259766)
2016-08-09 01:25:13,073 - DEBUG - (u'SELECT Word.normalform,
Word.frequency, Word2Word.relscore FROM Word2Word INNER JOIN Word ON
(Word2Word.word2 = Word.id) WHERE ((Word2Word.word1 = 2201) AND
(Word.frequency >= 45) AND (Word.frequency <= 65)) ORDER BY
Word2Word.relscore DESC LIMIT 15;', 0.5044560432434082)
2016-08-09 01:25:13,074 - DEBUG - (u"SELECT Word.id FROM Word WHERE
(Word.normalform = 'learn');", 0.0010268688201904297)
2016-08-09 01:25:13,074 - DEBUG - (u'SELECT Word.normalform,
Word.frequency, Word2Word.relscore FROM Word2Word INNER JOIN Word ON
(Word2Word.word2 = Word.id) WHERE ((Word2Word.word1 = 312) AND
(Word.frequency >= 45) AND (Word.frequency <= 65)) ORDER BY
Word2Word.relscore DESC LIMIT 15;', 0.009122133255004883)
2016-08-09 01:25:13,075 - DEBUG - (u"SELECT Word.id FROM Word WHERE
(Word.normalform = 'edge');", 0.0021839141845703125)
2016-08-09 01:25:13,075 - DEBUG - (u'SELECT Word.normalform,
Word.frequency, Word2Word.relscore FROM Word2Word INNER JOIN Word ON
(Word2Word.word2 = Word.id) WHERE ((Word2Word.word1 = 918) AND
(Word.frequency >= 45) AND (Word.frequency <= 65)) ORDER BY
Word2Word.relscore DESC LIMIT 15;', 0.22762799263000488)
2016-08-09 01:25:13,076 - DEBUG - (u"SELECT Word.id FROM Word WHERE
(Word.normalform = 'measure');", 0.0020809173583984375)
2016-08-09 01:25:13,077 - DEBUG - (u'SELECT Word.normalform,
Word.frequency, Word2Word.relscore FROM Word2Word INNER JOIN Word ON
(Word2Word.word2 = Word.id) WHERE ((Word2Word.word1 = 581) AND
(Word.frequency >= 45) AND (Word.frequency <= 65)) ORDER BY
Word2Word.relscore DESC LIMIT 15;', 0.47495388984680176)
-------------------------------------------------
<https://lh3.googleusercontent.com/-lIZdAtagegM/V6uZ1IT0kbI/AAAAAAAAAAw/SNGAbzYMgIEFP1BDOk8xVw5-8LTfC7vUwCLcB/s1600/q2.png>
<https://lh3.googleusercontent.com/-Mx12sfRbrlM/V6uZkWHQfEI/AAAAAAAAAAs/yNdYIAv6ll4uZcVzSagzQaQBbcSMrAebwCLcB/s1600/q1.png>
--
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.
