Re: Very fast data loading
Thank you very much for your response!
The table is very simple:
CREATE TABLE VariableData (
ExperimentID INTEGER UNSIGNED NOT NULL,
VariableID INTEGER UNSIGNED NOT NULL,
ordinal INTEGER UNSIGNED NOT NULL,
value DOUBLE NULL,
PRIMARY KEY(ExperimentID, VariableID, ordinal)
);
I'm extracting data from an electronic device that outputs lists of values.
The are approximately 66 lists.
Each list can hold up to 1200 individual double-precision values.
Here is the loop that builds the "insert" statement for each value:
for value in values:
statement = "insert into VariableData values ( {}, {}, {}, {} );".format(
idExperimentStr, idVariableStr, str(ordinal), str(value) )
ordinal += 1
cmd = MySqlClient.MySqlCommand(statement, connection)
cmd.ExecuteNonQuery()
When the last two lines of the loop are commented out, all 66 iterations of the loop run in SIX seconds!
When I uncomment those lines and then execute the command, each iteration of the loop takes an average of 43 seconds, and all iterations take approximately 50 minutes.
What do you suggest that I can do to speed things up given my constraints of having to use:
* MySQL Connector.NET 6.9 (MySql.Data.dll)
* IronPython 2.7
And again, thank you very much for your response!