interner Abfragenoptimierer

29/08/2008 - 12:36 von Thomas Winkler | Report spam
Hi,

habe mal eine Frage zur Funktionsweise des internen Abfrageoptimierers.

Mir ist schon klar, dass versucht wird, beim Ausführen einer Query-X aus
den Verknüpfungen der Basis-Queries A und B ein möglichst performanter
Plan für die Abarbeitung zu erstellen. Meiner Meinung nach hat dieser
"Optimierer" einen massiven Bug.

Szenario:
Für Importe werden 2 Tabellen (nennen wir sie Tabelle1 und Tabelle2 -
Tabelle2 ist eine Teilmenge der Tabelle1) temporàr in eine Anwendung
verknüpft. Weil die Daten potenziell fehlerhaft sein könnten, werden
verschiedene Gültigkeitsprüfungen und Korrekturen vorgeneommen. Das
geschieht für jede Tabelle einzeln in den Abfragen Abfrage1 und
Abfrage2. Damit stehen jetzt für die weitere Verarbeitung korrekte und
gültige Daten zur Verfügung.

Der Primàrschlüssel beider Tabellen ist Text, wird aber in Abfrage1 und
2 mittels einer Funktion F() zu einem Long gemacht.

Nun soll Abfrage3 eine LeftJoin zwischen Abfrage1 und Abfrage2
berechnen. Das führt zur Anzeige von "Fehler#" in Werten aus Abfrage2,
die nicht in Abfrage1 enthalten sind.

Nach einiger Suche habe ich auch die Ursache gefunden - nàmlich die
Konvertierungsfunktion, die die Text-PKs in Long-PKs umwandelt. Diese
war nicht darauf ausgelegt, Null-Werte zu behandeln (weil diese auch
rein logisch und praktisch nicht in den Basistabellen vorkommen können).

Da der Join aber ausdrücklich über F(ID) gemacht wurde hàtte ich
erwartet, dass das ganze funktioniert. Offensichtlich optimiert der
"Optimierer" jedoch das F() weg, Joint dann, und führt F() auf die
gejointe Menge aus. Dort sind dann logischerweise Nulls dabei, woraufhin
F() wieder streikt.

Habe mal eine Demo (A2k3) bereitgestellt, die das ganze veranschaulicht.
http://rapidshare.de/files/40346765/db1.zip.html

Habe schon probiert F() As Variant zu definieren, was das ursprüngliche
Problem auch behebt, allerdings scheitern dann weitere Joins (wegen
Datentypenkonflikten) mit weiteren internen Tabellen, da dort die PKs
wieder Longs sind.

Ist das ein Bug, oder bin ich mit falschen Erwartungen an die Geschichte
rangegangen? Gibt's Workarounds?

Thomas

"Access? Damit arbeite ich nicht. Das ist doch nur ein abgespecktes Excel."
 

Lesen sie die antworten

#1 Josef Poetzl
29/08/2008 - 13:18 | Warnen spam
Hallo!

Thomas Winkler schrieb:
Mir ist schon klar, dass versucht wird, beim Ausführen einer Query-X aus
den Verknüpfungen der Basis-Queries A und B ein möglichst performanter
Plan für die Abarbeitung zu erstellen. Meiner Meinung nach hat dieser
"Optimierer" einen massiven Bug.



vielleicht streikt er ganz einfach, weil der dieses Join für sinnlos
betrachtet. :-))

[...]
Habe mal eine Demo (A2k3) bereitgestellt, die das ganze veranschaulicht.
http://rapidshare.de/files/40346765/db1.zip.html




Kurz zusammengefasst, damit nicht jeder nachsehen muss:

Abfrage-Prinzip:
SELECT A1.ID, A2.ID
FROM
(
SELECT CLng(Tabelle1.ID) AS ID
FROM Tabelle1
) A1
LEFT JOIN
(
SELECT CLng(Tabelle2.ID) AS ID
FROM Tabelle2
) A2
ON A1.ID¢.ID;


Tabelle1.ID und Tabelle2.ID sind Textfelder
In Tabelle2 sind nicht für jeden DS aus Tabelle1 DS enthalten.
=> Ergebnis der SQL-Anweisung im Datenblattansicht:

| A1.ID A2.ID
| 1 #Fehler
| 10 #Fehler
| 2 #Fehler
| 3 3
| 4 #Fehler
| 5 5
| 6 #Fehler
| 7 #Fehler
| 8 #Fehler
| 9 9

... also #Fehler statt keinem Eintrag (NULL)

Habe schon probiert F() As Variant zu definieren, was das ursprüngliche
Problem auch behebt, allerdings scheitern dann weitere Joins (wegen
Datentypenkonflikten) mit weiteren internen Tabellen, da dort die PKs
wieder Longs sind.

Ist das ein Bug, oder bin ich mit falschen Erwartungen an die Geschichte
rangegangen?



Ausgelöst wird er Fehler durch clng(NULL), weil der Select-Teil immer
erst am Ende der SQL-Anweisungen berechnet wird.
Auch wenn du mehrere Abfragen verwendest, werdend diese nicht wie
Tabellen behandelt.
Die Frage ist nun: wo liegt der Bug bzw. wo beginnt die Fehlerursache?
;-)

Gibt's Workarounds?



1. Umgehung:
distict in Abfrage1 und Abfrage2 einfügen. => das schaltet meistens
die Abfrageoptimierung aus.

Showplan ohne Distinct:
01) Outer Join table 'Tabelle1' to table 'Tabelle2'
using X-Prod join
then test expression "CLng(Tabelle1.ID)=CLng(Tabelle2.ID)"

Showplan mit Distinct:
store result in temporary table
store result in temporary table
01) Sort result of '00)'
02) Outer Join result of '00)' to result of '01)'
using temporary index
join expression "Abfrage1.ID=Abfrage2.ID"


2. Umgehung:
SELECT Clng(T1.ID) AS ID1, T2.ID AS ID2
FROM Tabelle1 T1 LEFT JOIN Tabelle2 T2 ON clng(T1.ID)=clng(T2.ID)


=> 1 oder 2: viel Spaß bei mehreren DS . ;-)


3. Umgehung:
Eine "Verknüpfungstabelle nutzen, um ein Zahlenfeld für
Folge-Verknüpfungen zu erhalten.

select
X.Z as IdzumVerknuepfenMitLongWerten,
T1.ID,
T2.ID
from
(
Tabelle1 T1
LEFT JOIN
Tabelle2 T2
ON T2.ID = T1.ID
)
LEFT JOIN
tabZahlen X
ON X.T = T1.ID


mfg
Josef

EPT: (Access Error Prevention Table) http://access.joposol.com/
FAQ: (Access-FAQ von Karl Donaubauer) http://www.donkarl.com/

Ähnliche fragen