MemSQL Version: 6.7.15
This is a fairly common and simple use case. I am creating a temp table and inserting records to it. It’s taking ~2 mins to insert 1000 records. So to insert 150K records it would take around 5 hours. This is too slow. Any idea why it’s taking so much time?
CREATE TEMPORARY TABLE temp_KFcCwZvs ( PartitionId INT(20), Offset BIGINT(20), RowId VARCHAR(32), PositionType VARCHAR(22), Security VARCHAR(255), BusinessDate DATE )
INSERT INTO temp_KFcCwZvs (PartitionId, Offset, RowId, PositionType, Security, BusinessDate) VALUES ( ?, ?, ?, ?, ?, ? )
Relevant JAVA code:
final int batchSize = 1000;
int thisBatchSize = 0;
long bookmarkTime = System.currentTimeMillis();
try(final PreparedStatement stmt = connection.prepareStatement(insertTempTableSQL)) {
logger.info("Got prepared statement. Time taken in seconds: " + ((System.currentTimeMillis()-bookmarkTime)/1000) );
bookmarkTime = System.currentTimeMillis();
for(T item: items.keySet()) {
setInsertValues(stmt, item);
stmt.addBatch();
thisBatchSize++;
if(thisBatchSize == batchSize) {
stmt.executeBatch();
logger.info(" Inserted " + thisBatchSize + " records. Total time taken for the batch: " +((System.currentTimeMillis()-bookmarkTime)/1000) );
thisBatchSize=0;
bookmarkTime = System.currentTimeMillis();
}
}
stmt.executeBatch();
}catch (Exception ex) {
throw new RuntimeException("Error while inserting temp table.", ex);
}
logs:
2019/08/16 09:01:37.351 [jsilink-launcher] INFO [ClassName] - Got prepared statement. Time taken in seconds: 0
2019/08/16 09:03:41.646 [jsilink-launcher] INFO [ClassName] - Inserted 1000 records. Total time taken for the batch: 124
2019/08/16 09:05:45.671 [jsilink-launcher] INFO [ClassName] - Inserted 1000 records. Total time taken for the batch: 124
2019/08/16 09:07:45.568 [jsilink-launcher] INFO [ClassName] - Inserted 1000 records. Total time taken for the batch: 119
2019/08/16 09:10:03.174 [jsilink-launcher] INFO [ClassName] - Inserted 1000 records. Total time taken for the batch: 137
