Optymalizacja zapytania

0

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.

0
  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).
0
  1. co to ma robić (co zwracać)
  2. struktury tabel
  3. query plane
  4. indeksy
0
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.

0

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

0

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

0

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.

0

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 :)

0
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ł.

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

  1. 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.%' ?

0
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ł.

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

  1. 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. :)

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