SQL - problem z zapytaniem a może danymi w bazie

0

Zacząłem się bawić SQL-em i od razu na początku mnie przygniotło :(

mam takie zapytanie ( a tak przy okazji czy jest ono poprawne pod względem "optymalizacji" ? )

select DATEPART(wk, R_DATE), Count(R_ID) from Tabela1
WHERE R_DATE >= '130601'
  AND R_DATE <= '130605'
  AND T_MODE = '0'
  AND R_CANC = '0'
  AND USR_NO Like '2%'
  AND R_ID IN (SELECT R_ID FROM Tabela2
                     WHERE R_DATE >= '130601'
                       AND R_DATE <= '130605'
                       AND RET_NO = '0'
                       AND ORDER_NO <> '0'
                       AND ART_NO <> '12345678')
GROUP by DATEPART(wk, R_DATE)
ORDER by DATEPART(wk, R_DATE)

mamy 2 bardzo podobne do siebie tabele z elementami wspólnymi z taką różnicą że jedna zawiera
"nagłówki danych" (1) a druga już jakieś konkretne szczegóły (2) - czyli można by rzec: klasyczny układ
Baza jest oparta na MS SQL 2008 ( wersja express )

i teraz mam dziwną sytuację :
np. próbujemy zmienić kryteria ( zmieniamy tylko i wyłącznie R_DATE w obydwu członach zapytania )

czyli to samo ale dla dat :od '130602' do '130605'

select DATEPART(wk, R_DATE), Count(R_ID) from RECEIPT
WHERE R_DATE >= '130602'
  AND R_DATE <= '130605'
  AND T_MODE = '0'
  AND R_CANC = '0'
  AND USR_NO Like '2%'
  AND R_ID IN (SELECT R_ID FROM ARTICLE
                     WHERE R_DATE >= '130602'
                       AND R_DATE <= '130605'
                       AND RET_NO = '0'
                       AND ORDER_NO <> '0'
                       AND ART_NO <> '12345678')
GROUP by DATEPART(wk, R_DATE)
ORDER by DATEPART(wk, R_DATE)

wszystko pięknie działa.... idziemy dalej

R_DATE >= '130603'
R_DATE <= '130605'

wszystko pięknie działa....

ale już przy

R_DATE >= '130604'
R_DATE <= '130605'

SQL staje okoniem ... i nic nie chce zwrócić... choćby i czekać kilka minut
myślę sobie , może ten dzień '130604' jest jakiś trefny , może jakiś krzak w bazie jest

ale jak zapytam

R_DATE >= '130604'
R_DATE <= '130606'

no to juz mam wynik : ( oczywiście z uwzględnieniem tego dnia 130604 i 05 i 06 )

co ciekawe zakres

R_DATE >= '130604'
R_DATE <= '130604'

też nam zwraca wynik

kombinowałem jeszcze z wieloma wariantami
nawet odległymi w czasie kilku miesięcy

R_DATE >= '130101'
R_DATE <= '130604'

wszystko pięknie działa...

co może być przyczyną takiego zachowania , jak sprawdzić co blokuje mojego SQL, dlaczego nie może zwrócić wyniku
( może gdzieś jakieś sprytne logi gdzie to jest widać ... )
może jakiś niepoprawna konsystencja danych w bazie , ale jak to sprawdzić , jak wyszukać ... itd. ?

dziękuję za wszelkie podpowiedzi

Pozdrawiam
wporzak

0
SELECT DATEPART(wk, R_DATE), COUNT(R_ID) FROM Tabela1 t1 inner join Tabela2 t2
on t1.R_ID = t2.R_ID
and t1.R_DATE >= '130601'
  AND t1.R_DATE <= '130605'
  AND t1.T_MODE = '0'
  AND t1.R_CANC = '0'
  AND t1.USR_NO LIKE '2%'
                     AND t2.R_DATE >= '130601'
                       AND t2.R_DATE <= '130605'
                       AND t2.RET_NO = '0'
                       AND t2.ORDER_NO <> '0'
                       AND t2.ART_NO <> '12345678'
GROUP BY DATEPART(wk, t1.R_DATE)
ORDER BY DATEPART(wk, t1.R_DATE)

tak na szybko. Wykonaj to zapytanie i sprawdz jaj wyglada aktualny plan zapytania

0

dzięki za podpowiedź, ale niestety efekt jest dokładnie taki sam ... przy zakresie dat

R_DATE >= '130604'
R_DATE <= '130605'

totalna zwiecha SQL-a,

dodatkowo , Twoja wersja zapytania zwraca troszkę zawyżone wyniki :
kolumna R_ID w tabeli 2 zawiera powtarzające się wartości
dlatego gdzieś tam trzeba jakiegoś DISTINCT-a wstawić

ale nie to jest chyba głównym problemem ...

wporzak

0

masz jakies skrypty ktore utworza te tabele i zainsertuja dane?
Pokaz jak wyglada plan zapytania.

0

Załóż na nowo indeksy.

0

@ crowa

masz jakies skrypty ktore utworza te tabele i zainsertuja dane?
Pokaz jak wyglada plan zapytania.

żadnych skryptów ... , baza utworzona poprzez kreatora ,
przepraszam ale nie bardzo rozumiem , czym jest "plan zapytania"

@Marcin.Miga
Baza nie posiada indeksów ... czy to, że ich nie ma jest przyczyną takiego zachowania ?

jeszcze kilka, być może istotnych faktów :

baza jest może rzeczywiście i duża, ( składa się z 5 tabel - kilka kolumn, które zawierają odpowiedno )

13 mln
400 tys
1,9 mln
2,4 mln
1,7 mln rekordów

baza służy jako archiwum danych, co jakiś czas dane są uzupełniane poprzez DTS z tabel excelowych

co jakiś czas potrzebny jest jakiś prosty raport ,
i tak testujac natknąłem się na ten dziwny przypadek , przy czym wcześniej pisałem że SQL totalnie odmawia
posłuszeństwa przy wariancie

R_DATE >= '130604'
R_DATE <= '130605'

ale postanowałem go przetrzymać , i... zwróćił dane ale po 8 minutach
dla przypomnienia warianty z sąsiednimu datami, trwają tylko 2-3 sekundy
jedynie ten wariant dat tak dziwnie się zachowuje...

żeby jeszcze było ciekawiej , to jest takich baz jest kilka,
( zawierają dokładnie te same dane ale pochodzą z różnych ośrodków )
, i tam nie występuje takie zjawisko , być może metodą przypadkowości napotkam się znów na taki przypadek.

=============================================
zatem jeszcze raz w skrócie : problem jest ( tylko na jednej bazie ) kiedy wybieram dane za okres

R_DATE >= '130604'
R_DATE <= '130605'

problemu nie ma, kiedy wybieram inny dowolny okres

np.

R_DATE >= '130603'
R_DATE <= '130605'

lub

R_DATE >= '130604'
R_DATE <= '130604'

Pozdrawiam
wporzak

0

13M rekordów i nie masz żadnych indeksów? oO
Co to plan zapytania znajdziesz w 30s przez google. Poszukaj sobie razem z Management Studio, to dowiesz się, jak to włączyć i zobaczyć. Interesuje Cię taka nieco abstrakcyjna wielkość, jaką jest koszt zapytania, oraz, a może przede wszystkim, indeksy sugerowane przez Management Studio. Z mojego doświadczenia wynika, że koszt zapytania ostatniego, "końcowego" elementu wyższy od 0,1 to dużo w przypadku często wykonywanych zapytań. Indeksy znajdziesz na samej górze podglądu planu.
Na szybko znalazłem taki tutorialik: http://www.mssqltips.com/sqlservertip/1945/missing-index-feature-of-sql-server-2008-management-studio/. Jak dodasz najważniejsze indeksy, to zapytania zaczną się wykonywać szybciej nie pięciokrotnie, a o pięć, albo i sześć rzędów wielkości.

0

OK, mogę zgłębić tajniki "zakładania indeksów" itd, itp
ale nurtuje mnie pytanie , jak to możliwe że zapytanie "dwudniowe" typu

select xxx from tabela WHERE r_DATE >= '130604' AND r_DATE >= '130605'

potrzebuje 8 minut ( gdzie mamy do czynienia z powiedzmy z kilkoma tysiącami rekordów do całkowitego przeanalizowania )
a zapytanie kilkumiesięczne ( około 2 mln rekordów ) r_DATE >= '130104' AND r_DATE >= '130605' jest zwracane po 3 sekundach
Indeksy zgadza się , pewnie podwyższają wydajność, ale to chyba nie ten przypadek ... ? , odczyt z bazy jest raz na kilka dni ( żadnych update-ów )
ale jeśli to jest taki przypadek , że trafiliśmy tutaj na fragment danych , które mogą być tylko i wyłącznie szybko odczytane
jeśli jest zastosowany mechanizm indeksacji, to nie będę się upierał , zrobię tak aby działało dobrze, niezależnie od wybranej daty :-)

Pozdrawiam
wporzak

0

Bo np. masz pofragmentowaną tabelę, albo optymalizator głupieje. Porównaj plany obu zapytań, to się dowiesz.

0

Różnice w zapytaniach wyglądają następująco, szczegóły w załączniku - ( o ile o to chodziło )
niestety nie posiadam takiej wiedzy aby stwierdzić czy jest to OK, czy NOT OK ,
i co jest przyczyną takiego zachowania

a jak sprawdzić czy "optymalizator nie głupieje" ?

1

Patrząc na plan zapytania mogę stwierdzić jedno - brakuje mi wiedzy, żeby to wytłumaczyć.
Załóż brakujące indeksy i zobaczysz, że będzie dobrze.

0

No dobra, nie będę się upierał... :-)

ale , ponieważ jeszcze nie zgłębiłem zagadnienia zakładania indeksów,
to oczywiście przy tak postawionym stwierdzeniu

załóż brakujące indeksy
też zaczynam się gubić, jak zidentyfikować typ indeksu, znaczy się , który jest brakujący ( obowiązkowy )
a który można sobie darować itd

a może po prostu trzeba zastosować wskazówkę jaka jest wyświetlana podczas uruchamiania planu :( missing index details )

The Query Processor estimates that implementing the following index could improve the query cost by 70.4406%.
USE [nazwaBazy]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Tabela2] ([RET_ITEM],[R_DATE],[ART_NO],[ORDER_NO])
INCLUDE ([R_ID])
GO

czy może to trzeba zrobić jakoś inaczej ?
a czy można założyć indeksy w niewłaściwy sposób ?

0
wporzak napisał(a):

a może po prostu trzeba zastosować wskazówkę jaka jest wyświetlana podczas uruchamiania planu :( missing index details )

Napisałem to kilka postów temu... Jeśli nie czytasz uważnie naszych odpowiedzi, to jaki jest sens odpowiadania?
Po założeniu indeksu uruchom ponownie zapytanie i zerknij na plan, bardzo prawdopodobne, że będziesz mieć kolejny indeks do założenia. Ale weź pod uwagę, że MS czasem się myli i sugeruje założenie indeksu, który już istnieje - bądź ostrzeżon. Ponadto im więcej indeksów, tym wolniej działają inserty i update'y, więc jeśli dołożenie kolejnego indeksu "przyspieszyło" zapytanie o dwa procenty, to możesz sobie taki indeks darować.

0

założyłem indeksy ( "jakieś tam"- defaultowe ) bo jak zaczęłem czytać o rodzajach to się pogubułem , jeszcze jakieś klucze się tam przewijały .... :(
i rzeczywiście , problem już nie wystąpuje,

indeksy założyłem na 2 głównych kolumnach , teraz pewnie pojawią się wątpliwości na których
jeszcze powinienem... rozumiem że jak się znów pojawią jakieś problemy wydajnościowe to znów dodać kolejny indeks ?

który diagram , ( jaki wpis lub wartość ) ukazany planie świadczy że mamy właśnie taki problem

no.. jeszcze dużo muszę się nauczyć ! :-)

dzięki za pomoc

wporzak

0

To nie jest do końca tak, że zakładaasz sobie indeksy jekie chcesz...
Zresztą już to powyżej napisano. odam tylko od siebie, że na etapie projektowania powinno być zaznaczone najczęstsze wybieranie / sortowanie i a tych kolumnach zakładasz indeks(y). Czasem może się zdarzyć, że będziesz musiał założyć indeks wielokolumnowy. W MS SQL kiedyś był taki problem (nie wiem, czy teraz, bo już dość długo z nim nie pracuję), że jeśli w zapytaniu nie użyłeś wszyskich kolumn z indeksu, to domyślnie z niego nie korzystał (ale z tego co pamiętam, to można wymusić stosowanie danego indeksu). Można też zrobić sztuczkę taką:
załóżmy, że mamy indeks na pola "typ", "data" i chcemy wykazać wszystkie dokumenty (bez podziału na typ) z danego okresu. Standardowo zapisalibyśmy: WHERE data BETWEEN ... AND ... ale wtedy istnieje szansa, że nie wykorzystamy indeksu. Więc można zapytanie delikatnie zmodyfikować: WHERE typ=typ AND data BETWEEN ... AND ... co na to samo wychodzi, a indeks już był używany.
Ja osobiście wychodzę z założenia, że zapytania piszemy pod indeksy, nie odwrotnie. A te muszą powstać na etapie projektowania.

0

A wiec (podobno nie zaczyna sie tak zdania).
Roznice w planie zapytania moga wynikac z ilosci danych zwracanych przez laczone dataesty.
Optymalizator odpowiednio dobiera wtedy sposoby zlaczen.

Zaloz indexy (poczytaj tez o indexach typu include). Jedna z technik jakie mozesz zastosowac jest przerzucenie glownego datasetu danych do tabeli tymczasowej i tak zawezony dataset obrabiasz dalej.

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