Radomił Ząbik 308 Napisano 5 Maja 2020 Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO 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 Link to postu
Przemysław Werner 395 Napisano 5 Maja 2020 Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO (edytowane) 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 Link to postu
Radomił Ząbik 308 Napisano 5 Maja 2020 Autor Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO 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 Link to postu
Paweł Kubacki 206 Napisano 5 Maja 2020 Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO (edytowane) 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 Link to postu
Radomił Ząbik 308 Napisano 5 Maja 2020 Autor Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO (edytowane) 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 Link to postu
Paweł Kubacki 206 Napisano 5 Maja 2020 Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO 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. Link to postu
Radomił Ząbik 308 Napisano 5 Maja 2020 Autor Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO (edytowane) 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 Link to postu
Paweł Kubacki 206 Napisano 5 Maja 2020 Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO 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. Link to postu
Radomił Ząbik 308 Napisano 5 Maja 2020 Autor Udostępnij Napisano 5 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO 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. Link to postu
Paweł Kubacki 206 Napisano 6 Maja 2020 Udostępnij Napisano 6 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO 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. Link to postu
Radomił Ząbik 308 Napisano 6 Maja 2020 Autor Udostępnij Napisano 6 Maja 2020 w Indeksowanie z własnymi tablicami w bazie NEXO Ok, czyli jestem bezpieczny Dzięki wielkie za pomoc i wyrozumiałość! Link to postu
Polecane posty