Optymalizacja zapytania

Witam.
Problem polega na bardzo długim czasie zapytania. Zapytanie wygląda następująco:

SELECT DISTINCT
art.id,
art.kod + ' ',
art.nazwa + ' ',
SUM(m.ilosc),
((SELECT SUM(ISNULL(zdef.ilosc,0))
FROM zamow_poz_def zdef,
zamow_poz poz,
zamow zam
WHERE poz.id = zdef.id_zamow_poz
AND zam.id = poz.id_zamow
AND zdef.id_sl_artykul = art.id
AND (zam.data_potw IS NULL OR zam.data_potw > '20060120 19:00')
AND zam.numer_zamow LIKE 'ZMS%'
AND CAST(SUBSTRING(zam.numer_zamow, 4, CHARINDEX('.', zam.numer_zamow)-4) AS int)
IN ( 62 ) )),
(0),
art.cena,
art.id_sl_sezon
FROM magazyn m,
sl_artykul_def adef,
sl_artykul art
WHERE adef.id = m.id_sl_artykul_def
AND adef.id_sl_artykul = art.id
AND m.ilosc <> 0
AND m.id_sl_magazyn IN ( 10 )
GROUP by art.id, art.kod + ' ', art.nazwa + ' ', art.id_sl_sezon, art.cena
ORDER BY art.kod + ' '

Wiem, że nie wygląda ono łatwo. Problem polega na bardzo długim czasie trwania zapytania. Tutaj pojawia się moja prośba i pytanie. W jaki sposób można by zoptymalizować to zapytanie, może komuś wpadnie do głowy jakiś ciekawy sposób.
Moje kolejne pytanie to czy można scacheowac zapytanie i później te informacje obrabiać. Istnieją kursory ale nie wiem czy można do niego wrzucić wynik zapytania i później te informacje pobrać odfiltrowane na przykład.
Używam SQL Server 2000.

1. Używanie konstrukcji 'FROM a, b, c' powoduje wybranie iloczynu kartezjańskiego z trzech tabel (czyli wybranie COUNT(a) * COUNT(b) * COUNT(c) elementów, i później ograniczanie ich wherami). Użyj odpowiednio skonstruowanych LEFT JOINów.
2. Pozakładaj odpowiednie indeksy.
3. Unikaj używania funkcj w WHERE'ach, czy też innych działań, ponieważ utrudnia to znacznie używanie indeksów (użyj polecenia EXPLAIN żeby sprawdzić czy i jakie indeksy są używane - czasem trzeba siłowo wskazać jakie indeksy mają być użyte).

1. co to ma robić (co zwracać)
2. struktury tabel
3. query plane
4. indeksy

Misiekd napisał(a)

1. co to ma robić (co zwracać)
2. struktury tabel
3. query plane
4. indeksy

nie wiem czy dobrze rozumuje, ale to zapytanie ma zwracać wartości pól.

każde zapytanie typu select zwraca wartości jakichś pól - co ma zwrócić Twoje?
odpowiesz na wszystkie 4 pytania to będzie można coś pomóc, nie - temat zamknięty - wróżka ma wolne

Tutaj jest czynne

Taki mały off-topic. Czy wiecie dlaczego wiele prostych problemów wlecze się na 30 postów? Bo pytacz raczy nas albo brakiem informacji, gdzie piętnastu ludzi metodą zapytań dochodzi o co chodzi albo pytacz coś chce a sam nie wie co. Pamiętaj. Ty piszesz projekt, Ty masz wytyczne, Ty wiesz co on ma robić i jak to robi, Ty masz kod. My nie mamy nic poza informacjami, które widzimy na forum. Tak więc, MisiekD ma rację.

nie zrozumialem pytania MiskaD, teraz dopiero zakumalem, jutro napisze struktury tabel, nie wiem co to jest query plane, a z indeksow nie korzystamy, sami tworzymy relacje miedzy tabelami.

Zainteresuj sie indeksami, w wiekszosci przypadkow ZNACZNIE przyspieszaja dzialanie. Zreszta jak odpowiesz na pytania Miskad, to odpowiedz sie znajdzie. W szczegolnosci Misiekd wie co mowi :)

mysz napisał(a)

1. Używanie konstrukcji 'FROM a, b, c' powoduje wybranie iloczynu kartezjańskiego z trzech tabel (czyli wybranie COUNT(a) * COUNT(b) * COUNT(c) elementów, i później ograniczanie ich wherami). Użyj odpowiednio skonstruowanych LEFT JOINów.

To akurat nie ma znaczenia. Są 2 ograniczenia równościowe między kolumnami z par tabel w sekcji WHERE - query rewrite automatycznie zamieni to na INNER JOIN. W Oracle, MS SQL, DB/2 i PostgreSQL to jest jedna z podstawowych reguł.

2. Pozakładaj odpowiednie indeksy.

Tak, tak i jeszcze raz tak!

Warto sprobować taki zestaw początkowy:
CREATE INDEX zamow_data_potw_idx ON zamow(nr_zamow, data_potw);
CREATE INDEX magazyn_id_sl_idx ON magazyn(id_sl_magazyn);
CREATE INDEX zamow_poz_def_idx ON zamow_poz_def(id_sl_artykul);

Do tego jeszcze przydałoby się wrzucić indeksy na kluczach obcych (czyli wszystkich kolumnach, których nazwy zaczynają się u Ciebie od "id_"). Baza może chcieć ich w pewnych przypadkach uzyć - np. jeśli zdecyduje się na NESTED LOOP JOIN, co jest b. prawdopodobne, jeśli np. podzapytanie wybiera mało (kilka) rekordów.

3. Unikaj używania funkcj w WHERE'ach, czy też innych działań, ponieważ utrudnia to znacznie używanie indeksów (użyj polecenia EXPLAIN żeby sprawdzić czy i jakie indeksy są używane - czasem trzeba siłowo wskazać jakie indeksy mają być użyte).

Tak, ale zwykle można założyć indeksy, które radzą sobie z funkcjami.
PostgreSQL spoko łyka coś takiego:
CREATE INDEX idx ON tabela (lower(kolumna));

BTW: A ten substring w podzapytaniu to nie jest przypadkiem równoważny z:
... LIKE 'ZMS62.%' ?

mysz napisał(a)

1. Używanie konstrukcji 'FROM a, b, c' powoduje wybranie iloczynu kartezjańskiego z trzech tabel (czyli wybranie COUNT(a) * COUNT(b) * COUNT(c) elementów, i później ograniczanie ich wherami). Użyj odpowiednio skonstruowanych LEFT JOINów.

To akurat nie ma znaczenia. Są 2 ograniczenia równościowe między kolumnami z par tabel w sekcji WHERE - query rewrite automatycznie zamieni to na INNER JOIN. W Oracle, MS SQL, DB/2 i PostgreSQL to jest jedna z podstawowych reguł.

2. Pozakładaj odpowiednie indeksy.

Tak, tak i jeszcze raz tak!

Warto sprobować taki zestaw początkowy:
CREATE INDEX zamow_data_potw_idx ON zamow(nr_zamow, data_potw);
CREATE INDEX magazyn_id_sl_idx ON magazyn(id_sl_magazyn);
CREATE INDEX zamow_poz_def_idx ON zamow_poz_def(id_sl_artykul);

Do tego jeszcze przydałoby się wrzucić indeksy na kluczach obcych (czyli wszystkich kolumnach, których nazwy zaczynają się u Ciebie od "id_"). Baza może chcieć ich w pewnych przypadkach uzyć - np. jeśli zdecyduje się na NESTED LOOP JOIN, co jest b. prawdopodobne, jeśli np. podzapytanie wybiera mało (kilka) rekordów.

3. Unikaj używania funkcj w WHERE'ach, czy też innych działań, ponieważ utrudnia to znacznie używanie indeksów (użyj polecenia EXPLAIN żeby sprawdzić czy i jakie indeksy są używane - czasem trzeba siłowo wskazać jakie indeksy mają być użyte).

Tak, ale zwykle można założyć indeksy, które radzą sobie z funkcjami.
PostgreSQL spoko łyka coś takiego:
CREATE INDEX idx ON tabela (lower(kolumna));

BTW: A ten substring w podzapytaniu to nie jest przypadkiem równoważny z:
... LIKE 'ZMS62.%' ?

A jeśli potrzebował pełną optymalizację zapytań, to w stopce jest link. My to robimy zawodowo. :)