SQL-Abfrage schneller machen?

[Audi]

Hallo,

ich habe ein Problem mit der Geschwindigkeit einer SQL-Datenbank-Abfrage in MS Access, die Abfrage selbst funktioniert nur brauch sie ca. 10sec bis sich da etwas tut, allein schon bei SELECT/FROM :schlaf:

SELECT DISTINCT ARTMENGE.ArtMengeNr, SARTIKEL.ArtName1, SARTIKEL.ArtName2, SARTIKEL.ArtZusInfo1, BESTDOK.BestDocName1, ARTLIEF.ArtLiefBestellNr, ARTLIEF.ArtLiefEKPreis, SARTIKEL.ArtZusInfo2, SARTIKEL.ArtZusInfo4, SARTIKEL.ArtMatchcode

FROM ((ARTMENGE LEFT JOIN ARTLIEF ON ARTMENGE.ArtMengeNr = ARTLIEF.ArtLiefArtNr) LEFT JOIN BESTDOK ON ARTLIEF.ArtLiefLiefNr = BESTDOK.BestDocLiefNr) LEFT JOIN SARTIKEL ON ARTMENGE.ArtMengeNr = SARTIKEL.ArtNr;

Wie und was muss ich man verbessern um sie flotter zu machen?

Hoffe jemand kann mir helfen

[flashpixx]

Ohne dass ich die DB kenne, würde ich einfach mal sagen, dass Access an seine Grenzen stößt, so dass Du wohl einen ausgewachsenen DBMS Server verwenden solltest

[Marshal]

schon index regeln erstellt?

MySQL :: MySQL 5.1 Referenzhandbuch :: 7.4.3 Spaltenindizes

[_n4p_]

hier mal indizes für Access: Access 2002: 4.1.2.1 Beispiel 81: Ein Feld mit einem Index versehen

sollte so ähnlich auch mit neueren versionen funktionieren.

indizies speziell für die felder die an den joins beteiligt sind.

alternativ, wie flaschpixx schon sagte, über ein größeres DBMS nachdenken.

[Audi]

hier mal indizes für Access: Access 2002: 4.1.2.1 Beispiel 81: Ein Feld mit einem Index versehen

sollte so ähnlich auch mit neueren versionen funktionieren.

indizies speziell für die felder die an den joins beteiligt sind.

alternativ, wie flaschpixx schon sagte, über ein größeres DBMS nachdenken.

Danke soweit!

Habe das größte Problem entdeckt, es ist die Funktion "DISTINCT", wenn ich diese weglasse, dann findet er mir in 1sec. knapp ne Mio. Datensätze, aber diese sind natürlich 20-30fach vorhanden

Dann habe ich noch eine für mich unbrauchbare Tabelle gelöscht, jetzt habe ich 3.

Dass mit dem indizieren funktioniert nur bei einer Tabelle, bei der anderen ist es nicht möglich, da die Werte nicht eindeutig sind.

Jetzt fehlt mir nur noch ein Schritt zur Vollendung!

Wenn Artikel mehrfach vorhanden sind, soll es den Ersten den es findet nehmen, welche Funktion ist dafür zuständig? Wie kann man dass am besten realisieren?

Soll ich zur besseren Verständniss kurz mein DB-Modell beschreiben?

[_n4p_]

1) die indizes müssen nicht eindeutig sein, dafür gibts ja die option "Duplikate möglich"

2) eventuell kannst du das DISTINCT durch geschicktes gruppieren ersetzen, ob das schneller wird musst du testen

3) inwieweit kann ein artikel mehrfach vorhanden sein? in der SARTIKEL? in der ARTMENGE?

[Audi]

1) die indizes müssen nicht eindeutig sein, dafür gibts ja die option "Duplikate möglich"

2) eventuell kannst du das DISTINCT durch geschicktes gruppieren ersetzen, ob das schneller wird musst du testen

3) inwieweit kann ein artikel mehrfach vorhanden sein? in der SARTIKEL? in der ARTMENGE?

zu 3:

Ich habe die Tabelle ARTMENGE in der Abfrage nicht berücksichtigt (ist die gelöschte Tabelle, von der ich gesprochen habe). Die war mit ARTLIEF (Artikel Lieferant) und SARTIKEL (Artikel) nur wegen der ArtNr in SARTIKEL bzw. ARTLIEF verbunden, also weg damit

Jetzt habe ich nur diese Tabellen mit den dazugehörigen Attributen:

Tabelle ARTLIEF: ArtLiefArtNr (Artikelnummer)(habe ich als PK), ArtLiefBestNr (Bestellnummer), ArtLieftEKPreis (Einkaufspreis), ArtLiefLiefNr (LieferantNr)

Tabelle SARTIKEL: ArtNr (Art)(habe ich als PK), ArtName1 (Bauteilname), ArtName2 (Bauteilparameter), ArtZusInfo1 (Beschreibung), ArtZusInfo2 (Hersteller), ArtZusInfo4 (Hersteller BestellNr.), ArtMatchcode (Lagerort)

Tabelle BESTDOK: BestDocLiefNr (LieferantNr), BestDocName1 (Lieferantenname)

SARTIKEL.ArtNr ist mit ARTLIEF.ArtLiefArtNr verbunden

ARTLIEF.ArtLiefLiefNr ist mit BESTDOK.BestDocLiefNr verbunden

Hier noch der SQL-Code dazu:

SELECT art.ArtLiefArtNr, sart.ArtName1, sart.ArtName2, sart.ArtZusInfo2, art.ArtLiefBestellNr, sart.ArtZusInfo4, art.ArtLiefEKPreis, sart.ArtMatchcode, BESTDOKK.BestDocName1

FROM BESTDOKK RIGHT JOIN (SARTIKELL AS sart INNER JOIN ARTLIEFF AS art ON sart.ArtNr = art.ArtLiefArtNr) ON BESTDOKK.BestDocLiefNr = art.ArtLiefLiefNr

Bearbeitet 21. Mai 2010 von Audi

[dr.dimitri]

Mich würde mal interessieren, wozu der OP lauter Outer Joins macht. Da in der WHERE Bedingung nicht auf NULL abgefragt wird, und auch im Ergebnis keine NULL Werte sein können, da ansonstend er DISTINCT nicht eindampfen kann (NULL immer <> NULL) vermute ich mal, dass ein (deutlich schnellerer) Inner Join das gleiche Ergebnis liefern würde.

Ob ich Group By oder Distinct verwende ist egal - die Datenbank muss die Ergebnismenge immer sortieren.

Des weiteren wäre noch eine Definition von "flotter machen" wünschenswert. Millionen von Datensätzen durchzunudel dauert eben seine Zeit. Entweder ich schränke die zu durchsuchenden Sätze ein oder ich gebe der DB mehr Ressourcen, damit sie die Arbeit schneller erledigen kann - nachdem ich das SQL optimiert habe.

Dim

[Audi]

Mich würde mal interessieren, wozu der OP lauter Outer Joins macht. Da in der WHERE Bedingung nicht auf NULL abgefragt wird, und auch im Ergebnis keine NULL Werte sein können, da ansonstend er DISTINCT nicht eindampfen kann (NULL immer <> NULL) vermute ich mal, dass ein (deutlich schnellerer) Inner Join das gleiche Ergebnis liefern würde.

Ob ich Group By oder Distinct verwende ist egal - die Datenbank muss die Ergebnismenge immer sortieren.

Des weiteren wäre noch eine Definition von "flotter machen" wünschenswert. Millionen von Datensätzen durchzunudel dauert eben seine Zeit. Entweder ich schränke die zu durchsuchenden Sätze ein oder ich gebe der DB mehr Ressourcen, damit sie die Arbeit schneller erledigen kann - nachdem ich das SQL optimiert habe.

Dim

Ja genau das ist es ja, wir haben nur 4417 Artikel in der DB und die sollte auch Access schnell zustande bringen können.

Wenn ich DISTINCT verwende bekomme ich 4417 Datensätze, wenn nicht sind es über 600 Tausend und genau da liegt das Problem, bis der die verglichen hat vergeht viel Zeit :schlaf:

Wie ist es eigentlich mit z.B Google die Suchmaschine gibt ja ratz fatz mal so paar Mio. Ergebnisse in 1Sec. raus...?

Bearbeitet 21. Mai 2010 von Audi

[dr.dimitri]

Und wieso verwendest Du jetzt Outer Joins?

Dim

[flashpixx]

Wie ist es eigentlich mit z.B Google die Suchmaschine gibt ja ratz fatz mal so paar Mio. Ergebnisse in 1Sec. raus...?

Naja das ist ein riesiges Thema: Einmal ist die arbeitet Google mit sinnvollen Indizierungsmechanismen, die gut indizieren und die Informationen passend aufbereiten bzw eine passende Datenreduktion, dann steht hinter Google nicht eine Datenbank, sondern ein Datenbank Cluster, der über mehrere Rechenzentren über der Welt verteilt sind. Ausgeklügeltes Load Balacing der einzelen Knoten usw trägt dazu noch viel bei.

[lilith2k3]

Nur so nebenbei... Google hat ca. 1 Mio Server weltweit am Netz ... das da irgendwo ein bisschen power über ist, kann man sich vorstellen

[Audi]

Habe da eine neue Idee weiß aber nicht wie ich es realisieren soll, bzw. was es für ein Befehl ist.

Ist Zustand:

Ein Artikel hat X Lieferanten

Soll Zustand:

Beim ersten auftreten in der DB soll es in der Ausgabe vorkommen.

Bearbeitet 31. Mai 2010 von Audi

[RipperFox]

@Audi:

Deine Ist/Soll-Analyse ist für mich unverständlich

Wie wäre es wenn Du uns in einfachen Worten beschreiben würdest was Du vor hast?

Du möchtest irgendwelche Bestelllisten ausgeben?

Grüße

Ripper

[Audi]

@Audi:

Deine Ist/Soll-Analyse ist für mich unverständlich

Wie wäre es wenn Du uns in einfachen Worten beschreiben würdest was Du vor hast?

Du möchtest irgendwelche Bestelllisten ausgeben?

Grüße

Ripper

Ja so in der Art, ist eigentlich nur eine zusammengestellte Artikelliste, für Mitarbeiter. In dieser werden dann bestimmte Artikel die man z.B zur Hardwareerstellung braucht gesucht. (Das ganze funktioniert über eine Benutzeroberfläche die in C# programmiert wurde, die greift auf eine Access DB zu)

Das Programm sowie auch die Suche nach Artikeln funktioniert auch singemäss, nur ist die Suche langsam wie sau...das ist auch das eigentliche Problem.

[Audi]

Konnte die Suche heute durch Gruppieren um 3sec. beschleunigen, es bleiben allerdings noch 8-9sec. Wartezeit.

Die Funktion DISTINCT ist jetzt nicht mehr notwendig

[dr.dimitri]

Und ich stelle meine Frage zum dritten mal:

Benötigst Du wirklich OUTER JOINS (oder LEFT JOINs wie man sie auch nennt).

Dim

[Audi]

Und ich stelle meine Frage zum dritten mal:

Benötigst Du wirklich OUTER JOINS (oder LEFT JOINs wie man sie auch nennt).

Dim

Nein nicht unbedingt, wenn es einfacher (schneller) geht, bin ich ganz ohr.

[RipperFox]

Da wäre die Frage nach den OUTER JOINS und:

- Welche Spalten benötigst Du wirklich?

- Indizes angelegt?

- Liegt die Datenbank auf einer Netzfreigabe?

- Ist die Abfrage in Access selbst so lahm, oder bremst es erst bei Einbindung in deine C# App?

Mehr Infomationen wären schon nicht schlecht..

Grüße

Ripper

[Audi]

Da wäre die Frage nach den OUTER JOINS und:

- Welche Spalten benötigst Du wirklich?

- Indizes angelegt?

- Liegt die Datenbank auf einer Netzfreigabe?

- Ist die Abfrage in Access selbst so lahm, oder bremst es erst bei Einbindung in deine C# App?

Mehr Infomationen wären schon nicht schlecht..

Grüße

Ripper

zu 1: Habe natürlich nur Spalten genommen die ich wirklich brauche, die wurden in voherigen posts veröffentlicht, aber wenn trotzdem Fragen sind...fragen.

zu 2: Versucht, aber nicht geschaft (evt. könnte man da nochmal anfangen, brauche aber Hilfe)

zu3: Ja

zu4: Ich experementiere nur in Access selbst, das Programm bleibt aussen vor, denn es bekommt nur den SQL-Code als String übergeben, bremst auch kaum bzw. nicht der Rede wert.

zu den OUTER JOINS, die Abfragen sind von mir so wie ich es selbst konnte erstellt worden, Tabellen waren natürlich schon vorhanden.

[RipperFox]

zu 1: Habe natürlich nur Spalten genommen die ich wirklich brauche, die wurden in voherigen posts veröffentlicht, aber wenn trotzdem Fragen sind...fragen.

Naja, ich habe keine Ahnung, welche Tabellen Du hast (bzw. was da genau drin steht) und was für eine Ergebnisliste Du haben willst.

Das ist aus Deiner (evtl. ungünstigen oder gar falschen) SQL-Abfrage nicht direkt ersichtlich.

Beispiel für eine meiner Meinung nach halbwegs korrekte Fragestellung:

Ich habe die Tabellen 'Artikel' und 'Stückliste':

Artikel:

-------

ID

Name

Bezeichnung

Stückliste:

-------

AuftragsNr

Position

ArtikelID

Notiz

In der Stückliste gibt es die Spalte 'ArtikelID', welches auf den Primärschlüssel 'ID' in der Tabelle Artikel verweist.

Ich möchte nun eine Liste mit den benötigten Artikeln bei AuftragsNr = x, sortiert nach der 'Position' auf der Stückliste:

Bsp:

Artikel.Position, Artikel.Name

1 Gänseblümchen

Meine Abfrage sieht bisher aus...

zu 2: Versucht, aber nicht geschaft (evt. könnte man da nochmal anfangen, brauche aber Hilfe)

Ein Index hilft beim Durchsuchen von großen Listen.

Z.B. muss die Tabelle 'ART' für Deine JOINS nach passenden 'ArtLiefArtNr'-Einträgen durchkämmt werden - jede Zeile wird eingelesen (sowas nennt man SEEK).

Du machst es dem DBMS einfacher, in dem Du also einen Index über diese Spalte anlegst (damit wird quasi eine sortierte Liste erstellt, bei dem das DMBS weiß, an welcher Stelle ein Eintrag zu finden ist)

Bsp: Telefonbuch - dort sind die Einträge nach Namen sortiert. Jetzt ist es nicht gerade einfach, eine Rückwärtssuche zu machen (Nummer gegeben, Name gesucht), da man alle Einträge durchgehen müsste.

Folglich legt man einen Index über die Nummern an: Die Datenbank speichert die Nummern sortiert mit Hinweis auf die Zeile im Telefonbuch.

Ich spare mir also Suchzeit (muss allerdings Platz für den Index aufbringen und den Index auch immer mal wieder aktualisieren - das macht das DBMS allerdings normalerweise automatisch)

zu3: Ja

Dir ist klar, dass Access im Netzbetrieb nicht unbedingt der Performanceweltmeister ist? Je nach Datenbankgröße und Anzahl zugreifender Stationen werden die Zugriffe schnell recht lahm.

Grade Seeks (s.o.) fressen viel Performance.

Läuft das mit lokaler Datenbasis schneller?

Evtl. mal an ein anderes DBMS gedacht?

zu den OUTER JOINS, die Abfragen sind von mir so wie ich es selbst konnte erstellt worden, Tabellen waren natürlich schon vorhanden.

Wenn man die Outer weghaben will, sollten wir wissen WAS Du genau für eine ergebnisliste brauchst (s.o.) - vieleicht einfach mal als Text ausformulieren, statt mit SQL.

Grüße

Ripper

[dr.dimitri]

Wenn Du die OUTER JOINs nicht brauchst weg damit und schreib anstelle von LEFT JOIN einfach INNER JOIN.

Des weiteren lege mal prophylaktisch jeweils einen Index auf jedes Feld über das Du joinst (also 5 Stück wenn ich mich nicht verzählt habe).

Des weiteren solltest Du dir die DB mal auf den lokalen rechner holen und prüfen, wie groß die Auswirkung der Netzwerkverbindung auf die Abfragezeit ist.

Dim

[Audi]

Danke sehr!!!

brauche jetzt erstmal Zeit um die ganzen Anmerkungen durch zu gehen

[Audi]

Naja, ich habe keine Ahnung, welche Tabellen Du hast (bzw. was da genau drin steht) und was für eine Ergebnisliste Du haben willst.

Ich habe 3 Tabellen die ich benutze:

ARTLIEF

SARTIKEL

BESTDOK

Aus diesen Tabellen brauche ich folgende Infos/Spalten:

Artikelnummer

Bauteilname

Bauteilparameter

Beschreibung

Hersteller

Bestellnummer

Herrsteller Bestellnummer

Lieferantname

Einkaufspreis

Lagerort

Diese Spalten befinden sich in folgenden Tabellen:

Tabelle ARTLIEF:

ArtLiefArtNr (Artikelnummer)

ArtLiefBestNr (Bestellnummer)

ArtLieftEKPreis (Einkaufspreis)

ArtLiefLiefNr (LieferantNr)

Tabelle SARTIKEL:

ArtNr (Artikelnummer)(habe ich als PK)

ArtName1 (Bauteilname)

ArtName2 (Bauteilparameter)

ArtZusInfo1 (Beschreibung)

ArtZusInfo2 (Hersteller)

ArtZusInfo4 (Hersteller BestellNr.)

ArtMatchcode (Lagerort)

Tabelle BESTDOK:

BestDocLiefNr (LieferantNr)

BestDocName1 (Lieferantenname)

So habe ich die Tabellen miteinander verknüpft:

SARTIKEL.ArtNr (Artikelnummer) ist mit ARTLIEF.ArtLiefArtNr (Artikelnummer) verbunden

ARTLIEF.ArtLiefLiefNr (LieferantNr) ist mit BESTDOK.BestDocLiefNr (LieferantNr) verbunden

Hoffe es ist jetzt besser verständlich:-)

Bearbeitet 31. Mai 2010 von Audi

[RipperFox]

Ok, warst fleissig..

Aber das war fast _zu_ ausführlich, aber nicht aussagekräftig genug

Ich sehe wie deine Tabellen aufgebaut sind, weiss allerdings immer noch nicht, wie deine Ergebnismenge aussehen soll.

(Was soll die Liste nachher liefern? Nicht die Spalten sondern der "Zweck" wäre interessant - Bsp: "Liste von Artikeln je Stückliste, gruppiert nach Stückliste")

Ebensowenig weiß ich, was ARTLIEF, SARTIKEL und BESTDOK denn genau heissen soll!?

SARTIKEL = Stücklistenartikel?

BESTDOK = Lieferant nehm ich an?

ARTLIEF = Artikelstamm??

Da würde mir spontan einfallen: WO steht die ID einer Stückliste?

Natürlich kann ein Artikel von mehreren Lieferanten geliefert werden und wiederum in mehreren Stücklisten benutzt werden.

Ich denke, deswegen hattest Du das DISTINCT benutzt.

Aber das Ganze als Liste runterlaufen zu lassen ist ja an sich unsinnig..(nochmal: Zweck deiner Abfrage ist zumindest mir unklar)

Grüße

Ripper