Im developing an offline application with Visual Basic on Win7, 64bit. I use MS Visual Studio 2010 Express, MySQL Connector Net 6.3.5(32bit) and MySQL 5.1 (64bit).
I have to insert a lot of records (+20.000.000) given by a XML streamer. To increase speed I already lock tables and disable keys.
During testing I have recognized that MySQl doesn’t seem to use more than 2GB. I have installed 8GB. How can I pass through the 2GB limit? Is the connector the limitation? Does it make sense to switch tothe MySQL ODBC Connector (64bit) to increase the memory usage? As far as I have heard the NET connector is platform indepandant and performance should be (more or less) same.
Well, currently I use MyISAM. I’ve tried also INNO, but not seen a difference. For the current application I query 25.000.000 recordsets in total, which are placed in 5 tables. Each table has 1-5 coloumns only. No blobs. Biggest table is 13.000.000 recordsets.
How long does loading 25M rows on InnoDB take for you? I just tested loading 34M on stock my-medium.cnf using the sakila.film sample database and it too 19mins average on Linux if this gives you an idea.
I don’t know much about .NET, but have you tried loading your data manually if possible?