Robert,
We've done this recently. If you are simply inserting records into a table and don't need to worry about relationships between objects then you should probably fall back on the database's batch import functions. We tried loads of ways of doing this and this worked out miles better than everything else.
For MySQL we create a temporary CSV file with all the data to be imported in and use the LOAD DATA LOCAL INFILE statement to do the actual import.
In a service it looks a bit like this:
import javax.sql.DataSource
class ImportService {
DataSource dataSource
def import(data) {
def SQL = new Sql(dataSource)
File file = File.createTempFile('tmp-import', '.csv')
file.deleteOnExit()
String filePath = file.getAbsolutePath()
filePath = Matcher.quoteReplacement(filePath)
// Create the data:
data.each {
file << "${data.field1},${data.field2}\n"
}
SQL.execute("""
LOAD DATA LOCAL INFILE ${filePath} IGNORE INTO TABLE <table_name>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
(
@field1, field2
)
SET field1 = CAST(@field1 AS UNSIGNED)
""")
}
}
(the SET field1 bit is if you have a field that is a boolean - check the MySQL docs if you do).
Other DBs will have similar ways of doing this.
Jonathan
On 16 September 2011 16:49, Robert La Ferla
<[hidden email]> wrote:
I have a Grails app that needs to do a one-time insert of millions of records. How can I optimize/tune performance? Would disabling auto-commit help? If so, how do I do it. Please provide code sample for it if you can.
Thanks,
Robert
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
--
Dr Jonathan Stott
Development Team Leader
Axon TeleHealthCare
“Connecting with Care”
2 Venture Road
University of Southampton Science Park
Southampton SO16 7NP
Tel: 02380 111192
www.axonuk.com
This message and the information contained therein is intended only for
the use of the person(s) to whom it is addressed. It may contain
information that is confidential or privileged within the meaning of
applicable law. If you are not the intended recipient, please contact
the sender as soon as possible and delete this message from your system;
please also note that any use or disclosure of the information
contained in this message (including any attachments) is strictly
prohibited and may be unlawful. This correspondence may include
examples or terms based upon current assumptions with any costs shown
excluding VAT and cannot be considered as a quotation, offer or
commitment in any way. Whilst reasonable precaution has been taken to
minimise the risk, the contents or an attachment to this e-mail may have
become corrupted during transmission or contain viruses, we cannot
accept liability in this regard, and you should carry out your own virus
checks.
Axon Limited is a Limited Company registered in England and Wales number
5728502. Registered Office: Imperial House, 18-21 Kings Park Road,
Southampton SO15 2AT.
Locked
