Filterpane with MongoDB does always a count (which is very slow in mongodb)

[PatrickHuetter]

I'm using filterpane 2.4.2 with mongoDB 3.0.1 plugin. I have only one domain class wich contains a String and many numbers. For example:

class Person { 
String fullName 
Integer age 
Integer miles 
}

I had 6 Mio. persons in my database. Using filterpane to filter these persons worked fine. After importing 20 Mio. persons it got very slow.

I figured out where the problem is: A "count" takes very long in mongoDB. I don't count anywhere in my code so i can't understand why filterpane is doing a count on my person mongodb collection. I found this in my mongoDB logs wich shows, that filterpane is always doing a count before the main query:

2014-07-15T12:22:02.998+0200 [conn5] command personDeskMiddleware.$cmd command: count { count: "person", query: { person: /^\Q\E.*\Qmichael jackson\E.*\Q\E$/i } } keyUpdates:0 numYields:227 locks(micros) r:74486687 reslen:48 37360ms

2014-07-15T12:22:16.291+0200 [conn5] query personDeskMiddleware.person query: { person: /^\Q\E.*\Qmichael jackson\E.*\Q\E$/i } planSummary: IXSCAN { person: 1 } cursorid:95038381028 ntoreturn:0 ntoskip:0 nscanned:8082095 nscannedObjects:101 keyUpdates:0 numYields:168 locks(micros) r:26431982 nreturned:101 reslen:27780 13290ms

As you can see the count takes more than 37 seconds. This is very bad for me because one filter query takes very long then and i don't need the count and i don't count in my code so this must be in the filterpane plugin.

I don't use the filterpane with gsp views, i use the filterpaneService for a REST API wich returns the filtered persons.

Why mongodb count is slow can be find in many sources on the internet. For example: http://stackoverflow.com/questions/9778420/mongo-count-really-slow-when-there-are-millions-of-records

So why is filterpane doing a count before each query and how can i disable this?

[This issue ist similar to https://jira.grails.org/browse/GPFILTERPANE-127 ]

[ctoestreich]

Looking at the code, if you simply call filterPaneService.filter(params, Book) or something similar using your classes, it should not do a count. Can you send me the line of code that calls the filterPaneService in your code?

[PatrickHuetter]

I'm using this code in my controller action:

        def personInstanceList = filterPaneService.filter(filterParams, Person)
        def personInstanceListCount = personInstanceList.size()

That's all.

[ctoestreich]

let me test out with non-mongo and see if count is invoked, by using the .filter method it should not be.

[ctoestreich]

I think MongoDB driver for grails or the plugin is doing the count.

When I run this test

def "test emdash filtering"() {
        given:
        def params = ['filter': [op: [title: 'ILike'], title: 'how']]
        Book.findOrSaveWhere(title: 'Hello�how are you')

        when:
        def results = filterPaneService.filter(params, Book)

        then:
        Book.list().size() == 1
        1 == results?.size()
    }

I see this output from hibernate with no count query.

--Output from test emdash filtering--
Hibernate: select this_.id as id2_0_, this_.version as version2_0_, this_.book_type as book3_2_0_, this_.co_author_id as co4_2_0_, this_.cost as cost2_0_, this_.currency as currency2_0_, this_.in_stock as in7_2_0_, this_.last_updated as last8_2_0_, this_.price as price2_0_, this_.read_priority as read10_2_0_, this_.release_date as release11_2_0_, this_.title as title2_0_, this_.display_until as display14_2_0_, this_.month as month2_0_, this_.year as year2_0_, this_.price * 1.055 as formula0_0_, this_.class as class2_0_ from book this_ where (this_.title=?) limit ?
Hibernate: insert into book (id, version, book_type, co_author_id, cost, currency, in_stock, last_updated, price, read_priority, release_date, title, class) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'org.grails.plugin.filterpane.Book')
Hibernate: select this_.id as id2_0_, this_.version as version2_0_, this_.book_type as book3_2_0_, this_.co_author_id as co4_2_0_, this_.cost as cost2_0_, this_.currency as currency2_0_, this_.in_stock as in7_2_0_, this_.last_updated as last8_2_0_, this_.price as price2_0_, this_.read_priority as read10_2_0_, this_.release_date as release11_2_0_, this_.title as title2_0_, this_.display_until as display14_2_0_, this_.month as month2_0_, this_.year as year2_0_, this_.price * 1.055 as formula0_0_, this_.class as class2_0_ from book this_ where (lower(this_.title) like ?) order by this_.title asc
Hibernate: select this_.id as id2_0_, this_.version as version2_0_, this_.book_type as book3_2_0_, this_.co_author_id as co4_2_0_, this_.cost as cost2_0_, this_.currency as currency2_0_, this_.in_stock as in7_2_0_, this_.last_updated as last8_2_0_, this_.price as price2_0_, this_.read_priority as read10_2_0_, this_.release_date as release11_2_0_, this_.title as title2_0_, this_.display_until as display14_2_0_, this_.month as month2_0_, this_.year as year2_0_, this_.price * 1.055 as formula0_0_, this_.class as class2_0_ from book this_ order by lower(this_.title) asc
|Completed 1 integration test, 0 failed in 0m 0s

[ctoestreich]

There is an explicit count method on the service that can be called. But if you are using the service method filter directly, you would not be hitting that.

[PatrickHuetter]

@ctoestreich I've seen that too and already debugged the whole filterpaneService. There is no count wich gets executed. Seems to be a mongodb grails plugin bug... I created an issue in bugtracker: https://jira.grails.org/browse/GPMONGODB-395

If you've some information to add, it would be fine if you comment the issue. Thank you and best regards Patrick