I recently built an app to test the speed difference between writing 100000 lines to a text file and 100000 inserts into a database
Here is the heart of the code
private void RunTestBtn_Click(object sender, RoutedEventArgs e)
{
Stopwatch FFtimer = new Stopwatch();
FFtimer.Start();
RunFFTest();
FFtimer.Stop();
TimeSpan FFts = FFtimer.Elapsed;
string FFelapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
FFts.Hours, FFts.Minutes, FFts.Seconds,
FFts.Milliseconds / 10);
FFLabel.Content = "Flat File: " + FFelapsedTime;
Stopwatch Datatimer = new Stopwatch();
Datatimer.Start();
DataFFTest();
Datatimer.Stop();
TimeSpan Datats = Datatimer.Elapsed;
string DataelapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
Datats.Hours, Datats.Minutes, Datats.Seconds,
Datats.Milliseconds / 10);
DBLabel.Content = "Database: " + DataelapsedTime;
}
void RunFFTest()
{
using(StreamWriter writer = new StreamWriter(@"F:\test\FFtest.txt"))
{
for(int i = 0; i< 100000; i++)
{
writer.WriteLine("test");
}
}
}
void DataFFTest()
{
using (SqlConnection conn = new SqlConnection("Data Source=EMMY;Initial Catalog=MyDB;User Id=SqlServiceUser;Password=MyPassword;"))
{
conn.Open();
for (int i = 0; i < 100000; i++)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO TestTable VALUES ('Test')";
cmd.ExecuteNonQuery();
}
}
}
}
The end result is that the Flat file writing took 1 millisecond and the sql inserts took nine minutes and forty one seconds. I know the database will take longer, but is there any way to speed this up?
