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

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

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

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

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

Załóż na nowo indeksy.

@ 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

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.

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

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

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" ?

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.

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 ?

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ć.

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

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.