sure that timings aren't affected by the first query rather than the last ?
I mean rows = db(db.Word.normalform == aword.normalform).select(db.Word.id <http://db.word.id/>) On Wednesday, August 17, 2016 at 1:51:08 AM UTC+2, junde...@launchpnt.com wrote: > > 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.
