Utworzenie jednego zapytania dla kilkudziesięciu UPDATE

Witajcie, mam pewien problem. W poniższym kodzie jest przedstawiona pętla foreach w której tworzony jest string zapytania dla każdego obiektu truck w kolekcji obiektu trucks. Pętla iteruje 100 razy. A sama pęta jest wywoływana kilkaset razy.

Zrezygnowałem z ORM na rzecz wydajności. Choć niestety wciąż nie jestem jeszcze zadowolony z wyników.

Proszę zauważyć, że dla każdego obiektu z pętli tworzony jest string query, który, przykładowo, wygląda następująco:

UPDATE dbo.Trucks 
SET OfficialNumber = '5124095' 
, Status = 'Undefined' 
, PerformancesUpdate = @PerformancesUpdate
, PictureLink = 'http://www.somewebsite.com/photos/middle////.jpg'
WHERE TruckId = 405664;

następnie, po parsowaniu command.Parameters, jeśli istnieją (w tym przypadku @PerformancesUpdate), zapytanie jest wysyłane przez int rows = command.ExecuteNonQuery(); //execute SQL. I tak 100x w pętli. Proszę zauważyć, że sam string zapytania jest uzależniony od obecności własności obiektu, więc w każdym przypadku może ono wyglądać inaczej.

I teraz moje pytanie. Zastanawiam się, czy jest możliwość utworzenia jednego zapytania dla wszystkich 100 obiektów i wysłania go jednorazowo? A jeśli tak, to czy poprawi to wydajność? A jeśli tak, to jak takie zapytanie może wyglądać? Przykładowo, jeśli bym miał wysłać przykładowe zapytanie 100 razy, tyle że z innymi zmiennymi i dla innego TruckId.

A poniżej kod omawianej pętli która tworzy zapytania dla obiektów:
```csharp
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();
            }

A skąd masz trucks? Też z bazy? Modyfikujesz te dane jakoś w programie? Jeśli z bazy i nie modyfikujesz, to możesz to zrobić jednym prostym zapytaniem.

Czyli coś w tym stylu

UPDATE t
SET OfficialNumber = tp.OfficialNumber 
, Status = tp.Status 
, PerformancesUpdate =  tp.PerformancesUpdate 
, PictureLink =  tp.PictureLink 
from dbo.Trucks t
join TrucksParametrs tp on tp.truckid=t.truckid
WHERE TruckId IN( xxx);

Jak nie masz tabeli TrucksParametrs to przydałoby się zrobić tymczasówkę i załadować ją insertem a potem podstawić do powyższego updatea

Już prostuję, trucks jest parametrem metody, czyli obiektem C#, niestety nie dostaję go z bazy, a jest odpowiedzią z web API.

jak sugerowali @Marcin.Miga i @biela_ - dodaj tymczasową tabelkę, załaduj ją danymi i zrób update from table https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp

Alternatywne podejście, z dwoma założeniami:

• list atrybutów jest znana (skoro robisz if query.contains.., to zakładam, że jest znana)
• nie aktualizujesz atrybutów na NULL (słaby punkt propozycji)

1. Robisz jednego SQLa

UPDATE 
  TABELKA
SET
  attr1=coalesce(:attr1,attr1),
  attr2=coalesce(:attr2,attr2),
...
WHERE
   KEY = :id
;

2. W kodzie sprawdzasz czy atrybut ma wartość, jeśli tak to dowiązujesz ("bindujesz") tę wartość, w przeciwnym razie dowiązujesz NULLa.
   COALESCE zwraca pierwszą wartość NOT NULL, więc jak dowiążesz wartość to uwzględni ją w UPDATE, jeśli wstawisz NULL, to dowiąże bieżącą wartość atrybutu z bazy.

3. Korzystasz z "batch/bulk api" (wiążesz wartości N razy) i zamiast N operacji command.ExecuteNonQuery(); będziesz miał jedną operację batchową.

abrakadaber napisał(a):

jak sugerowali @Marcin.Miga i @biela_ - dodaj tymczasową tabelkę, załaduj ją danymi i zrób update from table https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp

Zastanawiam się, czy zapełnianie tymczasowej tabeli nie zajmie równie dużo czasu co tabeli docelowej?

"W przypadku SQL SERVER przechowywane są w pamięci operacyjnej do chwili zakończenia działania skryptu". Tabela docelowa ma mieć około 100MB. Zastanawiam się jak dodawanie do niej może obciążyć serwer.

Bądź co bądź, pomysł ciekawy.

yarel napisał(a):

• nie aktualizujesz atrybutów na NULL (słaby punkt propozycji)

Niestety, jeśli wartość z web API jest NULL, to nie nadpisuję wartości z tabeli, ponieważ zakładam że poprzednia jest właściwa, z kilku powodów. Gdyby nie to, pomysł byłby bardzo dobry.