Message Boards Message Boards

GROUPS:

DatabaseLink: Slow, and Slow Blobs

Posted 8 years ago
4356 Views
|
1 Reply
|
0 Total Likes
|

Hello,

In the attached notebook, the last two statements are problematic. The first of the two never completes, and the second is very slow. YouÂ’ll need access to a MySQL database to test this. (Easily obtain the installer from the MySQL website.)

Here is a code excerpt:

(* In the next statement, modify MACHINE, USER, and PASSWORD to your MySQL installation.*)

conn = OpenSQLConnection[
  JDBC["MySQL(Connector/J)", "MACHINE:3306"],
  "Name" -> "DATABASE",
  "Username" -> "USER",
  "Password" -> "PASSWORD",
  "Catalog" -> "DATABASE"
  ]

SQLDropTable[conn, "blobs"];
SQLCreateTable[conn, "blobs",
  {
   SQLColumn["a", "DataTypeName" -> "DOUBLE"],
   SQLColumn["b", "DataTypeName" -> "DOUBLE"],
   SQLColumn["mblob", "DataTypeName" -> "MEDIUMBLOB"]
   }
  ];

cols = SQLColumnNames[conn, {"blobs", "%"}][[All, 2]]

blobRecords = Table[{0, 0, SQLBinary@ConstantArray[0, 1920000]}, {144}];

SQLExecute[conn, "truncate table blobs"];

(* The next statement never completes. Usually blows Java heap. *)

SQLInsert[conn, "blobs", cols, blobRecords];

(* The next statement completes (assuming correct starting condition), but is very slow. *)

SQLInsert[conn, "blobs", cols, #] & /@ blobRecords;

Is there a way to make both statements complete quickly? Total data going to the DB is only in the 100s of MB (200-300 MB).

Thank you,

Vince

Attachments:
POSTED BY: Vincent Virgilio

For a performance baseline, I removed the blob column from the table, leaving the first two columns of doubles. Now the batch insert (the first) completed. But it took about 12 seconds. This seems very slow for an insert of 144 rows, each of 2 doubles. The second insert completed in about 13 seconds.

POSTED BY: Vincent Virgilio
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract