I have a problem. In code below there is a foreach loop, where is created query string for each object truck inside of trucks collection. The loop iterates 100 times (100 objects in the collection, for the example). And method with the loop is called 2000 times.
I resigned from using of ORM to get better performance. But, unfortunatelly, I am still disapointed a bit with code execution speed.
Please note, that for each iterated object there is created string query, that looks, for the example, like this:
UPDATE dbo.Trucks
SET OfficialNumber = '5124095'
, Status = 'Undefined'
, PerformancesUpdate = @PerformancesUpdate
, PictureLink = 'http://www.somewebsite.com/photos/middle////.jpg'
WHERE TruckId = 405664;
and next, after parsing command.Parameters, if they exists (in this case @PerformancesUpdate), the query is sent via library method with int rows = command.ExecuteNonQuery(); //execute SQL. And so on, 100 times. Please note, that query string depends on object properties, so every case will be different a bit.
Here are my questions. I am wondering, is it possible to create one query for all 100 objects in the loop and sending it at once? If yes, should it increase performance? If yes, How the sample query should look like?
And here is the code that creates loop, where are created and executed SQL queries:
using (SqlConnection connection = GetConnection(_connectionString))
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();
foreach (SomeObject truck in trucks)
{
Console.WriteLine("Updating " + counter++ + " of " + trucks.Count);
//clean up string for basic data
truck.Status = CleanUpStringCases(truck.Status);
truck.Destination = CleanUpStringCases(truck.Destination);
//clean up string for full AIS data
if (dataType == "full")
{
truck.TruckType = CleanUpStringCases(truck.TruckType);
}
//PARSE
SomeObject existing = new SomeObject();
//find existing truck to be updated
if (truck.OfficialNumber > 0) existing = _context.trucks.Where(v => v.OfficialNumber == truck.OfficialNumber).FirstOrDefault();
StringBuilder querySb = new StringBuilder();
if (existing != null)
{
//update for basic data
querySb.Append("UPDATE dbo." + _trucksTableName + " SET OfficialNumber = '" + truck.OfficialNumber + "'");
if (existing.MNCI == 0) if (truck.MNCI.HasValue) querySb.Append(" , MNCI = '" + truck.MNCI + "'");
if (truck.LatestActivity.HasValue) querySb.Append(" , LatestActivity = @LatestActivity");
if (truck.ETA.HasValue) querySb.Append(" , ETA = @ETA");
if (!string.IsNullOrEmpty(truck.Status)) querySb.Append(" , Status = '" + truck.Status + "'");
if (!string.IsNullOrEmpty(truck.Destination)) querySb.Append(" , Destination = '" + truck.Destination + "'");
if (!string.IsNullOrEmpty(truck.Area)) querySb.Append(" , Area = '" + truck.Area + "'");
if (truck.HeadingTo.HasValue) querySb.Append(" , HeadingTo = @HeadingTo");
if (truck.Lat.HasValue) querySb.Append(" , Lat = @Lat");
if (truck.Lon.HasValue) querySb.Append(" , Lon = @Lon");
if (truck.Speed.HasValue)
{
querySb.Append(" , Speed = @Speed");
if ((existing.SpeedMax < truck.Speed || existing.SpeedMax == null) && truck.Speed != 0) querySb.Append(" , SpeedMax = @Speed"); //update speed max
}
//string for full AIS data
if (dataType == "full")
{
if (truck.PerformancesUpdate.HasValue) querySb.Append(" , PerformancesUpdate = @PerformancesUpdate");
if (!string.IsNullOrEmpty(truck.TruckType)) querySb.Append(" , TruckType = '" + truck.TruckType + "'");
if (!string.IsNullOrEmpty(truck.PictureLink)) querySb.Append(" , PictureLink = '" + truck.PictureLink + "'");
if (truck.LOA.HasValue) querySb.Append(" , LOA = '" + truck.LOA + "'");
if (truck.Height.HasValue) querySb.Append(" , Height = '" + truck.Height + "'");
}
querySb.Append(" WHERE truckId = " + existing.truckId + "; ");
}
try
{
string query = querySb.ToString();
command.CommandText = query;
if (query.Contains("LatestActivity ="))
command.Parameters.AddWithValue("@LatestActivity", truck.LatestActivity);
if (query.Contains("ETA ="))
command.Parameters.AddWithValue("@ETA", truck.ETA);
if (query.Contains("PerformancesUpdate ="))
command.Parameters.AddWithValue("@PerformancesUpdate", truck.PerformancesUpdate);
if (query.Contains("HeadingTo ="))
command.Parameters.AddWithValue("@HeadingTo", truck.HeadingTo);
if (query.Contains("Lat ="))
command.Parameters.AddWithValue("@Lat", truck.Lat);
if (query.Contains("Lon ="))
command.Parameters.AddWithValue("@Lon", truck.Lon);
if (query.Contains("Speed ="))
command.Parameters.AddWithValue("@Speed", truck.Speed);
command.CommandTimeout = 30;
command.CommandType = CommandType.Text;
int rows = command.ExecuteNonQuery(); //execute SQL
command.Parameters.Clear();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
connection.Close();
}
Solution: Martin`s approach is marked as a solution. And answering question about increasing performance, after testing his approach on local DB I can tell, that DB update speed increases directly proportional on quantity of updated rows in single query. For the example, when updating 300 rows, it took me around 30s, together with API requests. When updating 1000 rows, it all takes around 80s. And, as guys mentioned, the only limitation is the number of parameters, so I am just converting doubles and DateTime to SQL format, getting rid of parameters, and voila.
Yes, it is possible to send multiple update commands on one operation.
You would need to add the commands to your string builder and number the SQL parameters.
SqlCommand command = connection.CreateCommand()
var querySb = new Stringbuilder();
for(int i = 0; i < trucks.Count; i++)
{
[...]
if (truck.Speed.HasValue)
{
querySb.Append(" , Speed = @Speed" + i);
command.Parameters.AddWithValue("@Speed" + i, truck.Speed);
}
querySb.AppendLine();
}
command.CommandText = querySb.ToString();
command.ExecuteNonQuery();
You might create smaller batches of rows (not send all rows at once), maximum number of parameters in one command is 2,098.