{"id":1945,"date":"2011-08-17T11:17:08","date_gmt":"2011-08-17T19:17:08","guid":{"rendered":"http:\/\/blog.mozilla.org\/webdev\/?p=1945"},"modified":"2011-08-17T11:17:08","modified_gmt":"2011-08-17T19:17:08","slug":"knowing-what-you-want-pymongo","status":"publish","type":"post","link":"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/","title":{"rendered":"Knowing what you want can make a huge difference (…with pymongo)"},"content":{"rendered":"

So I’ve got a piece of\u00a0MongoKit<\/a>\u00a0code that simply wraps a\u00a0pymongo<\/a>\u00a0query and it looks like this<\/p>\n

\n
        times<\/span> =<\/span> defaultdict<\/span>(<\/span>list<\/span>)<\/span>\r\n        for<\/span> pq<\/span> in<\/span> (<\/span>db<\/span>.<\/span>PlayedQuestion<\/span>.<\/span>collection<\/span>\r\n                   .<\/span>find<\/span>({<\/span>'play.$id'<\/span>:<\/span> {<\/span>'$in'<\/span>:<\/span> play_ids<\/span>}})):<\/span>\r\n            if<\/span> pq<\/span>[<\/span>'time'<\/span>]<\/span> is<\/span> not<\/span> None<\/span>:<\/span>\r\n                if<\/span> pq<\/span>[<\/span>'right'<\/span>]:<\/span>\r\n                    times<\/span>[<\/span>'right'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n                elif<\/span> pq<\/span>[<\/span>'answer'<\/span>]:<\/span>\r\n                    times<\/span>[<\/span>'wrong'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n<\/code><\/pre>\n<\/div>\n
What I noticed was that is was quite slow. For example, looping over 12,200 documents to get this data out took a big fat\u00a0600 milliseconds<\/strong>\u00a0on average! Note that it’s not dressing things up as Python model instances as the\u00a0.collection.find()<\/code>\u00a0just returns a dict straight from pymongo.<\/p>\n
After realising that I actually only need\u00a0time<\/code>,\u00a0right<\/code>\u00a0and\u00a0answer<\/code>\u00a0I just set the fields parameter like this:<\/p>\n
\n
        times<\/span> =<\/span> defaultdict<\/span>(<\/span>list<\/span>)<\/span>\r\n        for<\/span> pq<\/span> in<\/span> (<\/span>db<\/span>.<\/span>PlayedQuestion<\/span>.<\/span>collection<\/span>\r\n                   .<\/span>find<\/span>({<\/span>'play.$id'<\/span>:<\/span> {<\/span>'$in'<\/span>:<\/span> play_ids<\/span>}},<\/span>\r\n                         fields<\/span>=<\/span>(<\/span>'time'<\/span>,<\/span>'right'<\/span>,<\/span>'answer'<\/span>)<\/span>  # the difference<\/span>\r\n                         )):<\/span>\r\n            if<\/span> pq<\/span>[<\/span>'time'<\/span>]<\/span> is<\/span> not<\/span> None<\/span>:<\/span>\r\n                if<\/span> pq<\/span>[<\/span>'right'<\/span>]:<\/span>\r\n                    times<\/span>[<\/span>'right'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n                elif<\/span> pq<\/span>[<\/span>'answer'<\/span>]:<\/span>\r\n                    times<\/span>[<\/span>'wrong'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n<\/code><\/pre>\n<\/div>\n
Now, looping over 12,200 documents only takes\u00a0200 milliseconds<\/strong>\u00a0on average. Now we’re talking!<\/p>\n
But wait a minute! If I only care about those with a time that isn’t None (null), can’t I include that in the query? Well, the\u00a0time<\/code>\u00a0field is not indexed but it’s usually a good idea to push work onto the database. Let’s try:<\/p>\n
\n
        times<\/span> =<\/span> defaultdict<\/span>(<\/span>list<\/span>)<\/span>\r\n        for<\/span> pq<\/span> in<\/span> (<\/span>db<\/span>.<\/span>PlayedQuestion<\/span>.<\/span>collection<\/span>\r\n                   .<\/span>find<\/span>({<\/span>'play.$id'<\/span>:<\/span> {<\/span>'$in'<\/span>:<\/span> play_ids<\/span>},<\/span>\r\n                          'time'<\/span>:<\/span> {<\/span>'$ne'<\/span>:<\/span> None<\/span>}},<\/span>\r\n                         fields<\/span>=<\/span>(<\/span>'time'<\/span>,<\/span>'right'<\/span>,<\/span>'answer'<\/span>)<\/span>\r\n                         )):<\/span>\r\n            if<\/span> pq<\/span>[<\/span>'right'<\/span>]:<\/span>\r\n                times<\/span>[<\/span>'right'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n            elif<\/span> pq<\/span>[<\/span>'answer'<\/span>]:<\/span>\r\n                times<\/span>[<\/span>'wrong'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n<\/code><\/pre>\n<\/div>\n
As it turns out, only 1,575 of my 12,200 documents have a time attribute that isn’t null. So doing it this way takes only about\u00a039 milliseconds<\/strong>\u00a0on average.<\/p>\n
Let’s repeat the same logic and push even more work into the database query instead. Here’s two queries instead of one but this time there’s less python control flow and all done in two queries:<\/p>\n
\n
        for<\/span> pq<\/span> in<\/span> (<\/span>db<\/span>.<\/span>PlayedQuestion<\/span>.<\/span>collection<\/span>\r\n                   .<\/span>find<\/span>({<\/span>'play.$id'<\/span>:<\/span> {<\/span>'$in'<\/span>:<\/span> play_ids<\/span>},<\/span>\r\n                          'time'<\/span>:<\/span> {<\/span>'$ne'<\/span>:<\/span> None<\/span>},<\/span>\r\n                          'right'<\/span>:<\/span> True<\/span>},<\/span>\r\n                         fields<\/span>=<\/span>(<\/span>'time'<\/span>,)<\/span>\r\n                         )):<\/span>\r\n            times<\/span>[<\/span>'right'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n\r\n        for<\/span> pq<\/span> in<\/span> (<\/span>db<\/span>.<\/span>PlayedQuestion<\/span>.<\/span>collection<\/span>\r\n                   .<\/span>find<\/span>({<\/span>'play.$id'<\/span>:<\/span> {<\/span>'$in'<\/span>:<\/span> play_ids<\/span>},<\/span>\r\n                          'time'<\/span>:<\/span> {<\/span>'$ne'<\/span>:<\/span> None<\/span>},<\/span>\r\n                          'right'<\/span>:<\/span> False<\/span>,<\/span>\r\n                          'answer'<\/span>:<\/span> {<\/span>'$ne'<\/span>:<\/span> u''<\/span>}<\/span>\r\n                          },<\/span>\r\n                         fields<\/span>=<\/span>(<\/span>'time'<\/span>,)<\/span>\r\n                         )):<\/span>\r\n            times<\/span>[<\/span>'wrong'<\/span>]<\/span>.<\/span>append<\/span>(<\/span>pq<\/span>[<\/span>'time'<\/span>])<\/span>\r\n<\/code><\/pre>\n<\/div>\n
You’d think this would be a lot faster now since the only field it returns is\u00a0time<\/code>\u00a0which is a float point number and takes up significantly less bytes compared to\u00a0answer<\/code>\u00a0which is a unicode string. However this approach takes\u00a063 milliseconds<\/strong>\u00a0on average which is worse than before. Having said that, now things are getting a bit weird because I only have 1,575 documents to loop over so a large part of that 63 milliseconds is probably spent just setting up and tearing down the query. Next: map\/reduce!<\/p>\n
As you might have noticed, this query is now ripe for an aggregate instead of this silly for-loop. So, let’s try a map reduce job on it:<\/p>\n
\n
from<\/span> bson.code<\/span> import<\/span> Code<\/span>\r\nmap_<\/span> =<\/span> Code<\/span>(<\/span>"""<\/span>\r\n function() {<\/span>\r\n   if (this.right)<\/span>\r\n     emit('right', {total:this.time, count:1});<\/span>\r\n   if (!this.right && this.answer)<\/span>\r\n     emit('wrong', {total:this.time, count:1});<\/span>\r\n }<\/span>\r\n"""<\/span>)<\/span>\r\n\r\nreduce_<\/span> =<\/span> Code<\/span>(<\/span>"""<\/span>\r\n function r( who , values ){<\/span>\r\n   var n = { total : 0 , count : 0 };<\/span>\r\n   for ( var i=0; i<values.length; i++ ){<\/span>\r\n     n.total += values[i].total;<\/span>\r\n     n.count += values[i].count;<\/span>\r\n   }<\/span>\r\n   return n;<\/span>\r\n }<\/span>\r\n"""<\/span>)<\/span>\r\n\r\nfinalize<\/span> =<\/span> Code<\/span>(<\/span>"""<\/span>\r\n function f( who , res ){<\/span>\r\n     res.avg = res.total \/ res.count;<\/span>\r\n     return res;<\/span>\r\n  }<\/span>\r\n"""<\/span>)<\/span>\r\n ...<\/span>\r\n        result<\/span> =<\/span> db<\/span>.<\/span>PlayedQuestion<\/span>.<\/span>collection<\/span>.<\/span>map_reduce<\/span>(<\/span>\r\n          map_<\/span>,<\/span> reduce_<\/span>,<\/span> 'myoutput'<\/span>,<\/span>\r\n          finalize<\/span>=<\/span>finalize<\/span>,<\/span>\r\n          query<\/span>=<\/span>{<\/span>'play.$id'<\/span>:<\/span> {<\/span>'$in'<\/span>:<\/span> play_ids<\/span>},<\/span>\r\n                 'time'<\/span>:<\/span> {<\/span>'$ne'<\/span>:<\/span> None<\/span>},<\/span>\r\n                 }<\/span>\r\n        )<\/span>\r\n        times<\/span> =<\/span> {}<\/span>\r\n        for<\/span> reduction<\/span> in<\/span> result<\/span>.<\/span>find<\/span>():<\/span>\r\n            if<\/span> reduction<\/span>[<\/span>'_id'<\/span>]<\/span> ==<\/span> 'right'<\/span>:<\/span>\r\n                times<\/span>[<\/span>'right'<\/span>]<\/span> =<\/span> reduction<\/span>[<\/span>'value'<\/span>][<\/span>'avg'<\/span>]<\/span>\r\n            elif<\/span> reduction<\/span>[<\/span>'_id'<\/span>]<\/span> ==<\/span> 'wrong'<\/span>:<\/span>\r\n                times<\/span>[<\/span>'wrong'<\/span>]<\/span> =<\/span> reduction<\/span>[<\/span>'value'<\/span>][<\/span>'avg'<\/span>]<\/span>\r\n<\/code><\/pre>\n<\/div>\n
Unfortunately all that juggling and javascript malarkey isn’t actually that performant. This takes\u00a0130 milliseconds<\/strong>\u00a0on average. Bear in mind that the query reduces the collection down to a small number (1,575) which might mean that a map\/reduce job simply isn’t the right tool.<\/p>\n
In conclusion<\/strong><\/p>\n
I think doing this optimization dance was a bit premature. The collection size is simply too little. Looping over 1.5k lightweight dicts in python is a breeze. Also, knowing what data you need from a query makes a huge difference (200% in my case).<\/p>\n
Also, by the time my database is big enough such that silly loops in the drivers don’t work, MongoDB might have the new promised\u00a0aggregate functions<\/a>\u00a0(estimated for version 2.2 (two releases away)) which will do basic operations such as\u00a0average()<\/code>\u00a0natively without the javascript parsing.<\/p>\n","protected":false},"excerpt":{"rendered":"
So I’ve got a piece of\u00a0MongoKit\u00a0code that simply wraps a\u00a0pymongo\u00a0query and it looks like this times = defaultdict(list) for pq in (db.PlayedQuestion.collection .find({'play.$id': {'$in': play_ids}})): if pq['time'] is not None: if pq['right']: times['right'].append(pq['time']) elif pq['answer']: times['wrong'].append(pq['time']) What I noticed was … Continue reading<\/a><\/p>\n","protected":false},"author":325,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4712,288],"tags":[],"coauthors":[],"yoast_head":"\nKnowing what you want can make a huge difference (...with pymongo) - Mozilla Web Development<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Peter Bengtsson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/\",\"url\":\"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/\",\"name\":\"Knowing what you want can make a huge difference (...with pymongo) - Mozilla Web Development\",\"isPartOf\":{\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/#website\"},\"datePublished\":\"2011-08-17T19:17:08+00:00\",\"dateModified\":\"2011-08-17T19:17:08+00:00\",\"author\":{\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/#\/schema\/person\/fbadf48380da3866faf204f1d1c976ea\"},\"breadcrumb\":{\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/blog.mozilla.org\/webdev\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Knowing what you want can make a huge difference (…with pymongo)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/#website\",\"url\":\"https:\/\/blog.mozilla.org\/webdev\/\",\"name\":\"Mozilla Web Development\",\"description\":\"For make benefit of glorious tubes\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.mozilla.org\/webdev\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/#\/schema\/person\/fbadf48380da3866faf204f1d1c976ea\",\"name\":\"Peter Bengtsson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/blog.mozilla.org\/webdev\/#\/schema\/person\/image\/4b8aaab120020f4d8090901be034f267\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/37d081c393f95a14e2704af38ecc4c8d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/37d081c393f95a14e2704af38ecc4c8d?s=96&d=mm&r=g\",\"caption\":\"Peter Bengtsson\"},\"description\":\"Peter is a staff web developer at Mozilla working on MDN Web Docs. He blogs on www.peterbe.com\",\"sameAs\":[\"https:\/\/www.peterbe.com\"],\"url\":\"https:\/\/blog.mozilla.org\/webdev\/author\/pbengtssonmozilla-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Knowing what you want can make a huge difference (...with pymongo) - Mozilla Web Development","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/","twitter_misc":{"Written by":"Peter Bengtsson","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/","url":"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/","name":"Knowing what you want can make a huge difference (...with pymongo) - Mozilla Web Development","isPartOf":{"@id":"https:\/\/blog.mozilla.org\/webdev\/#website"},"datePublished":"2011-08-17T19:17:08+00:00","dateModified":"2011-08-17T19:17:08+00:00","author":{"@id":"https:\/\/blog.mozilla.org\/webdev\/#\/schema\/person\/fbadf48380da3866faf204f1d1c976ea"},"breadcrumb":{"@id":"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.mozilla.org\/webdev\/2011\/08\/17\/knowing-what-you-want-pymongo\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/blog.mozilla.org\/webdev\/"},{"@type":"ListItem","position":2,"name":"Knowing what you want can make a huge difference (…with pymongo)"}]},{"@type":"WebSite","@id":"https:\/\/blog.mozilla.org\/webdev\/#website","url":"https:\/\/blog.mozilla.org\/webdev\/","name":"Mozilla Web Development","description":"For make benefit of glorious tubes","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.mozilla.org\/webdev\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/blog.mozilla.org\/webdev\/#\/schema\/person\/fbadf48380da3866faf204f1d1c976ea","name":"Peter Bengtsson","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/blog.mozilla.org\/webdev\/#\/schema\/person\/image\/4b8aaab120020f4d8090901be034f267","url":"https:\/\/secure.gravatar.com\/avatar\/37d081c393f95a14e2704af38ecc4c8d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/37d081c393f95a14e2704af38ecc4c8d?s=96&d=mm&r=g","caption":"Peter Bengtsson"},"description":"Peter is a staff web developer at Mozilla working on MDN Web Docs. He blogs on www.peterbe.com","sameAs":["https:\/\/www.peterbe.com"],"url":"https:\/\/blog.mozilla.org\/webdev\/author\/pbengtssonmozilla-com\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/posts\/1945"}],"collection":[{"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/users\/325"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/comments?post=1945"}],"version-history":[{"count":0,"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/posts\/1945\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/media?parent=1945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/categories?post=1945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/tags?post=1945"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/blog.mozilla.org\/webdev\/wp-json\/wp\/v2\/coauthors?post=1945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}