I have a c# application that is taking data from one database, making the necessary transformations, and inserting the data into a table in another database. I am doing this by inserting my source data into a queue and then processing the queue to insert the data into the destination table. I have two separate threads to read the source data and write the destination data. The reading thread is operating MUCH faster than the writing thread so my queue fills up pretty quickly.
As you can see in the reading thread I am using SqlCommand.ExecuteReader() to read in the data. I am then looping through the queue and doing separate INSERT statements for each row. What I am envisioning is, instead of inserting row by row, doing some sort of (possibly a Linq statement). Does anyone have any ideas how to do my inserts more quickly?
Queue Definition:
private static readonly BlockingCollection<HistorianData> ValueQueue = new BlockingCollection<HistorianData>(new ConcurrentQueue<HistorianData>(), 1000000);
Reading:
public static void EnqueueHistorianData(SqlConnection connection, int idToAdd, DateTime minDatetime, DateTime maxDateTime, string cluster, string dbName, string dataTable, string idTable, string mainIdColumn, string foreignIdColumn, string dateColumn, string nameColumn, string valueColumn)
{
StringBuilder select = new StringBuilder();
HistorianData values;
select.Append(String.Format("SELECT {0}.{1},", dataTable, dateColumn));
select.Append(String.Format(" '{0}.' + {1}.{2},", cluster, idTable, nameColumn));
select.Append(String.Format(" {0}.{1}", dataTable, valueColumn));
select.Append(String.Format(" FROM {0}.{1}", dbName, dataTable));
select.Append(String.Format(" JOIN {0}.{1}", dbName, idTable));
select.Append(String.Format(" ON {0}.{1} = {2}.{3}", dataTable, foreignIdColumn, idTable, mainIdColumn));
select.Append(String.Format(" INNER JOIN Runtime.dbo.Tag"));
select.Append(String.Format(" ON Runtime.dbo.Tag.TagName = '{0}.' + {1}.{2}", cluster, idTable, nameColumn));
select.Append(String.Format(" WHERE {0}.{1} >= '{2}'", dataTable, dateColumn, minDatetime.ToString()));
select.Append(String.Format(" AND {0}.{1} = {2}", dataTable, foreignIdColumn, idToAdd.ToString()));
select.Append(String.Format(" AND {0}.{1} >= '{2}'", dataTable, dateColumn, minDatetime.ToString()));
select.Append(String.Format(" AND {0}.{1} < '{2}'", dataTable, dateColumn, maxDateTime.ToString()));
using (var command = new SqlCommand(select.ToString(), connection))
{
command.CommandTimeout = 1000;
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
values = new HistorianData();
values.SampleDate = reader.GetDateTime(0);
values.TagName = reader.GetString(1);
values.TagValue = reader.GetDouble(2);
ValueQueue.Add(values);
}
values = null;
reader.Close();
}
}
}
}
Writing:
public static void WriteQueueValuesToHistorian(string connectionString)
{
HistorianData values;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand insertCommand = connection.CreateCommand())
{
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = "INSERT INTO Runtime.dbo.History (DateTime, TagName, Value, QualityDetail) VALUES (@P1, @P2, @P3, 192)";
insertCommand.CommandTimeout = 1000;
var param1 = new SqlParameter("@P1", SqlDbType.DateTime);
insertCommand.Parameters.Add(param1);
var param2 = new SqlParameter("@P2", SqlDbType.NVarChar, 512);
insertCommand.Parameters.Add(param2);
var param3 = new SqlParameter("@P3", SqlDbType.Float);
insertCommand.Parameters.Add(param3);
insertCommand.Prepare();
while (!ValueQueue.IsCompleted && ValueQueue.TryTake(out values, System.Threading.Timeout.Infinite))
{
int retries = 0;
while (retries < 3)
{
insertCommand.Parameters["@P1"].Value = values.SampleDate.ToLocalTime();
insertCommand.Parameters["@P2"].Value = values.TagName;
insertCommand.Parameters["@P3"].Value = values.TagValue;
try
{
insertCommand.ExecuteNonQuery();
retries = 4;
}
catch (SqlException)
{
retries += 1;
sw.WriteLine("SQLException - Values: " + insertCommand.Parameters["@P1"].Value + ", " + insertCommand.Parameters["@P2"].Value + ", " + insertCommand.Parameters["@P3"].Value);
}
}
}
}
}
}
