MSSQL : Kolejnosc warunkow w sql
Diesel
Nurtuje mnie pewne pytanie
Mam dosc skomplikowane zapytanie w SQL
Wybieram grupy rekordow, dokladnie jakie - to precyzuje w warunkach
Oglednie rzecz biorac warunki wygladaja nastepujaco:
where (...)and(..)and((...)or((..)and((..)and(..))or(..))
troszke skomlikowane nie?
ale wynikiem jest zestaw "blednie" wprowadzonych rekordow do systemu. Mozna
by to wyciagnac prostrzymi zapytaniammi i zlaczyc je w union... ale
slyszalem ze union duzo wolnij dziala.
Ostatnio zauwazylem ze jeden z warunkow generuje blad. Co wczesniej nie
wystepowalo. A mianowicie chodzi o typ pola, w ktorym zawsze wpisywana byla
liczba(jako string) w zaleznosci od wartosci poprzedniego pola - we
wczesniejszym warunku where. Obecnie server zachowuje sie tak, jakby drugi
warunek byl sprawdzany dla wszystkich rekordow a nie dla tych, ktore
spelniaja pierwszy warunek.
Stad moje pytanie: jak ustawic kolejnosc warunkow? Czy mozna to jakos
regulowac - ingerowac w statystyki?
Pozdrawiam
Dieselek
Paweł Pluta
<kkdi...@poczta.onet.pl> wrote:
[...]
> wczesniejszym warunku where. Obecnie server zachowuje sie tak, jakby drugi
> warunek byl sprawdzany dla wszystkich rekordow a nie dla tych, ktore
> spelniaja pierwszy warunek.
No i to wydaje mi się normalne.
> Stad moje pytanie: jak ustawic kolejnosc warunkow? Czy mozna to jakos
> regulowac - ingerowac w statystyki?
IODP nie za bardzo da się to zrobić. Sam kiedyś chciałem przestawiać
warunki.
Paweł Pluta
--
- Is life always like this or only when you're a child?
- Always like this.
Jacek Zaleski
> warunek byl sprawdzany dla wszystkich rekordow a nie dla tych, ktore
> spelniaja pierwszy warunek.
Nie bardzo rozumiem Twój problem. Jeżeli zapytanie zwraca zły wynik,
to błędne są warunki, a nie ich kolejność wykonywania przez serwer.
Jeżeli wynik jest dobry, to w ogóle nie przejmuj się kolejnością wykonywania
poszczególnych warunków. Ta kolejność będzie się zmieniać właśnie w
zależności od statystyk. Twoja ingerencja może znacznie spowolnić wykonanie
zapytania.
BTW, jest hint FORCE ORDER, który wymusza kolejność złączeń, a więc
pośrednio kolejność warunków.
Jacek Zaleski
Paweł Pluta
<j...@simple.com.pl> wrote:
> Gdzie zginął post, do którego pisałem odpowiedź???
Ja nie brałem.
Maciej Kromrych
"Jacek Zaleski" <j...@simple.com.pl> wrote in message
news:a9p0c0$1241$1...@news2.ipartners.pl...
> >Obecnie server zachowuje sie tak, jakby drugi
> > warunek byl sprawdzany dla wszystkich rekordow a nie dla tych, ktore
> > spelniaja pierwszy warunek.
> To nie jest C :-))).
>
> Nie bardzo rozumiem Twój problem. Jeżeli zapytanie zwraca zły wynik,
> to błędne są warunki, a nie ich kolejność wykonywania przez serwer.
Ja go chyba rozumiem, weź taki przykład:
... WHERE ISNUMERIC(pole) = 1 AND pole > 10
Tu druga część wyrażenia ma sens tylko jak pierwsza jest prawdziwa.
Dlatego kolejność jest ważna oraz to czy dla wierszy nie spełniających
pierwszego warunku będzie jeszcze sprawdzany drugi warunek (co w
tym przypadku nie jest potrzebne).
Jestem ciekaw co na to powiesz, bo mi się przypomniało, że w jednym
swoim programie mam select wybierający błędne numery pesel, który
działa na tej zasadzie. Warunek to coś w stylu:
pesel_nie_składa_się_z_11_cyfr OR (pesel_składa_się_z_11_cyfr AND
warunek_na_sumę_kontrolną_nie_jest_spełniony)
Chodzi o to, żeby suma kontrolna była sprawdzana tylko dla peseli, które
składają się z 11 cyfr. Nikt mi dotychczas nie zgłosił, żeby to powodowało
błędy, sam też to intensywnie testowałem z różnego rodzaju danymi,
liczbowymi i tekstowymi. Ale z tego co napisałeś wynika, że jak się
zmienią statystyki serwera, to może się to sypnąć, tak?
Maciej
Jacek Zaleski
>Tu druga część wyrażenia ma sens tylko jak pierwsza jest prawdziwa.
Drugi warunek oznacza wtedy, że pole > znak_o_kodzie_10.
Dlatego to działa i nie ma nic wspólnego z ilością sprawdzonych
warunków.
Sprawdzanie warunków tylko do momentu znalezienia wyniku
jest zupełnie sensowną implementacją (i pewnie większość serwerów
tak robi), ale ponieważ standard SQL nic na ten temat nie mówi,
więc nie możesz założyć, że to będzie działać. Poza tym warunki na
pewno nie są sprawdzane w kolejności ich zapisu, więc nie możesz
zakładać, że ISNUMERIC(pole) = 1 będzie wyliczone przed pole > 10.
>Ale z tego co napisałeś wynika, że jak się
>zmienią statystyki serwera, to może się to sypnąć, tak?
Jak sama nazwa wskazuje, statystyki opisują charakterystykę danych
w bazie. Jeżeli zaczniesz tam grzebać, to charakterystyki będą oszukane,
co spowoduje spadek wydajności zapytań. Jeżeli już koniecznie chcesz
ręcznie sterować procesem optymalizacji zapytania, to służą do tego hinty.
Jacek Zaleski
No nie wiem, nie wiem... ;-))
Ty też maczałeś w nim palce.
Ireneusz Pełka
news:aa0dee$1ksk$1...@news2.ipartners.pl...
> Sprawdzanie warunków tylko do momentu znalezienia wyniku
> jest zupełnie sensowną implementacją (i pewnie większość serwerów
> tak robi), ale ponieważ standard SQL nic na ten temat nie mówi,
> więc nie możesz założyć, że to będzie działać. Poza tym warunki na
> pewno nie są sprawdzane w kolejności ich zapisu, więc nie możesz
> zakładać, że ISNUMERIC(pole) = 1 będzie wyliczone przed pole > 10.
Chyba zaszla tu wielka pomylka, ktora dodatkowo wprowadza w blad mniej
wtajemniczonych. Warunki w MSSQL _sa_wykonywane_ od lewej do prawej. I ten
fakt nalezy wykorzystywac przy pisaniu zapytan umieszczajac od lewej warunki
najbardziej efektywne az do mniej efektywnych. Zmiana kolejnosci warunkow
moze znacznie wplynac na wydajnosc takiego zapytania, moze rowniez
spowodowac, ze przestanie byc uzywany index polozony na kilku polach (lub
bedzie uzyty inny mniej wydajny) i wreszcie moze powodowac bledy np.
konwersji typow.
Prosty przyklad:
create table test(pole varchar(10))
go
insert into test values('1asdas34')
insert into test values('1')
insert into test values('34asd')
insert into test values('10')
go
select * from test where isnumeric(pole) = 1 and convert(numeric(10), pole)
> 3
--bez bledu
select * from test where convert(numeric(10), pole) > 3 and isnumeric(pole)
= 1
--blad konwersji
Pozdrawiam
Irek
Jacek Zaleski
Ale jestem strasznie zaskoczony.
Czy mógłbyś dać namiar na opis w MSSQL takiego działania.
>Zmiana kolejnosci warunkow moze znacznie wplynac na
>wydajnosc takiego zapytania, moze rowniez spowodowac, ze
>przestanie byc uzywany index polozony na kilku polach
należy wiązaś się z kolejnością warunków, bo to optymalizator
dba o kolejność.
Maciej Kromrych
> >WHERE ISNUMERIC(pole) = 1 AND pole > 10
> >Tu druga część wyrażenia ma sens tylko jak pierwsza jest prawdziwa.
> "Druga część" ma sens również wtedy, gdy 'pole' zawiera string.
> Drugi warunek oznacza wtedy, że pole > znak_o_kodzie_10.
> Dlatego to działa i nie ma nic wspólnego z ilością sprawdzonych
> warunków.
Ok, źle dobrałem przykład, ale wiadomo o co chodziło:
o konwersję pola "pole" na wartość numeryczną, która
wywali błąd, jeśli pierwszy warunek nie będzie spełniony.
> Sprawdzanie warunków tylko do momentu znalezienia wyniku
> jest zupełnie sensowną implementacją (i pewnie większość serwerów
> tak robi), ale ponieważ standard SQL nic na ten temat nie mówi,
> więc nie możesz założyć, że to będzie działać. Poza tym warunki na
> pewno nie są sprawdzane w kolejności ich zapisu, więc nie możesz
> zakładać, że ISNUMERIC(pole) = 1 będzie wyliczone przed pole > 10.
Dzięki za wyjaśnienie. Muszę więc przepisać swoje selecty,
żeby wyeliminować przypadkowość.
Maciej
Ireneusz Pełka
> Działanie Twojego przykładu rzeczywiście potwiedza taką teorię.
> Ale jestem strasznie zaskoczony.
> Czy mógłbyś dać namiar na opis w MSSQL takiego działania.
BOL -> (zakladka Index) search conditions -> defining -> sekcja Remarks
> Wszystko co do tej pory czytałem wyraźnie zaznaczało, że nie
> należy wiązaś się z kolejnością warunków, bo to optymalizator
> dba o kolejność.
Widac czytalismy rozne artykuly (jesli chodzi o ten temat) ;))
Pozdrawiam
Irek
R@F
> Działanie Twojego przykładu rzeczywiście potwiedza taką teorię.
> Ale jestem strasznie zaskoczony.
> należy wiązaś się z kolejnością warunków, bo to optymalizator
> dba o kolejność.
jesli dodasz indeks na pole
to rowniez pierwsze zapytanie zwroci blad konwersji
jak obejrzysz estimated execution plan to zobaczysz ze query optimizer
zamienil kolejnosc warunkow
--
R@F
ICQ: 76118452
"Kto ogląda niebo w wodzie, ten widzi ryby na drzewach"
-przysłowie chińskie
Maciej Kromrych
Nie mam dużego doświadczenia z MSSQL (to na wypadak
gdybym napisał coś bez sensu), ale zrobiłem coś takiego:
1) select * from tabela1 where nazwisko > 5
i mam błąd "Syntax error converting the nvarchar value
'KROMRYCH' to a column of data type int."
2) select * from tabela1 where nazwisko > 5 and
nazwisko = 'aaa'
Nie ma błędu! Patrzę na execution plan i tam widzę (w sekcji
Argument), że *drugi* warunek jest wyliczany przed pierwszym.
Ponieważ w tabeli nie ma wierszy z nazwiskiem 'aaa', pierwszy
warunek nie jest sprawdzany i nie ma błędu konwersji.
3) Piszę podobny select dla *innej* tabeli, patrzę w execution
plan i widzę, że warunki są wyliczane od lewej do prawej.
Wniosek: nie można przewidzieć, w jakiej kolejności będą
sprawdzane warunki, czyli Jacek ma rację.
Maciej
R@F
news:aa0mie$qlp$1...@helios.domdata.com...
> > Czy mógłbyś dać namiar na opis w MSSQL takiego działania.
>
> BOL -> (zakladka Index) search conditions -> defining -> sekcja Remarks
faktycznie , jest tam napisane ze warunki o tym samym priorytecie sa
ewaluowane od lewej do prawej, ale query optimizer moze zmienic ta kolejnosc
jesli stwierdzi ze ta zamiana
przyspieszy wykonanie zapytania, tak wiec defacto nigdy nie mozesz byc na
100%
pewny w jakiej kolejnosci sa wykonywane warunki
patrz moja odpowiedz na post jacka zalewskiego , gdzie dodanie indeksu
do tablicy w twoim przykladzie spowodowalo ze query optimizer zamienil
kolejnosc sprawdzania warunkow
Mike
Jacek ma racje. Query optimizer wybiera kolejnosc warunkow na
podstawie estimacji kosztu operaci. Nie ma morzliwosci zmiec tego
zachowania i nie wiadomo jaka kolejnosc bedzie wybrana.
Ale w MSSQL morzes napisac warunek ktory gwarantuje kolejnosc swoich
sub-warunkow urzywajac CASE statement ktory jest proceduralnie short
circuted.
SELECT * FROM tablea1
WHERE
CASE
WHEN isnumeric(nazwisko) = 0 THEN 'no'
WHEN nazwisko > 5 THEN 'yes'
ELSE 'no'
END = 'yes'
OR
nazwisko = 'aaa'
Michal