How to improve performance of pymongo queries
<p>I inherited an old Mongo database. Let's focus on the following two collections <em>(removed most of their content for better readability)</em>:</p>

<p>Collection user</p>

<pre><code>db.user.find_one({"email": "user@host.com"})

{'lastUpdate': datetime.datetime(2016, 9, 2, 11, 40, 13, 160000),
 'creationTime': datetime.datetime(2016, 6, 23, 7, 19, 10, 6000),
 '_id': ObjectId('576b8d6ee4b0a37270b742c7'),
 'email': 'user@host.com' }
</code></pre>

<p>Collections entry (one user to many entries):</p>

<pre><code>db.entry.find_one({"userId": _id})

{'date_entered': datetime.datetime(2015, 2, 7, 0, 0),
 'creationTime': datetime.datetime(2015, 2, 8, 14, 41, 50, 701000),
 'lastUpdate': datetime.datetime(2015, 2, 9, 3, 28, 2, 115000),
 '_id': ObjectId('54d775aee4b035e584287a42'),
 'userId': '576b8d6ee4b0a37270b742c7', 
 'data': 'test'}
</code></pre>

<p><em>As you can see, there is no DBRef between the two.</em></p>

<p>What I would like to do is to count the total number of entries, and the number of entries updated after a given date. </p>

<p>To do this I used Python's pymongo library. The code below gets me what I need, but it is painfully slow.</p>

<pre><code>from pymongo import MongoClient
client = MongoClient('mongodb://foobar/')
db = client.userdata

# First I need to fetch all user ids. Otherwise db cursor will time out after some time.
user_ids = []  # build a list of tuples (email, id)
for user in db.user.find():
    user_ids.append( (user['email'], str(user['_id'])) )

date = datetime(2016, 1, 1)
for user_id in user_ids:
    email, _id =  user_id

    t0 = time.time()

    query = {"userId": _id}
    no_of_all_entries = db.entry.find(query).count()

    query = {"userId": _id, "lastUpdate": {"$gte": date}}
    no_of_entries_this_year = db.entry.find(query).count()

    t1 = time.time()
    print("delay ", round(t1 - t0, 2))

    print(email, no_of_all_entries, no_of_entries_this_year)
</code></pre>

<p>It takes around 0.83 second to run both <code>db.entry.find</code> queries on my laptop, and  0.54 on an AWS server (not the MongoDB server).</p>

<p>Having ~20000 users it takes painful 3 hours to get all the data.
Is that the kind of latency you'd expect to see in Mongo ? What can I do to improve this ? Bear in mind that MongoDB is fairly new to me.</p>

<p>Instead of running two aggregates for all users separately you can just get both aggregates for all users with <a href="http://api.mongodb.com/python/current/examples/aggregation.html" rel="nofollow"><code>db.collection.aggregate()</code></a>.</p>

<p>And instead of a <code>(email, userId)</code> tuples we make it a dictionary as it is easier to use to get the corresponding email.</p>

<pre><code>user_emails = {str(user['_id']): user['email'] for user in db.user.find()}

date = datetime(2016, 1, 1)
entry_counts = db.entry.aggregate([
    {"$group": {
        "_id": "$userId",
        "count": {"$sum": 1},
        "count_this_year": {
            "$sum": {
                "$cond": [{"$gte": ["$lastUpdate", date]}, 1, 0]
            }
        }
    }}
])

for entry in entry_counts:
    print(user_emails.get(entry['_id']),
          entry['count'],
          entry['count_this_year'])
</code></pre>

<p>I'm pretty sure getting the user's email address into the result could be done but I'm not a mongo expert either.</p>

python mongodb python-3.x pymongo pymongo-3.x