public void CheckMemoryUsage(IConnectionManager connection, int numberOfRows, int batchSize, double deviation)
{
//Arrange
BigDataCsvSource.CreateCSVFileIfNeeded(numberOfRows);
ReCreateDestinationTable(connection, "CsvDestinationWithTransformation");
var sourceExpando = new CsvSource(BigDataCsvSource.GetCompleteFilePath(numberOfRows));
var trans = new RowTransformation <ExpandoObject, CSVData>(
row =>
{
dynamic r = row as ExpandoObject;
return(new CSVData()
{
Col1 = r.Col1,
Col2 = r.Col2,
Col3 = r.Col3,
Col4 = r.Col4
});
});
var destGeneric = new DbDestination <CSVData>(connection, "CsvDestinationWithTransformation", batchSize);
sourceExpando.LinkTo(trans);
trans.LinkTo(destGeneric);
//Act
long memAfter = 0;
long memBefore = 0;
bool startCheck = true;
int count = 1;
destGeneric.AfterBatchWrite = data =>
{
if (count++ % 50 == 0)
{
using Process proc = Process.GetCurrentProcess();
memAfter = proc.WorkingSet64;
if (startCheck)
{
memBefore = memAfter;
startCheck = false;
}
Assert.True(memAfter < (memBefore + (memBefore * deviation)));
}
};
var timeElapsedETLBox = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with {numberOfRows} rows of data using ETLBox",
() =>
{
sourceExpando.Execute();
destGeneric.Wait();
}
);
output.WriteLine("Elapsed " + timeElapsedETLBox.TotalSeconds + " seconds for ETLBox (Expando to object transformation).");
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationWithTransformation"));
//10.000.000 rows, batch size 10.000: ~8 min
//10.000.000 rows, batch size 1.000: ~10 min 10 sec
}
//MemberData(nameof(Connections), 200000, 1000)]
public void UsingNonGeneric(IConnectionManager connection, int numberOfRows, int batchSize)
{
//Arrange
TableDefinition destinationTable = CreateDestinationTable(connection, "CSVDestination");
CreateCSVFile("res/Csv/TestData.csv", numberOfRows, destinationTable);
var source = new CSVSource("res/Csv/TestData.csv");
var dest = new DBDestination(batchSize)
{
DestinationTableDefinition = destinationTable,
ConnectionManager = connection
};
source.LinkTo(dest);
//Act
BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) wswith {numberOfRows} rows of data",
() =>
{
source.Execute();
dest.Wait();
}
);
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CSVDestination"));
}
internal static void CreateCSVFileIfNeeded(int numberOfRows)
{
if (!File.Exists(GetCompleteFilePath(numberOfRows)))
{
BigDataHelper bigData = new BigDataHelper()
{
FileName = GetCompleteFilePath(numberOfRows),
NumberOfRows = numberOfRows,
TableDefinition = new TableDefinition("CSV", DestTableCols)
};
bigData.CreateBigDataCSV();
}
}
private void CreateCSVFile(string fileName, int numberOfRows, TableDefinition destinationTable)
{
BigDataHelper bigData = new BigDataHelper()
{
FileName = fileName,
NumberOfRows = numberOfRows,
TableDefinition = destinationTable
};
BigDataHelper.LogExecutionTime($"Create .csv file {fileName} with {numberOfRows} Rows",
() => bigData.CreateBigDataCSV()
);
}
public void CompareWithHiddenReflection(int objectsToCreate, double deviation)
{
//Arrange
//Act
var timeWithReflection = BigDataHelper.LogExecutionTime("Creation with Reflection",
() =>
{
for (int i = 0; i < objectsToCreate; i++)
{
MergeableTestRow row = new MergeableTestRow()
{
ColKey1 = i,
ColKey2 = "Test",
ColValue1 = "X1" + i,
ColValue2 = "T1" + i
};
string id = row.UniqueId;
bool isequal = row.Equals(row);
LogTask.Trace("Id:" + id + " Equals:" + isequal.ToString());
}
;
});
output.WriteLine("Elapsed " + timeWithReflection.TotalSeconds + " seconds for creation with reflection.");
var timeWithoutReflection = BigDataHelper.LogExecutionTime("Creation without Reflection",
() =>
{
for (int i = 0; i < objectsToCreate; i++)
{
MergeableTestHidingRefĺection row = new MergeableTestHidingRefĺection()
{
ColKey1 = i,
ColKey2 = "Test",
ColValue1 = "X2" + i,
ColValue2 = "T2" + i
};
string id = row.UniqueId;
bool isequal = row.Equals(row);
LogTask.Trace("Id:" + id + " Equals:" + isequal.ToString());
}
});
output.WriteLine("Elapsed " + timeWithoutReflection.TotalSeconds + " seconds for creation without reflection.");
//Assert
Assert.True(timeWithoutReflection < timeWithReflection);
Assert.True(timeWithoutReflection.TotalMilliseconds * (deviation + 1) > timeWithReflection.TotalMilliseconds);
}
//
// GET: /Database/BigDataHelper/
public ActionResult Index()
{
BigDataHelper bigDataHelper = new BigDataHelper(DBConn.PublisherDBConn);
List <TestTable> list = new List <TestTable>();
list.Add(new TestTable()
{
Name = "Test"
});
DataTable table = ListHelper.ToDataTable <TestTable>(list, new List <string>()
{
"ID"
});
table.TableName = "Test";
bool result = bigDataHelper.Insert(table);
return(View());
}
private TimeSpan GetBulkInsertTime(IConnectionManager connection, int numberOfRows)
{
TimeSpan result = TimeSpan.FromMilliseconds(0);
if (connection.GetType() == typeof(SqlConnectionManager))
{
result = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with rows of data using BulkInsert",
() =>
{
SqlTask.ExecuteNonQuery(connection, "Insert with BulkInsert",
$@"BULK INSERT [dbo].[CsvDestinationBulkInsert]
FROM '{BigDataCsvSource.GetCompleteFilePath(numberOfRows)}'
WITH ( FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
");
});
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationBulkInsert"));
output.WriteLine("Elapsed " + result.TotalSeconds + " seconds for bulk insert.");
}
return(result);
}
private TimeSpan GetETLBoxTime <T>(int numberOfRows, CsvSource <T> source, DbDestination <T> dest)
{
source.LinkTo(dest);
var timeElapsedETLBox = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with {numberOfRows} rows of data using ETLBox",
() =>
{
source.Execute();
dest.Wait();
}
);
if (typeof(T) == typeof(string[]))
{
output.WriteLine("Elapsed " + timeElapsedETLBox.TotalSeconds + " seconds for ETLBox (Non generic).");
}
else
{
output.WriteLine("Elapsed " + timeElapsedETLBox.TotalSeconds + " seconds for ETLBox (Generic).");
}
return(timeElapsedETLBox);
}
public void TestLeaveOpenOnConnMan(bool leaveOpen)
{
//Arrange
SqlConnectionManager con = new SqlConnectionManager(ConnectionStringParameter)
{
LeaveOpen = leaveOpen
};
//Act
BigDataHelper.LogExecutionTime("Connection is left open: " + leaveOpen,
() =>
{
for (int i = 0; i < 100000; i++)
{
new SqlTask("Dummy", "WAITFOR DELAY '0:00:00.010'")
{
ConnectionManager = con,
DisableLogging = true
}
}
.ExecuteNonQuery();
});
public void NoUpdateWithGuid(int rowsInDest, int rowsInSource)
{
//Arrange
CreateDestinationTable("MergeDestination");
List <MyMergeRow> knownGuids = CreateTestData(rowsInSource);
TransferTestDataIntoDestination(knownGuids);
MemorySource <MyMergeRow> source = AddNewTestData(rowsInDest, knownGuids);
DbMerge <MyMergeRow> mergeDest = new DbMerge <MyMergeRow>(SqlConnection, "MergeDestination");
source.LinkTo(mergeDest);
//Act
var executionTime = BigDataHelper.LogExecutionTime("Execute merge", () =>
{
source.Execute();
mergeDest.Wait();
});
//Assert
Assert.Equal(rowsInDest + rowsInSource, RowCountTask.Count(SqlConnection, "MergeDestination") ?? 0);
Assert.True(executionTime <= TimeSpan.FromMilliseconds((rowsInDest + rowsInSource) * 2));
}
public void TestLeaveOpenOnConnMan(int runs, double deviation)
{
//Arrange
SqlConnectionManager conNormal = new SqlConnectionManager(ConnectionStringParameter);
SqlConnectionManager conLeaveOpen = new SqlConnectionManager(ConnectionStringParameter)
{
LeaveOpen = true
};
//Act
var timeNormal = BigDataHelper.LogExecutionTime("Connection is cloned",
() =>
{
for (int i = 0; i < runs; i++)
{
new SqlTask("Dummy", "WAITFOR DELAY '0:00:00.010'")
{
ConnectionManager = conNormal,
DisableLogging = true
}
}
.ExecuteNonQuery();
});
