Efficient way to implement excel import in grails

Question

This code should probably go in code review but I won't get quick response there (Only 2 groovy questions there). I have the following code for importing data from excel into my grails application. The problem is that I didn't test for >1000 rows in the excel file so my app froze when my client tried to upload 13k rows. I have checked the stacktrace.log (app is in production) but no exception. The system admin thinks the jvm ran out of memory. We have increased the size of the heap memory. However, I want to ask if there's a better way to implement this. I am using apache poi and creating domain objects as I read each row from excel. After that, I pass the list of objects to the controller that validates and saves them in the database. Should I tell my client to limit number of items imported at a time? Is there a better way to write this code?

def importData(file, user){
    def rows = []
    def keywords = Keyword.list()
    int inventoryCount = Inventory.findAllByUser(user).size()

    def inventory = new Inventory(name:"Inventory ${inventoryCount +1}", user:user)

    Workbook workbook = WorkbookFactory.create(file)
    Sheet sheet = workbook.getSheetAt(0)

    int rowStart = 1;
    int rowEnd = sheet.getLastRowNum() + 1 ;
    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
        Row r = sheet.getRow(rowNum);

        if(r != null && r?.getCell(0, Row.RETURN_BLANK_AS_NULL)!=null ){
            def rowData =[:]
            int lastColumn = 8;
            for (int cn = 0; cn < lastColumn; cn++) {
                Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                if (c == null) {
                    return new ExcelFormatException("Empty cell not allowed",rowNum+1, cn+1)
                } else {

                    def field = properties[cn+1]
                    if(field.type==c.getCellType()){
                        if(c.getCellType()==text){

                            rowData<<[(field.name):c.getStringCellValue().toString()]
                        }else if(c.getCellType()==numeric){
                            if(field.name.equalsIgnoreCase("price") ){
                                rowData<<[(field.name):c.getNumericCellValue().toDouble()]
                            }else{
                                rowData<<[(field.name):c.getNumericCellValue().toInteger()]
                            }
                        }
                    }else{
                        return new ExcelFormatException("Invalid value found", rowNum+1, cn+1)
                    }
                }

            }

            def item = new InventoryItem(rowData)
            String keyword = retrieveKeyword(item.description, keywords)
            String criticality = keyword?"Critical":"Not known"
            int proposedMin = getProposedMin(item.usagePerYear)
            int proposedMax = getProposedMax(proposedMin, item.price, item.usagePerYear, item?.currentMin)
            String inventoryLevel = getInventoryLevel(item.usagePerYear, item.quantity, proposedMin, item.currentMin) 

            item.proposedMin = proposedMin
            item.proposedMax = proposedMax
            item.inventoryLevel = inventoryLevel
            item.keyword = keyword 
            item.criticality = criticality

            inventory.addToItems(item)
        }

    }

    return inventory
}

Functions used in above code:

def retrieveKeyword(desc, keywords){
    def keyword
    for (key in keywords){
        if(desc.toLowerCase().contains(key.name.toLowerCase())){
            keyword = key.name
            break
        }
    }
    return keyword
}
int getProposedMin(int usage){
    (int) ((((usage/12)/30) *7) + 1)
}


int getProposedMax(int pmin, double price, int usage, int cmin){
    int c = price == 0? 1: ((Math.sqrt((24 * (usage/12)*5)/(0.15*price))) + (pmin - 1))
    if(cmin >= c){
        return pmin
    }
    return c
}

String getInventoryLevel(int usage, int qty, int proposedMin, int currentMin){
    if(qty != 0){
        double c = usage/qty
        if(usage==0)
            return "Excess"
        if(c<0.75){
            return "Inactive"
        }else if(proposedMin<currentMin){
            return "Excess"
        }else if(c>=0.75){
            return "Active"
        }
    }else if(usage==0 && qty == 0){
        return "Not used"
    }else if(usage>3 && qty ==0){
        return "Insufficient"
    }else if(proposedMin > currentMin){
        return "Insufficient"
    }

}

Controller action:

def importData(){
    if(request.post){
        def file = request.getFile("excelFile")

        //validate file
        def file_types = ["application/vnd.ms-excel","application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"]

        if(!file_types.contains(file.getContentType())){
            render view:"importData", model:[error:"Invalid File type"]
            return
        }

        def inv = excelService.importData(file.getInputStream(),User.get(principal.id))
        if(inv){
            if(inv instanceof ExcelFormatException){
                def err = (ExcelFormatException) inv
                render view:"importData", model:[error:err.message +". Error occurred at: Row: "+err.row+" Col: "+err.col]
                return
            }else{
                render view:"viewData", model:[inventory:inv]
                return
            }
        }
    }


}

Answer 1

Hibernate and GORM require some tuning when dealing with bulk imports. Two suggestions:

1. Follow the techniques found here: http://naleid.com/blog/2009/10/01/batch-import-performance-with-grails-and-mysql (written with MySQL in mind, but the concepts are pertinent to any RDBMS)

2. Don't use a collection to map the relationship between Inventory and InventoryItem. Remove the items collection from Inventory and instead add an Inventory field to your InventoryItem class. Burt Beckwith covers this in great detail here: http://burtbeckwith.com/blog/?p=1029

Answer 2

Using a plugin would be a better option. I use this plugin - http://grails.org/plugin/excel-import