GORM Simple Query but VERY slow

Question

I am using grails 2.3.8 with mysql database. I have this domain class with 200,000 records. It takes 18 seconds to retrieve.

class Alert {
    Long id
    int recorded_at
    int responded_at
    String source_name
    String source_type
    String check_name
    String label
    String priority 
    String status
    String message
    String remarks
    Date dateCreated 
    Date lastUpdated 

    static mapping = {
        table "alerts"
        version false
        dateCreated column: "created_at" 
        lastUpdated column: "updated_at"    
        message type: "text"
        remarks type: "text"
        recorded_at index: 'Recorded_Atx'
        //sort recorded_at: "desc"
    }

    static constraints = {
        responded_at(nullable:true)
        message(nullable:true)
        remarks(nullable:true)
    }
}

 def alerts = Alert.findAll()

why?

what is your comparsion, that let you estimate, that this is slow. and do you really need to materialize all those rows? — cfrick, May 19 '15 at 06:47
The GORM is slow because it's creating 200,000 objects. Just like any ORM would be slow if it needed to create 200,000 objects. — christopher, May 19 '15 at 07:29
what is "fast"? what is your baseline e.g. how long does it take to read simple maps via jdbc? and you have not answered: do you really need all .2 million objects in memory? do you really nead to run `findAll()` again and again? what do you do with those data, that can't be done by already shrinking the resultset in the database? — cfrick, May 19 '15 at 08:16

score 0 · Answer 1 · answered May 24 '15 at 10:21

0

This is because of MySQL wait till all results returned before giving it to the user. Databases like Oracle and Postgres support holders that allow returning results in batches. We can then return the results in chunks to user.

answered May 24 '15 at 10:21

user903772

1,554
5
32
55

GORM Simple Query but VERY slow

1 Answers1