MATLAB: Datainsert is very slow

MATLAB

Hi,

I have a table of 750000 rows by 7 columns. I use datainsert to insert into database. But it is very slow. If I sert 1 row, it is very quick; if it is 100000 rows, it take several minutes, but it take "forever" to insert 750000 rows.

How to speed it up?

Thanks.

Best Answer

There are many ways to insert data a database using the command line. You can use datainsert, fastinsert, or insert. For best performance with large volumes of data, it is recommended to use datainsert or fastinsert.

As you are still experiencing performance issues, create a data file with every record in your dataset. Then, you can use this data file as input into the bulk insert functionality of your database to process the large dataset. Additionally, you can insert data with special characters such as double quotes with this file. Bulk insert provides performance gains by using the bulk insert utilities that are native to different database systems. For details about exporting data using bulk insert, see the following link:

https://www.mathworks.com/help/database/ug/exporting-data-using-bulk-insert.html

Related Solutions

MATLAB: Insert a blank space using datainsert

I understand that you are trying to insert a black space into an SQL table using the "datainsert" function.

The "datainsert" function automatically casts the string '' which is of type MATLAB 'char' to NULL prior to the insertion. One workaround for this issue is to use the string representation in Java as shown in the following example:

Suppose I have a MYSQL table called "names" with three fields 'id', 'name' and 'subject'. I want to insert a record with the 'name' column's value to be '' (the empty string), I would do the following:

>> colnames = {'id','name' , 'subject'};
>> data = {13, java.lang.String(''),'asd'};
>> datainsert(conn,tablenames,colnames,data)

Hope I have answered your question.

MATLAB: Is the database connection slower with MySQL than with Microsoft Access

MySQL tables are of type InnoDB by default. The table format InnoDB grows continually (on inserting data) and doesn't shrink when tables or data are deleted. It is recommended to use MyISAM as the table format, as this is both faster and more efficient.

The official documentation of MySQL says that InnoDB tables require a lot more disk space than MyISAM tables. Also, rollbacks take very long on InnoDB tables.

Please refer to the MySQL documentation at the following page for more information:

http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html

Best Answer

Related Solutions

MATLAB: Insert a blank space using datainsert

MATLAB: Is the database connection slower with MySQL than with Microsoft Access

Related Question