Utworzenie jednego zapytania dla kilkudziesięciu UPDATE

Odpowiedz Nowy wątek
2020-04-09 09:05

Rejestracja: 3 lata temu

Ostatnio: 4 dni temu

Lokalizacja: Polska

0

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:

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();
                }
edytowany 1x, ostatnio: bakunet, 2020-04-09 09:14

Pozostało 580 znaków

2020-04-09 10:21

Rejestracja: 12 lat temu

Ostatnio: 7 godzin temu

2

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.

Pozostało 580 znaków

2020-04-09 11:22

Rejestracja: 3 lata temu

Ostatnio: 16 godzin temu

Lokalizacja: WPR

0

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

Pozostało 580 znaków

2020-04-09 12:46

Rejestracja: 3 lata temu

Ostatnio: 4 dni temu

Lokalizacja: Polska

0

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

Pozostało 580 znaków

2020-04-09 15:46

Rejestracja: 7 lat temu

Ostatnio: 13 godzin temu

2

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


Chcesz pomocy - pokaż kod - abrakadabra źle działa z techniką.

Pozostało 580 znaków

2020-04-09 16:45

Rejestracja: 5 lat temu

Ostatnio: 2 minuty temu

1

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ą.

Pozostało 580 znaków

2020-04-09 20:59

Rejestracja: 3 lata temu

Ostatnio: 4 dni temu

Lokalizacja: Polska

0
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/que[...]635796/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.

moim zdaniem krócej, ale oczywiście sprawdzić trzeba ;) - biela_ 2020-04-09 21:44

Pozostało 580 znaków

2020-04-09 21:03

Rejestracja: 3 lata temu

Ostatnio: 4 dni temu

Lokalizacja: Polska

0
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.

Pozostało 580 znaków

Odpowiedz

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0