Slow queries with medium database (3-5 seconds)

[James Norman]

I'm currently experiencing very slow queries against a somewhat large database.  

The database has 21693 documents (via createAllDocumentsQuery().run().getCount()) with 5-10 fields of small strings or numbers.  I'm experiencing queries of 3-5 seconds using a normal query or live view, the number of returned records in the query is 30.  I'm positive the emitter is not being run on each query.

My View simply emits the id of its parent object.

        view.setMap(new Mapper() {

            @Override

            public void map(Map<String, Object> document, Emitter emitter) {

                Object classType = document.get(AssayRTApplication.CLASS_KEY);

                if( Flow.class.getName().equals( classType ) ) {

                    emitter.emit( document.get( "assay_guid" ), document);

                }

            }

        }, "2" );

And the query is as follows:

            Query query2 = view.createQuery();

            ArrayList keys2 = new ArrayList();

            keys2.add( assayId );

            query2.setKeys( keys2 );

            QueryEnumerator enum1 = query2.run();

            while( enum1.hasNext() ) {

                Map props = enum1.next().getDocument().getProperties();

                for( Object key:props.keySet() ) {

                    props.get(key);

                }

            }

Again this returns only 30 records.  The large lag is when I iterate over the properties.  Without the iteration the query is extremely fast however this is not useful without the values.

This is a showstopper in our application as a database with 21K records is not all that large.  The performance was great when I started but as the data grew it's dismal now.

2 things to note that I'm not positive about, one the emitter emits the document, but I have to call queryRow.getDocument().getProperties() because calling queryRow.getDocumentProperties() always returns null.  I suspect the slow times may have something to do with this.

Thanks again for any help, I've spent some time porting our application and this one will block us going forward with cb mobile.

-james

[James Norman]

Just to note, I realized I need to call query.setPrefetch(true) in order to call queryRow.getDocumentProperties() however this resulted in the exact same query times.

-james

[James Norman]

Forgot to include versions, I'm on 1.0.2 of CB mobile for android and 1.0.2 of sync gateway.

[James Norman]

OK, so this is resolved and the queries are very fast now.  The issue is that I was calling queryRow.getDocument().getProperties() on each row and this would do a query for each individual document.  I changed it to just (Map)queryRow.getValue() and its fast.  This is clearly the value that was used in the emitter.

This normally would have been very clear, but all the example apps call queryRow.getDocument() rather than the queryRow.getValue() even though they emit the document.  Glad its fast now and hopefully this thread can same some people some time.

Thanks -james

[Jens Alfke]

On Sep 13, 2014, at 5:10 PM, James Norman <james....@gmail.com> wrote:

2 things to note that I'm not positive about, one the emitter emits the document

You shouldn't do this, in general. The map function should emit only the values you'll need when you run the query, or null if you don't need them. Emitting the whole document makes the view index a lot larger, and adds a lot of JSON parsing overhead as each row is fetched. 

I have to call queryRow.getDocument().getProperties()

If you need performance, avoid dereferencing the document. It results in another database fetch per row. Instead use the properties you need from the emitted value.

—Jens