Optymalizacja SQL podczas importu kontrahentów z pliku EDI

[Krzysztof Lipczyk 1]

Czy ma ktoś pomysł na optymalizację związaną z zapytaniem SQL, które jest wykonywane podczas importu z pliku EDI w fazie importowania kontrahentów. Uruchomione na RewizorGT (1.64 HF2 (1.6402.2.4882). Plik EDI zawiera zwykle po 5-15 tys. kontrahentów i podobną ilość dokumentów handlowych. 

Zapytanie (w miejscu numer_nip jest oczywiście konkretny NIP):

SELECT TOP 1 kh_Id, adr_Id, adrh_Id FROM kh__Kontrahent AS a  INNER JOIN adr__Ewid AS b ON a.kh_Id=b.adr_IdObiektu INNER JOIN adr_Historia AS c ON b.adr_Id=c.adrh_IdAdresu  WHERE adr_TypAdresu=1 AND REPLACE(REPLACE(adr_NIP,'-','' ),' ','')='numer_nip' ORDER BY adrh_Id DESC 

SQL Profiler zwraca informację, że czas przetwarzania takiego zapytania wynosi od 11 do 13 sekund. Czy ma ktoś pomysł jak to przyspieszyć? Zapytanie wykonywane jest na SQL Express 14.0.20.27 na dość starym sprzęcie (Q8200 @2.33Ghz, 8GB RAM, SSD, Win10Pro), ale to nie jest raczej powód.

Gdy tylko z tego zapytania usuniemy fragment AND REPLACE(REPLACE(adr_NIP,'-','' ),' ','')='numer_nip' i zastąpimy to adr_NIP='numer_nip' to zapytanie wykonywane jest błyskawicznie. Wygląda to tak, że funkcja REPLACE mieli kolumnę z numerami NIP. Ma ktoś jakiś pomysł na to?

 

[Daniel Kozłowski 1 171]

Co do optymalizacji to oczywiście pomysły są, optymalizacja jest relatywnie prosta, lecz w tym przypadku może przeprowadzić ja tylko producent.

Nie mniej przy tym sprzęcie bardzo dużą zmianę przyniesienie zmiana sprzętu na coś normalnego / aktualnego / zdecydowanie wydajniejszego. Warto też rozważyć dużo wydajniejszy i mniej problematyczny model pracy - praca na wspólnej bazie danych (nie wiadomo z jakiego programu pochodzą dane). 

[Krzysztof Lipczyk 1]

8 minut temu, Daniel Kozłowski napisał:

Warto też rozważyć dużo wydajniejszy i mniej problematyczny model pracy - praca na wspólnej bazie danych (nie wiadomo z jakiego programu pochodzą dane). 

Niestety praca na wspólnej bazie danych odpada. Klient pracuje na WAPRO i jedyna opcja to niestety konwersja plików eksportowych do EPP. Pliki zawierają bardzo dużą ilość pozycji (dziesiątki tysięcy miesięcznie). Praca na wspólnym środowisku i bazie byłaby spełnieniem marzeń  bo wiadomo ma to więcej korzyści niż import z EPP.

28 minut temu, Daniel Kozłowski napisał:

zmiana sprzętu na coś normalnego / aktualnego / zdecydowanie wydajniejszego. 

Dobrze się, składa bo lada dzień ten staroć zostanie zastąpiony trochę młodszą maszynką i chętnie podzielę się  rezultatami. Następcą jest i7-6700, 16GB RAM, M.2 SSD, Win10Pro z opcją rozszerzenia pamięci RAM do 32GB ale najpierw muszę zobaczyć czy w przypadku SQL Express ma to jakikolwiek sens. Baza po 2 latach ma ok. 4 GB, także jeszcze jest limit. 

 

[Paweł Szczygieł 36]

Proszę podesłać plan wykonania zapytania.

[Daniel Kozłowski 1 171]

23 godziny temu, Paweł Szczygieł napisał:

Proszę podesłać plan wykonania zapytania.

W czym ta informacja będzie przydatna ? Takie zapytanie w Microsofcie nigdy nie skorzysta z indeksu, więc zawsze będzie wykonywało się nieoptymalnie...

 

ps.

Ilu kontrahentów znajduje się w bazie danych Rewizora i dlaczego - do czego są potrzebni ? To oczywiste, ale napiszę - gdyby była relatywnie mała liczba kontrahentów to zapytanie naturalnie też wykonywałoby się dużo szybciej.

[Krzysztof Lipczyk 1]

W bazie znajduje się ok. 800 tys. kontrahentów, 99% niepotrzebnych gdyż mają powiazania tylko z rejestrem VAT i kontem bez rozrachunków. Byłaby to bardzo fajna opcja, gdyby InsertGt nie umieszczał automatycznie kontrahentów jeśli nie tworzy się dla nich rozrachunków. Kiedyś testowo usunąłem wszystkich kontrahentów żeby sprawdzić czy import dokona dekretów i wpisów w ewidencji VAT bez zakładania kartotek dla nich, ale nie - nawet z usuniętymi kontrahentami dodaje ich na podstawie zapisów z nagłówków dokumentów. 

Najchętniej usunąłbym wszystkich kontrahentów, którzy nie mają przypisanych kont analitycznych - ich jedyne powiązanie jest z wpisem w rejestrze VAT, ale przyznam szczerze, że nie wiem czy to nie naruszyłoby integralności danych.

 

Co do planów wykonania, to SSMS sugerował założenie 2 indeksów dla kolumn. Poprawiło to szybkość wykonywania zapytania o jakieś ~500ms. 

[Paweł Szczygieł 36]

Może Pan podesłać ten plan.

[Daniel Kozłowski 1 171]

3 godziny temu, Krzysztof Lipczyk napisał:

W bazie znajduje się ok. 800 tys. kontrahentów, 99% niepotrzebnych, gdyż mają powiazania tylko z rejestrem VAT i kontem bez rozrachunków.

Tak, taki "nieistotny szczegół".

 

3 godziny temu, Krzysztof Lipczyk napisał:

Byłaby to bardzo fajna opcja, gdyby InsertGt nie umieszczał automatycznie kontrahentów jeśli nie tworzy się dla nich rozrachunków. Kiedyś testowo usunąłem wszystkich kontrahentów żeby sprawdzić czy import dokona dekretów i wpisów w ewidencji VAT bez zakładania kartotek dla nich, ale nie - nawet z usuniętymi kontrahentami dodaje ich na podstawie zapisów z nagłówków dokumentów. 

Dobrze wiadomo jak działa program, wiedząc to można uniknąć tego typu problemów... Skoro jest wykorzystywany konwerter to można scalić kontrahentów / zamienić na jednego typu "kontrahent jednorazowy" / "sprzedaż detaliczna".

 

3 godziny temu, Krzysztof Lipczyk napisał:

Najchętniej usunąłbym wszystkich kontrahentów, którzy nie mają przypisanych kont analitycznych - ich jedyne powiązanie jest z wpisem w rejestrze VAT, ale przyznam szczerze, że nie wiem czy to nie naruszyłoby integralności danych.

Nie rozumiem wątpliwości - przecież program pozwala dodawać dokumenty VAT bez kontrahentów, więc jeśli kontrahenci zostaną odpięci od dokumentów to nie naruszy to integralności.

 

Dnia 25.10.2020 o 11:21, Krzysztof Lipczyk napisał:

z opcją rozszerzenia pamięci RAM do 32GB ale najpierw muszę zobaczyć czy w przypadku SQL Express ma to jakikolwiek sens.

No nie ma czego sprawdzać, wymagania systemowe SQL'a są bardzo dobrze znane - nie ma sensu.

[Krzysztof Lipczyk 1]

Cytat

Dobrze wiadomo jak działa program, wiedząc to można uniknąć tego typu problemów... Skoro jest wykorzystywany konwerter to można scalić kontrahentów / zamienić na jednego typu "kontrahent jednorazowy" / "sprzedaż detaliczna".

Tak, zdaję sobie sprawę z takiego rozwiązania, ale ze smutkiem na chwilę obecną nie mam takiej możliwości. Taka zmiana rodziłaby konieczność dostosowania pewnych procesów u klienta. Męczę temat od 2 lat  

Cytat

Nie rozumiem wątpliwości - przecież program pozwala dodawać dokumenty VAT bez kontrahentów, więc jeśli kontrahenci zostaną odpięci od dokumentów to nie naruszy to integralności.

Czyli wygląda na to, że taką bazę kontrahentów można oczyścić - to też jest ciekawe rozwiązanie warte uwagi. 

 

Cytat

Może Pan podesłać ten plan.

Proszę:

[Daniel Kozłowski 1 171]

Dnia 27.10.2020 o 01:49, Krzysztof Lipczyk napisał:

Cytat

Dobrze wiadomo jak działa program, wiedząc to można uniknąć tego typu problemów... Skoro jest wykorzystywany konwerter to można scalić kontrahentów / zamienić na jednego typu "kontrahent jednorazowy" / "sprzedaż detaliczna".

Tak, zdaję sobie sprawę z takiego rozwiązania, ale ze smutkiem na chwilę obecną nie mam takiej możliwości. Taka zmiana rodziłaby konieczność dostosowania pewnych procesów u klienta. Męczę temat od 2 lat  

Niestety nie rozumiem problemu - jeśli są jakieś kryteria, w oparciu o które dane są importowane do księgowości to dlaczego nie można ich wykorzystać krok wcześniej, w konwerterze.

[Krzysztof Lipczyk 1]

Przepisy wymagają ujawnienia w plikach JPK nabywców - każdy musi mieć swój wpis w ewidencji VAT a aktualne zmiany nie upraszczają tego, a wręcz przeciwnie zmuszą do wpisania wszystkiego. Zatem scalenie odbiorcy w jednego kontrahenta odpada. Pozyskiwane dane ze źródła nie tworzą rozróżnienia na klienta detalicznego i niedetalicznego, więc to też odpada. Zostaje tylko kontrahent jednorazowy, ale z tego co się orientuje to on też uzyskuje wpis w tabeli kh__Kontrahent, jak wszyscy, tylko ma specyficzny symbol w kolumnie kh_symbol z wartością "********************" - czy takie rozwiązanie przyspieszy procesy importu czy nie tego nie wiem, jeśli ktoś z was wie to proszę o podpowiedź. Czy takie rozwiązanie spowoduje, że zapytanie SQL, o którym mowa w tym poście, nie będzie uruchamiane?

Zmiany procesów u klienta można by ustawić tak, żeby selektywnie importować rodzaje transakcji - właśnie z podziałem na detal i firmy, jednak obecne zmiany VAT po części utopiły ten temat. Fiskalizacja transakcji znacznie ułatwiłaby sprawę, ale niestety to rozwiązanie zostało odrzucone.