1. Problémy se složitými dotazy

1.1. Nadměrné nároky na dotaz mohou způsobit chybný výsledek

Následující ukázka prezentuje problém, který vznikl při vyhodnocování dotazu, který spojuje data z několika tabulek a současně provádí agregované výpočty, viz obr. 1. Hlavním zdrojem dat je tabulka "Akce", k ní jsou vázány záznamy z tabulek "Akce podklady" a "Čerpání", obě vazbou 1:N a v obou se často skutečně vyskytuje několik záznamů pro jednu akci. Ostatní tabulky obsahují vždy jediný záznam odpovídající akci a problémy nezpůsobují.

Při spojení záznamů je provedena volba "Unique Records", aby se zamezilo opakování záznamů na vstupu. Spojení zdrojových záznamů komplikuje spojování více záznamů z tabulek "Akce podklady" a "Čerpání". Po spojení záznamů je proveden výpočet součtu čerpání (položka z tabulky "Čerpání"). Výsledkem je následující hodnota:
Primární klíč
Účel vydání
Cislo
Plánovaná cena
Cerpano
23Objednávky 3520007/97 10 004,00 Kč85 400,00 Kč

Jedině rozborem zdrojových záznamů zjistíme, že vypočtená částka je rovna čtyřnásobku skutečné částky (21 350,- Kč). To koresponduje se skutečností, že k této akci náležely 4 záznamy v tabulce "Akce podklady"

SELECT DISTINCTROW Akce.ID, Akce.Ucel, IIf([Akce].[Ucel]="Vyslání" Or 
  [Akce].[Ucel]="Přijetí",[Akce].[Podminky],[Akce].[Cislo]) AS Cislo, 
  Akce.Datum, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí",
  [Akce].[Stat],[Dodavatele].[Nazev]) AS Nazev, Dodavatele.Specifikace, Akce.Podminky, 
  Uhrady.Uhrada, Uhrady.Cislo AS UhradaCislo, Akce.DruhProst, Akce.Vyrizuje, 
  Akce.PlanCena AS Planovano, Sum(Cerpani.Castka) AS Cerpano
FROM (Uhrady RIGHT JOIN ((Dodavatele RIGHT JOIN Akce ON Dodavatele.ID = Akce.Dodavatel) 
  LEFT JOIN Cerpani ON Akce.ID = Cerpani.Akce) ON Uhrady.ID = Akce.Uhrada) 
  LEFT JOIN AkcePodklady ON Akce.ID = AkcePodklady.Akce
GROUP BY Akce.ID, Akce.Ucel, IIf([Akce].[Ucel]="Vyslání" 
  Or [Akce].[Ucel]="Přijetí",[Akce].[Podminky],[Akce].[Cislo]), 
  Akce.Datum, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí",
  [Akce].[Stat],[Dodavatele].[Nazev]), Dodavatele.Specifikace, Akce.Podminky, Uhrady.Uhrada, 
  Uhrady.Cislo, Akce.DruhProst, Akce.Vyrizuje, Akce.PlanCena, Akce.Stav
HAVING ((Akce.Stav=1));

Obr. 1. Globální dotaz spojující data a vypočítávající součet

Problém vyřešíme rozdělením dotazu na dvě části. Na obr. 2 je zobrazen přípravný dotaz, který pouze spojuje zdrojová data. Jeho výsledkem je však poněkud překvapivě pouze 6 záznamů, odpovídajících 6 záznamům v tabulce "Čerpání".
Primární klíč
Účel vydání
Cislo
Plánovaná cena
Skutečně vyplacená částka v Kč
23Objednávky 3520007/97 10 004,00 Kč4 064,00 Kč
23Objednávky 3520007/97 10 004,00 Kč1 256,00 Kč
23Objednávky 3520007/97 10 004,00 Kč2 824,00 Kč
23Objednávky 3520007/97 10 004,00 Kč7 278,00 Kč
23Objednávky 3520007/97 10 004,00 Kč3 766,00 Kč
23Objednávky 3520007/97 10 004,00 Kč2 162,00 Kč

Výsledek vstoupí do dotazu, který provede výpočet součtu, viz obr. 3. Součet je pak samozřejmě správný:
Primární klíč
Účel vydání
Cislo
Plánovaná cena
Cerpano
23Objednávky 3520007/97 10 004,00 Kč21 350,00 Kč

Dosažení správného výsledku je sice potěšující, nicméně důvod nesprávného výpočtu dotazu z obr. 1 je tímto výsledkem značně zpochybněn. Pokud byl nesprávný globální dotaz, mělo by být nesprávné i druhé řešení. Vysvětlením je pouze průběh realizace dotazu a jeho optimalizace, které způsobují nesprávný výsledek.

SELECT DISTINCTROW Akce.ID, Akce.Ucel, IIf([Akce].[Ucel]="Vyslání" Or 
  [Akce].[Ucel]="Přijetí",[Akce].[Podminky],[Akce].[Cislo]) AS Cislo, 
  Akce.Datum, IIf([Akce].[Ucel]="Vyslání" Or [Akce].[Ucel]="Přijetí",
  [Akce].[Stat],[Dodavatele].[Nazev]) AS Nazev, Dodavatele.Specifikace, 
  Akce.Podminky, Uhrady.Uhrada, Uhrady.Cislo AS UhradaCislo, Akce.DruhProst, 
  Akce.Vyrizuje, Akce.PlanCena AS Planovano, Cerpani.Castka AS Cerpano
FROM (Uhrady RIGHT JOIN ((Dodavatele RIGHT JOIN Akce ON Dodavatele.ID = Akce.Dodavatel) 
  LEFT JOIN Cerpani ON Akce.ID = Cerpani.Akce) ON Uhrady.ID = Akce.Uhrada) 
  LEFT JOIN AkcePodklady ON Akce.ID = AkcePodklady.Akce
WHERE ((Akce.Stav=1));

Obr. 2. Dotaz spojující data
SELECT DISTINCTROW QAkceVyrizovanoPom.ID, QAkceVyrizovanoPom.Ucel, 
  QAkceVyrizovanoPom.Cislo, QAkceVyrizovanoPom.Datum, QAkceVyrizovanoPom.Nazev, 
  QAkceVyrizovanoPom.Specifikace, QAkceVyrizovanoPom.Podminky, QAkceVyrizovanoPom.Uhrada, 
  QAkceVyrizovanoPom.UhradaCislo, QAkceVyrizovanoPom.DruhProst, QAkceVyrizovanoPom.Vyrizuje, 
  QAkceVyrizovanoPom.Planovano, Sum(QAkceVyrizovanoPom.Cerpano) AS Cerpano
FROM QAkceVyrizovanoPom
GROUP BY QAkceVyrizovanoPom.ID, QAkceVyrizovanoPom.Ucel, QAkceVyrizovanoPom.Cislo, 
  QAkceVyrizovanoPom.Datum, QAkceVyrizovanoPom.Nazev, QAkceVyrizovanoPom.Specifikace, 
  QAkceVyrizovanoPom.Podminky, QAkceVyrizovanoPom.Uhrada, QAkceVyrizovanoPom.UhradaCislo, 
  QAkceVyrizovanoPom.DruhProst, QAkceVyrizovanoPom.Vyrizuje, QAkceVyrizovanoPom.Planovano;

Obr. 3. Dotaz vypočítávající součet dříve spojených dat

1.2. Průběh vyhodnocení dotazu

V některých okamžicích může být uživatel značně zaskočen chováním systému při vyhodnocování dotazů. Normální postup je následující:

  1. proběhne spojení zdrojových objektů (data jsou převedena do první normální formy) podle klauzule FROM,
  2. jsou vybrány požadované položky uvedené v klauzuli SELECT
  3. je provedena selekce záznamů podle výběrových podmínek WHERE,
  4. záznamy jsou seřazeny podle požadavku ORDER BY (max. 10 položek, nelze použít položky typu Memo a OLE).
  5. záznamy jsou seskupeny do skupin podle klauzule GROUP BY, pokud je použita,
  6. pokud je použita klauzule DISTINCT, DISTINCTROW nebo TOP, jsou vybrány odpovídající záznamy,
  7. pokud je použita klauzule HAVING jsou zobrazeny jen záznamy vyhovující zadané podmínce (až 40 výrazů spojených logickými operátory And a Or).

Otázkou je, kdy se provádí agregované výpočty a kdy výpočty virtuálních položek. K výpočtům by mělo dojít až po výběru požadovaných záznamů, tedy nejdříve po provedení bodu 3. To vysvětluje také chování dotazu vypočítávajícího průměrný věk osob v tabulce Adesar. Problém je komplikován skutečností, že k dispozici je pouze datum narození osoby v položce Spocti_Vek. Výpočet věku provádí funkce:

Function Spocti_vek(Dat As Variant) As Variant
' ************************************************************
' Vrátí aktuální věk spočtený ze zadaného datumu
' ************************************************************
If IsNull(Dat) Then
    Spocti_vek = Null
Else
    Dim V As Integer
    V = Year(Date) - Year(Dat)
    If Month(Dat) > Month(Date) Then
        V = V - 1
    Else
        If Month(Dat) = Month(Date) Then
            If Day(Dat) > Day(Date) Then
                V = V - 1
            End If
        End If
    End If
    Select Case V
        Case 1
            Spocti_vek = Str(V) & " rok"
        Case 2, 3, 4
            Spocti_vek = Str(V) & " roky"
        Case Else
            Spocti_vek = Str(V) & " let"
    End Select
End If
End Function

Funkce Spocti_Vek ale vrací věk včetně jednotky, např. "26 let". Pro výpočet průměrného věku převedeme výsledek funkce na číslo funkcí Val. Větší problém je v tom, že argument funkce je typu String (řetězec) a pokud bude položka DatumNarozeni prázdná (Null), bude hlášena chyba při předávání parametru funkci. Problém vyřešíme výběrem záznamů které nemají prázdné datum narození. Celý dotaz pak zní:

SELECT Avg(Val(Spocti_Vek([DatumNarozeni]))) 
FROM Adresy 
WHERE (((Adresy.DatumNarozeni) Is Not Null)))

Výsledkem dotazu bude skutečně průměrný věk všech osob v tabulce, kteří mají zadané datum narození.

Nyní vložíme výsledek do podmínky pro výběr osob, které mají vyšší věk než průměrný:

SELECT Adresy.*, Val(Spocti_Vek([DatumNarozeni])) AS Vek
FROM Adresy
WHERE (((Val(Spocti_Vek([DatumNarozeni])))>
      (SELECT Avg(Val(Spocti_Vek([DatumNarozeni]))) 
       FROM Adresy 
       WHERE (((Adresy.DatumNarozeni) Is Not Null)))) 
     AND ((Adresy.DatumNarozeni) Is Not Null));

Opět zde musíme počítat věk osob, proto také v hlavním dotazu vybíráme pouze osoby, které mají zadáno datum narození. Jinak by opět vznikala chyba při volání funkce Spocti_Vek, neboť by jí byly předávány ke zpracování všechny záznamy. Problém by v tom případě bylo možno řešit jedině úpravou položky DatumNarozeni funkcí Nz(), která převádí hodnotu Null na prázdný řetězec nebo hodnotu 0:

SELECT Adresy.*, Val(Spocti_Vek(Nz([DatumNarozeni]))) AS Vek
FROM Adresy
WHERE (((Val(Spocti_Vek(Nz([DatumNarozeni]))))>
      (SELECT Avg(Val(Spocti_Vek([DatumNarozeni]))) 
       FROM Adresy 
       WHERE (((Adresy.DatumNarozeni) Is Not Null))));

Obr. 4. Dotaz s vnořeným dotazem

Poslední otázkou, která se v souvislosti s řešeným problémem může objevit je proč jsme nepoužili agregovanou funkci DAvg("Val(Spocti_Vek([DatumNarozeni]))", "Adresy", "Not IsNull(DatumNarozeni)"), která poskytuje stejný výsledek. Důvodem je nižší rychlost provedení dotazu při jejím použití. Funkce ve své podstatě představuje dotaz, plán jeho provedení se ale musí vytvářet vždy znovu při použití funkce. Pokud pro určení průměrného věku použijeme dotaz, vytvoří se plán jeho realizace při uložení dotazu a současně s dotazem se uloží. Při dalším použití dotazu se tedy tento plán nemusí znovu vytvářet.

1.3. Příliš složitý dotaz

Již několikrát se stalo, že odladěný systém po nějaké době provozu náhle vypověděl poslušnost při vyhodnocení dotazu s hlášením viz obr. 5.

Obr. 5. Hlášení o příliš složitém dotazu

Důvod vzniku problému plně vysvětluje text nápovědy:
Can't perform join, group, or sort. Combined fields are too long.
The maximum combined length of fields used to join, group, or sort is 255 bytes. Reduce the number of fields in your join, group, or sort, or use shorter data types and field sizes for the fields.

Dotaz provádí agregovaný výpočet a součet velikostí obsahu položek, podle kterých se provádí seskupování záznamů je větší než 255 Byte. Je třeba buď ubrat počet položek pro seskupování nebo omezit jejich velikost např. funkcí Left([Jmeno], 50), protože problém způsobují především textové položky.

Bohužel se tato chyba projevuje obvykle až při provozu systému, protože v testovací fázi se používají jen cvičná data, která mají malý objem.