Indeksowanie z własnymi tablicami w bazie NEXO

[Radomił Ząbik 308]

Na początku wspomnę, że nie mam jakiegoś ogromnego doświadczenia z MSSQL, większość życia, spędziłem w MySQL i staram się w oparciu o tą wiedzę działać. Posiadam w bazie dodatkową tablicę, rozszerzającą o dodatkowe dane tablice PozycjeDokumentu, która nazywa się AKC_PozycjeDokumentu. Kolumna Id w mojej tablicy, jest jednoznaczna, z kolumną Id w tablicy Pozycji. Podczas optymalizowania pewnych zapytań, zauważyłem, że dołączanie mojej tablicy przez JOIN znacząco spowalnia zapytanie. Idąc swoimi doświadczeniami z MySQL, podejrzewałem jakiś brak kluczy, w mojej tablicy i porównując podobną relację w bazie NEXO (Asortymenty / Asortymenty_PolaWlasneAsortyment), zauważyłem, że prawdopodobnym problemem mojej tablicy jest brak FOREIGN KEY. Podjąłem próbę dodania, ale otrzymuje błąd, przy próbie dodania z kontrolą istniejących danych, a po jej wyłączeniu, klucz co prawda się tworzy, ale nie wpłynął nic a nic na wydajność.

Zapytanie wygląda tak:

ALTER TABLE [ModelDanychContainer].[AKC_PozycjeDokumentu]
ADD CONSTRAINT [AKC_PozycjeDokumentu_key] FOREIGN KEY ([Id]) 
  REFERENCES [ModelDanychContainer].[PozycjeDokumentu] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

Czy generalnie dobrze wyśledziłem problem z wydajnością? A może moja tablica jest ogólnie źle zrobiona? Pomożecie?

Dodatkowo, zapytanie z strukturą mojej tablicy:

CREATE TABLE [ModelDanychContainer].[AKC_PozycjeDokumentu] (
  [Id] int NOT NULL,
  [Uwagi] varchar(512) COLLATE Polish_CI_AS NULL,
  [Potwierdzenie] bit NULL,
  [MaterialKlienta] bit NULL,
  [UwagiRezerwacjiBlach] varchar(512) COLLATE Polish_CI_AS NULL,
  [BlachaProdukcyjna] bit NULL,
  [BlachaProdukcyjnaUwagi] varchar(128) COLLATE Polish_CI_AS NULL,
  PRIMARY KEY CLUSTERED ([Id])
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO

 

[Przemysław Werner 393]

Czy mógłby Pan opisać ten błąd, który jest zwracany? Skąd biorą się zapisy w tych tabelach? Kiedy są one dodawane, aktualizowane, usuwane?

Edytowane 5 Maja 2020 przez Przemysław Werner

[Radomił Ząbik 308]

Błąd:

Cytat

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "AKC_PozycjeDokumentu_key". The conflict occurred in database "Nexo_InvestTech", table "ModelDanychContainer.PozycjeDokumentu", column 'Id'.

Zapisy dodaję sam. Podstawowy tworzę monitorując czy brakuje mi zapisu w mojej tablicy, o tak:

SELECT TOP 1000 pos.Id 
FROM ModelDanychContainer.PozycjeDokumentu AS pos 
LEFT JOIN ModelDanychContainer.AKC_PozycjeDokumentu AS posAdv ON posAdv.Id=pos.Id 
WHERE posAdv.Id IS NULL ORDER BY pos.Id DESC;

Usuwać, nie usuwam, bo wszędzie moja tablica jest jako INNER JOIN do PozycjeDokumentu, więc jak ich nie będzie, to mogą Sobie moje wisieć. Aktualizuje w różnych momentach po stronie zmian z mojego systemu. Dane są rozszerzeniem danych pozycji dokumentu, bo nie chcieliście nigdy zrobić do nich Pól Własnych

[Paweł Kubacki 203]

Cytat

Usuwać, nie usuwam, bo wszędzie moja tablica jest jako INNER JOIN do PozycjeDokumentu, więc jak ich nie będzie, to mogą Sobie moje wisieć.

Jeżeli chce Pan wprowadzić FOREIGN KEY do swojej tabeli, to niestety nie mogą wisieć. Prawdopodobnie to właśnie te wiszące pozycje uniemożliwiają założenie klucza obcego. Należałoby więc zrobić dwie rzeczy. Pierwsza to wyczyszczenie wszystkich wiszących pozycji, które nie mają już swojego odwzorowania w pozycjach dokumentów. Druga to zapewnienie aby przy usuwaniu pozycji dokumentu były też usuwane pozycje z Pana tabeli. W przeciwnym przypadku może przestać działać usuwanie pozycji na dokumentach i usuwanie dokumentów. Powinno pomóc ustawienie ON DELETE CASCADE.

Samo wprowadzenie FOREIGN KEY może być niewystarczające. Jednak o tym za chwilę. Proszę najpierw wprowadzić ten klucz obcy i sprawdzić czy działa usuwanie pozycji i całych dokumentów. Proszę też zweryfikować czy po wprowadzeniu klucza obcego cokolwiek się zmieniło w wydajności tych zapytań.

Edytowane 5 Maja 2020 przez Paweł Kubacki

[Radomił Ząbik 308]

Ok, pozycje złe wywalone, klucz dodany już bez błędów. Mój skrypt dopisał pozycje, a podczas usuwania dokumentu, zostały one skasowane. Niestety, zerowy wpływ na wydajność

Edit: No dobra, jest lekki wpływ na wydajność - na bezpośrednich zapytaniach PHP - SQL widoczna.

Edytowane 5 Maja 2020 przez Radomił Ząbik

[Paweł Kubacki 203]

Tak niestety przypuszczałem. Proszę sprawdzić czy i jakie indeksy są założone na tą własną tabelę. Proszę też przesłać warunek łączenia pozycji dokumentu z tą własną tabelą w Pana raporcie.

[Radomił Ząbik 308]

Nie ma innych indeksów na moje tabeli, oczywiście poza PRIMARY. Najprostsze zapytanie jakim testuje, to to:

SELECT TOP 1000 pos.Id 
FROM ModelDanychContainer.PozycjeDokumentu AS pos
LEFT JOIN ModelDanychContainer.AKC_PozycjeDokumentu AS posAdv ON posAdv.Id=pos.Id 
WHERE posAdv.Id IS NULL ORDER BY pos.Id DESC;

Wynik na poziomie 300-400ms, na manedżerze SQL.

Dla porównania podpiąłem pod tablicę RealizacjePozycji:

SELECT TOP 1000 pos.Id 
FROM ModelDanychContainer.PozycjeDokumentu AS pos
LEFT JOIN ModelDanychContainer.RealizacjePozycji AS posAdv ON posAdv.PozycjaRealizowanaId=pos.Id 
WHERE posAdv.Id IS NULL ORDER BY pos.Id DESC;

I tu mam 30-100ms, co biorąc pod uwagę ilość zapisów, daje zdecydowanie lepszy wynik, na manedżerze SQL.

Bezpośrednie zapytania do SQL z poziomu PHP, spadły z 300ms, poniżej 100ms, więc tutaj jest odczuwalne, więc zaczynam się zastanawiać, czy to nie wina programu do edycji, tylko czemu akurat na mojej tablicy muli?

I tak, wiem, że walczę o ms, ale ogólnie chciałem uzyskać więcej informacji, na temat prawidłowego integrowania tablic własnych z waszymi identyfikatorami, aby przy tak dużych bazach jak nasze, zachować maksymalną wydajność

Edytowane 5 Maja 2020 przez Radomił Ząbik

[Paweł Kubacki 203]

Trochę się pogubiłem. To jak długo się wykonuje to zapytanie, które stwarza problemy? Te czasy, które Pan do tej pory podał nie wskazują na problemy wydajnościowe. Każdy JOIN wydłuża czas wykonania zapytania, więc to, że akurat po połączeniu z własną tabelą zapytanie nieco spowolniło to nic dziwnego.

Cytat

... zaczynam się zastanawiać, czy to nie wina programu do edycji, tylko czemu akurat na mojej tablicy muli?

Proszę rozwinąć tą myśl, gdyż nic nie wiem o tym programie, z którego Pan korzysta. Jeżeli problem jest widoczny w jakimś zewnętrznym programie, a takie samo zapytanie wykonywane z poziomu Management Studio wykonuje się szybko, to warto sprawdzić profilerem SQL co ten program rzeczywiście wysyła do SQLa.

[Radomił Ząbik 308]

Zapytanie, które Panu podałem, wcześniej zarówno w programie do zarządzania bazą, jak i bezpośrednio w użyciu PDO w PHP, wykonywało się stosunkowo długo, jak na Swoją prostotę, bo 0,4s (400ms). Dlatego też poruszyłem temat kluczy, bo zauważyłem, że podłączając wasze tabele, zarówno w programie jak i PHP szło w 0,03s (300ms). Po operacjach, które Pan wskazał, po stronie programu jest bez zmian, ale znowu po stronie PHP, jest ten poziom poniżej 0,1s (100ms), co jest dla mnie satysfakcjonujące. Może to niewielka różnica, ale staram się teraz ograniczyć masowo cykliczne zapytania do bazy NEXO, aby jej niepotrzebnie nie obciążać. Więc efekt jest, po poprawieniu klucza Dodatkowo, chciałem poznać zasady działania kluczy, aby poprawić też inne moje tabele - ta jest przykładowa tak naprawdę, mam ich więcej

Więc generalnie efekt już uzyskałem i jest on na tą chwilę akceptowalny dla tej tabeli i zastosuje je dla innych tabel także

Mam jeszcze jedno zapytanie uzupełniające. Ponieważ elementem tego klucza, było automatyczne usuwanie wierszy połączonych z mojej tablicy, to czy w przypadku gdybym jakimś trafem, dopisał do mojej AKC_PozycjeDokumentu, dopisał pozycję, którego Id nie ma w PozycjeDokumentu, spowodowałbym jakiś problem po stronie tablicy PozycjeDokumentu, który uniemożliwi pracę NEXO - pytam, bo te pozycje, uniemożliwiały utworzenie klucza. Na pewno będzie to problemem uniemożliwiającym konserwację bazy danych, tego jestem świadomy.

[Paweł Kubacki 203]

Cytat

... gdybym jakimś trafem, dopisał do mojej AKC_PozycjeDokumentu, dopisał pozycję, którego Id nie ma w PozycjeDokumentu, spowodowałbym jakiś problem po stronie tablicy PozycjeDokumentu ...

Nie uda się dopisać pozycji do AKC_PozycjeDokumentu z Id, którego nie ma w tabeli PozycjeDokumentu. Tak samo nie udałoby się usunięcie pozycji z PozycjeDokumentu bez usuwania skojarzonego wpisu w AKC_PozycjeDokumentu.

[Radomił Ząbik 308]

Ok, czyli jestem bezpieczny

Dzięki wielkie za pomoc i wyrozumiałość!