Zestawienia - timeout

[Michał Dyrała 3]

Subiekt po aktualizacji z wersji 35.1 do 36.0 na większości zestawień dostaję komunikat "Przekroczenie czasu oczekiwania na odpowiedź serwera (timeout)". Nie jest to pierwszy raz kiedy mam taki problem jednak wcześniej udało się go rozwiązać przez `ALTER INDEX ALL ON xxx REBUILD`. Teraz natomiast przebudowa indeksu nie pomaga (były wykonywane z programu serwisowego, po kolei: konserwacja bazy danych i przebudowanie InsTYNKtu

 

Konkrety. Tak jest ustawione zestawienie:

W rezultacie dostaję 6 rekordów (ale tylko jeśli wybiorę do zapytania jeden dzień. Przy większym zakresie jest timeout). Poniżej przechwycone zapytanie z Subiekta:

 

Zapytanie.sql

 

Zapytanie ma 798 linii!!! Naliczyłem 10 poziomów zagłębieni, tzn. select from select from select... a wszystkich podzapytań jest 62!!! Dla jednego dnia zapytanie wykonuje się 4:57s (przed aktualizacją to było ok. 5 sekund dla całego miesiąca), w tym czasie serwer wygląda tak (widać na wykresach kiedy zapytanie się rozpoczęło)

 

 

a sam plan zapytania wygląda tak:

 

Jeśli chodzi o sprzęt to za serwer robi komputer z I7-10700K 3.8GHz i 32GB RAM, baza jest na SSD (Samsung 980 Pro 500GB), natomiast system stoi na drugim SSD (Samsung 870 EVO 500GB). Na serwerze jest zainstalowany Windows Server 2019 Standard a wersja MS SQLa to 2019 Enterprise. Sprzęt był wymieniany niecałe 3 miesiące temu.

 

Rozmiar bazy:

Rozmiar poszczególnych tabel i ilość rekordów: table_size.csv

 

Wszystkich dokumentów sprzedażowych z dnia dla którego jest zapytanie jest 197.

 

Problem nie dotyczy tylko tego konkretnego zapytania - wszystkie zapytania sprzedażowe leżą i kwiczą jeśli się wybierze zakres większy niż jeden dzień. Dla testów zmieniliśmy maksymalny czas wykonywania zapytania n 1h i sprawdzaliśmy ile będzie się wykonywało zapytanie dla całego miesiąca - szło ponad 40 minut...

 

Ja wiem, że to jest zapytanie wygenerowane przez EF ale miejcie litość - wybranie dokumentów wg typu, daty i pogrupowanie wg pracowników to jest proste zapytanie bez podzapytań! Popełniłem w życiu wiele potworków w SQLu ale to co wypluwa Subiekt to jest jakieś monstrum.

Edytowane 12 Lipca 2021 przez Michał Dyrała

[Paweł Szczygieł 36]

Panie Michale, zdecydowanie polecam budowę hurtowni danych. Taki model zapewni Panu dostępność danych analitycznych bez relacji do wielkości bazy danych OLTP.

Większość dużych systemów transakcyjnych ma wyłączone moduły zestawień z bardzo prostego powodu - struktura obiektów bazodanowych dla operacji jakie działają na systemach OLTP jest zupełnie inna niż struktura baz analitycznych.

[Michał Dyrała 3]

Ja to wiem, co więcej właściciel firmy powoli przekonuje się do tego pomysłu (który, swoją drogą, po części jest już zrealizowany - właściciel posiada kilka oddzielnych podmiotów dla których potrzebne były min. zestawienia zbiorcze). Tylko że przed aktualizacją to zestawienie (oraz reszta zestawień sprzedażowych) działała jak najbardziej bez zarzutu (jak pisałem dla całego miesiąca było to kilka sekund). 

 

Co więcej to samo zestawienie napisane w SQLu wykonuje się 141ms (mili sekund, czyli niecałą sekundę!) i zwraca dokładnie te same dane co zestawienie w Subiekcie. Więc to NIE JEST problem z ilością danych albo słabą maszyną tylko ze słabym zapytaniem.

 

Dla porównania z 798 linijkowym potworkiem moje zapytanie:

select
  s.Nazwisko,
  sum(p.Ilosc),
  sum(p.Wartosc_NettoPoRabacie),
  sum(p.Wartosc_BruttoPoRabacie),
  sum(p.KosztMagazynowy),
  sum(p.Wartosc_NettoPoRabacie) - sum(p.KosztMagazynowy)
from
  ModelDanychContainer.Dokumenty d
  join ModelDanychContainer.PozycjeDokumentu p on p.Dokument_Id = d.Id
  join ModelDanychContainer.Osoby s on s.Id = d.WystawilaOsobaId
where
  d.Symbol in (N'FL',N'FM',N'FS',N'FU',N'PA',N'PF',N'PI',N'KFD',N'KFL',N'KFM',N'KFS',N'KFU',N'ZW',N'ZWf',N'ZWi',N'WF')
  and d.DataWprowadzenia between '2021-07-07 00:00:00' and '2021-07-07 00:00:00'
group by 
  s.Nazwisko

 

Tak, wiem że w Subiekcie można wybrać jeszcze inne warunki ale chyba nie chodzi o to, żeby napisać zapytanie, które ma wszystkie warunki a potem wstawiać wszędzie `null` tylko żeby to zrobić z głową. Programistą zawodowo jestem od 16 lat, 13 lat siedziałem w systemach ERP więc wiem jak to wygląda od środka - jak kierownictwo stwierdzi, że olewamy raporty to potem są takie kwiatki. 

Co więcej to nie jest tak, że mnie to dotknęło i nikogo innego - na forum jest trochę postów po wpisaniu w wyszukiwarce "timeout", a będzie ich coraz więcej. Ta konkretna instalacja subiekta pracuje od 01.03.2018, czyli ponad 3 lata natomiast Nexo zaistniało w 2014 roku. Średnio wystawianych jest 200-300 dokumentów sprzedażowych na dzień. Coraz więcej podmiotów z mniejszą ilością dokumentów zacznie odczuwać skutki koślawych zapytań bo dane w bazach klientów raczej się rozrastają a nie zmniejszają.

[Przemysław Werner 394]

Doraźnie zalecam zwiększyć timeout, a docelowo przyjrzymy się działaniu raportu do jednej z najbliższych wersji InsERT nexo.

[Paweł Szczygieł 36]

Panie Przemysławie, jeżeli dobrze myślę Nexo korzysta z zupełnie innego modelu dostępu do bazy danych. Model który stanowi elastyczne podejście do obiektów biznesowych kosztem dobrych praktyk SQL - ponieważ to warstwa pośrednia tworzy zapytania. 

[Przemysław Werner 394]

19 godzin temu, Paweł Szczygieł napisał:

Panie Przemysławie, jeżeli dobrze myślę Nexo korzysta z zupełnie innego modelu dostępu do bazy danych. Model który stanowi elastyczne podejście do obiektów biznesowych kosztem dobrych praktyk SQL - ponieważ to warstwa pośrednia tworzy zapytania. 

Zgadza się – nexo korzysta z języka zapytań LINQ, który jest później automatycznie przekształcany na zapytania SQL. Porównywanie zapytań generowanych automatycznie z języka LINQ z zapytaniami pisanymi ręcznie w SQL nie ma większego sensu. Zapytania generowane automatycznie z pewnością będą bardziej rozbudowane, ale nie oznacza to, że zawsze będą przez to wolniejsze.

W dniu 12.07.2021 o 23:01, Michał Dyrała napisał:

Co więcej to samo zestawienie napisane w SQLu wykonuje się 141ms (mili sekund, czyli niecałą sekundę!) i zwraca dokładnie te same dane co zestawienie w Subiekcie. Więc to NIE JEST problem z ilością danych albo słabą maszyną tylko ze słabym zapytaniem.

Jeżeli jest Pan w stanie napisać takie zestawienie, które będzie zwracać takie same wyniki i wykonywać się dużo szybciej, to zawsze istnieje możliwość wykorzystania udostępnionej funkcjonalności raportów własnych SQL lub LINQ. Proszę jednak pamiętać, że wbudowane zestawienia muszą uwzględniać wiele przypadków biznesowych, które być może akurat w Państwa firmie nie występują, takich jak m.in.:

• dokumenty w walucie obcej
• dokumenty zaliczkowe oraz wydania/korekty wydań do nich
• usługi z materiałami oraz korekty materiałów
• różne daty kwalifikacji dokumentów
• opłaty specjalne.

To właśnie konieczność uwzględniania tych i innych przypadków powoduje, że wbudowane zestawienia są bardziej skomplikowane, a przez to wolniej się wykonują.

[Michał Dyrała 3]

Wersja 36.0.1 naprawiła problem - miesiąc wylicza się w kilka sekund, wielki ukłon dla zespołu za potraktowanie sprawy poważnie.

@Paweł Szczygieł czyli to nie do końca jest tak, że się nie da - owszem ORM ma swoje ograniczenia i generowany przez niego SQL potrafi być brzydki i wolny ale jak wszędzie - w większości przypadków wynik zależy od człowieka (programisty) a nie automatu (ORMa)

[Wojciech Łozdowski 20]

Namierzyłem podobny problem dla raportu "Sprzedaż wg kategorii". Zestawienie za jeden m-c generuje się około 15 minut, za poprzednie m-ce (dla starszych wersji nexo) 15 sekund.

 

Zgłoszenie [INS-648857].

[Przemysław Werner 394]

Dziękujemy za zgłoszenie. Problem zostanie poprawiony w najbliższej wersji InsERT nexo.