public void TestBulkLoadPerformance()
{
using (NuoDbConnection cnn = new NuoDbConnection(connectionString))
{
cnn.Open();
Utils.DropTable(cnn, "temp");
DbCommand createCommand = new NuoDbCommand("create table temp (col1 integer, col2 integer)", cnn);
int result = createCommand.ExecuteNonQuery();
DbCommand cmm = cnn.CreateCommand();
cmm.CommandText = "insert into temp(col1, col2) values(?, ?)";
cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col1" });
cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col2" });
cmm.Prepare();
const int ROW_NUMBER = 40000;
DateTime start = DateTime.Now;
for (var i = 1; i <= ROW_NUMBER; i++)
{
cmm.Parameters["col1"].Value = i;
cmm.Parameters["col2"].Value = 2 * i;
cmm.ExecuteNonQuery();
}
DateTime end = DateTime.Now;
double insertTime = (end - start).TotalMilliseconds;
Utils.DropTable(cnn, "temp2");
createCommand = new NuoDbCommand("create table temp2 (col1 integer, col2 integer)", cnn);
createCommand.ExecuteNonQuery();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.DestinationTableName = "TEMP2";
DbCommand command = new NuoDbCommand("select * from temp", cnn);
DbDataReader reader = command.ExecuteReader();
loader.BatchProcessed += new BatchProcessedEventHandler(loader_BatchProcessed);
start = DateTime.Now;
loader.WriteToServer(reader);
end = DateTime.Now;
double loadTime = (end - start).TotalMilliseconds;
reader.Close();
Console.WriteLine("{0} insert = {1}\n{0} bulk load = {2}\n", ROW_NUMBER, insertTime, loadTime);
Assert.IsTrue(loadTime < insertTime, "BulkLoad takes more time than manual insertion");
}
}
public void TestBulkLoad_DataTableWithStateNoMapping()
{
CreateTargetForBulkLoad();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.BatchSize = 2;
loader.DestinationTableName = "TEMP";
DataTable metadata = new DataTable("dummy");
metadata.Columns.Add("xyz", typeof(string));
const int ROW_TO_ADD = 10;
metadata.BeginLoadData();
for (int i = 0; i < ROW_TO_ADD; i++)
{
DataRow row = metadata.NewRow();
row[0] = Convert.ToString(i);
metadata.Rows.Add(row);
}
metadata.EndLoadData();
metadata.AcceptChanges();
metadata.Rows[ROW_TO_ADD / 2].BeginEdit();
metadata.Rows[ROW_TO_ADD / 2][0] = "999";
metadata.Rows[ROW_TO_ADD / 2].EndEdit();
loader.WriteToServer(metadata, DataRowState.Modified);
VerifyBulkLoad(1, "999");
}
public void TestBulkLoad_DataTableWithMappingOrdinal2Ordinal()
{
CreateTargetForBulkLoad();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.BatchSize = 2;
loader.DestinationTableName = "TEMP";
loader.ColumnMappings.Add(1, 0);
DataTable metadata = new DataTable("dummy");
metadata.Columns.Add("xyz1", typeof(int));
metadata.Columns.Add("xyz2", typeof(string));
const int ROW_TO_ADD = 10;
for (int i = 0; i < ROW_TO_ADD; i++)
{
DataRow row = metadata.NewRow();
row[0] = -1;
row[1] = Convert.ToString(i);
metadata.Rows.Add(row);
}
loader.WriteToServer(metadata);
VerifyBulkLoad(ROW_TO_ADD, "0");
}
public void TestBulkLoad_DataRowsWithMappingOrdinal2Ordinal()
{
CreateTargetForBulkLoad();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.BatchSize = 2;
loader.DestinationTableName = "TEMP";
loader.ColumnMappings.Add(1, 0);
DataTable metadata = new DataTable("dummy");
metadata.Columns.Add("xyz1", typeof(int));
metadata.Columns.Add("xyz2", typeof(string));
DataRow[] rows = new DataRow[10];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = metadata.NewRow();
rows[i][0] = -1;
rows[i][1] = Convert.ToString(i);
}
loader.WriteToServer(rows);
VerifyBulkLoad(rows.Length, "0");
}
public void TestBulkLoad_DataReaderWithMappingOrdinal2Ordinal()
{
CreateTargetForBulkLoad();
NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
loader.BatchSize = 2;
loader.DestinationTableName = "TEMP";
loader.ColumnMappings.Add(1, 0);
using (NuoDbConnection connection = new NuoDbConnection(connectionString))
{
DbCommand command = new NuoDbCommand("select number, position as xyz2 from hockey order by number", connection);
connection.Open();
DbDataReader reader = command.ExecuteReader();
loader.WriteToServer(reader);
reader.Close();
command = new NuoDbCommand("select count(*) from hockey", connection);
object val = command.ExecuteScalar();
VerifyBulkLoad((int)val, "Fan");
}
}
protected void closeResources()
{
// only close local resources (do NOT close parent)
// any batch is local to this SqlSession, so close it now
if (mode == Mode.BATCH && batch != null)
{
long batchStart = Environment.TickCount;
NuoDbBulkLoader loader = new NuoDbBulkLoader((NuoDbConnection)Connection());
//{
loader.DestinationTableName = batch[0].Table.TableName;
//loader.DestinationTableName = BatchTable.TableName;
int index = 0;
foreach (DataColumn c in batch[0].Table.Columns)
//foreach (DataColumn c in BatchTable.Columns)
{
loader.ColumnMappings.Add(index++, c.ColumnName);
}
loader.WriteToServer(batch.ToArray());
//loader.WriteToServer(BatchTable);
//}
long duration = Environment.TickCount - batchStart;
double rate = (batch.Count > 0 && duration > 0 ? 1000.0 * batch.Count / duration : 0);
log.info("Batch commit complete duration={0:N0} ms; rate={1:F2} ips", duration, rate);
}
// commit any transaction scoped to this session
if (transaction != null /* && commitMode == Mode.TRANSACTIONAL */) {
transaction.Commit();
}
if (connection != null) {
connection.Close();
}
}
protected void insertDataWithSQL(long groupId, int dataCount)
{
List<DataRow> batch = new List<DataRow>(dataCount);
DataTable table = new System.Data.DataTable("NuoTest.DATA");
table.Columns.Add("groupId", typeof(long));
table.Columns.Add("dataGuid", typeof(String));
table.Columns.Add("instanceUID", typeof(String));
table.Columns.Add("createdDateTime", typeof(DateTime));
table.Columns.Add("acquiredDateTime", typeof(DateTime));
table.Columns.Add("version", typeof(Int16));
table.Columns.Add("active", typeof(bool));
table.Columns.Add("sizeOnDiskMB", typeof(float));
table.Columns.Add("regionWeek", typeof(String));
for (int dx = 0; dx < dataCount; dx++)
{
Data data = generateData(groupId, dx);
DataRow row = table.NewRow();
batch.Add(row);
row[0] = data.GroupId;
row[1] = data.DataGuid;
row[2] = data.InstanceUID;
row[3] = data.CreatedDateTime;
row[4] = data.AcquiredDateTime;
row[5] = data.Version;
row[6] = data.Active;
row[7] = data.SizeOnDiskMB;
row[8] = data.RegionWeek;
}
appLog.info("inserting {0} data rows in BATCH SQL mode", dataCount);
NuoDbBulkLoader loader = new NuoDbBulkLoader((NuoDbConnection)SqlSession.getCurrent().Connection());
loader.DestinationTableName = table.TableName;
int index = 0;
foreach (DataColumn c in table.Columns)
{
loader.ColumnMappings.Add(index++, c.ColumnName);
}
loader.WriteToServer(batch.ToArray());
table.Clear();
batch.Clear();
using (DbCommand cmd = SqlSession.getCurrent().getStatement("importer_UpdateGroup"))
{
cmd.Parameters[0].Value = groupId;
cmd.ExecuteNonQuery();
}
}