CODEWITHSUNDEEP
pythonperformancecouchdb
Paul Nelson
Paul Nelson

Reputation: 1341

CouchDB-Python query performance

I have several CouchDB databases. The largest is about 600k documents, and I am finding that queries are prohibitively long (several hours or more). The DB is updated infrequently (once a month or so), and only involves adding new documents, never updating existing documents.

Queries are of the type: Find all documents where key1='a' or multiple keys: key1='a', key2='b'...

I don't see that permanent views are practical here, so have been using the CouchDB-Python 'query' method.

I have tried several approaches, and I am unsure what is most efficient, or why.

Method 1: map function is:

    map_fun = '''function(doc){
        if(doc.key1=='a'){
            emit(doc.A, [doc.B, doc.C,doc.D,doc.E]);
        }
    }'''

The Python query is: results = ui.db.query(map_fun, key2=user)

Then some operation with results.rows. This takes up the most time.

It takes about an hour for 'results.rows' to come back. If I change key2 to something else, it comes back in about 5 seconds. If I repeat the original user, it's also fast.

But sometimes I need to query on more keys, so I try:

    map_fun = '''function(doc){
        if(doc.key1=='a' && doc.key2=user && doc.key3='something else' && etc.){
            emit(doc.A, [doc.B, doc.C,doc.D,doc.E]);
        }
    }'''

and use the python query:

results = ui.db.query(map_fun) Then some operation with results.rows

Takes a long time for the first query. When I change key2, takes a long time again. If I change key2 back to the original data, takes the same amount of time. (That is, nothing seems to be getting cached, B-tree'ed or whatever).

So my question is: What's the most efficient way to do queries in couchdb-python, where the queries are ad hoc and involve multiple keys for search criteria?

The UI is QT-based, using PyQt underneath.

Upvotes: 1

Views: 1772

Answers (2)

Marcin Skórzewski
Marcin Skórzewski

Reputation: 2854

I think that the fix to your problem is to create an index for the keys you are searching. It is what you called permanent view.

Note the difference between map/reduce and SQL queries in a B-tree based table:

  • simple SQL query searching for a key (if you have an index for it) traverses single path in the B+-tree from root to leaf,
  • map function reads all the elements, event if it emits small result.

What you are doing is for each query

  1. reading every document (most of the cost) and
  2. searching for a key in the emitted result (quick search in the B-tree).

and I think your solution has to be slow by the design.

If you redesign database structure to make permanent views practical, (1.) will be executed once and only (2.) will be executed for each query. Each document will be read by a view after addition to DB and a query will search in B-tree storing emitted result. If emitted set is smaller than the total documents number, then the query searches smaller structure and you have the benefit over SQL databases.

Temporary views are far less efficient, then the permanent ones and are meant to be used only for development. CouchDB was designed to work with permanent views. To make map/reduce efficient one has to implement caching or make the view permanent. I am not familiar with the details of the CouchDB implementation, perhaps second query with different key is faster because of some caching. If for some reason you have to use temporary view then perhaps CouchDB is a mistake and you should consider DBMS created and optimized for online queries like MongoDB.

Upvotes: 1

Kxepal
Kxepal

Reputation: 4679

There are two caveats for couchdb-python db.query() method:

  1. It executes temporary view. This means that code flow processing would be blocked until this all documents would be proceeded by this view. And this would happened again and again for each call. Try to save view and use db.view() method instead to get results on demand and have incremental index updates.

  2. It's reads whole result no matter how bigger it is. db.query() nor db.view() methods aren't lazy so if view result is 100 MB JSON object, you have to fetch all this data before use them somehow. To query data in more memory-optimized way, try to apply patch to have db.iterview() method - it allows you to fetch data in pagination style.

Upvotes: 3

Related Questions