SQL: Query performanter machen

SQL: Query performanter machen (29 Beiträge, 315 Mal gelesen)

Du bist nicht angemeldet. [ Login/Registrieren ]

Hallo ... ich brauch eine kleine Abfrage, allerdings läuft sie nicht mit der Performance, die ich gerne hätte!

select count(*)
from tabelle
where aktiv=1 --0 u. 1 erlaubt
and blabla=1 --0 u. 1 erlaubt
and uniqueid not in (160.000 IDs aufgelistet)

najo ingesamt umfasst die Tabelle 9.5 Mio. IDs ... jetzt hab ich die Abfrage nach 2 Stunden abgebrochen, weils ma einfach zblöd geworden ist!

habt ihr eine Idee, wie ich die Abfrage performanter gestalten könnte?

in erster Linie reicht mir das Ergebnis mit count, in zweiter Linie bräucht ich natürlich die unique id's als Ergebnis!

edit

Schande über mein Haupt, die ID ist ja gar nicht unique, sondern wird es nur durch die Einschränkung aktiv=1

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

16.06.2008, 12:39 Uhr - Editiert von -Transformer2K-, alte Version: hier

So wie es da steht: Mehr RAM

Ein Kreuzprodukt über eine derartige Anzahl von Feldern ist krank

Woher kommen denn die 160k IDs? Wie lange dauert die Anfrage ohne das "Where not in .." ?

Wenn der Sturm losbricht,
verstummen die Einen vor Schrecken,
und die Anderen breiten, einem Adler gleich, die Flügel aus
und schwingen sich empor.

Dieser Beitrag bezieht sich auf eine ältere Version des beantworteten Postings!

So wie es da steht: Mehr RAM

noch mehr? der Server hat eh schon 10GB

Ein Kreuzprodukt über eine derartige Anzahl von Feldern ist krank

das steht außer Frage, aber die Anforderung kommt vom Verkauf und ich muss es halt irgendwie lösen!

Woher kommen denn die 160k IDs?

ist ebenfalls eine Anforderung, dass diese IDs exkludiert werden!

Wie lange dauert die Anfrage ohne das "Where not in .." ?

1 Sekunde

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

Kanns sein dass der Optimizer mit dem Where not in nicht gut umgehen kann? Könntest da evtl. wirklich das Kreuzprodukt machen?
Sprich ein
select count(*)
from tabelle1, tabelle2
where
  tabelle1.id != tabelle2.id and
  tabelle1.irgendwas = x and
  tabelle2.irgendwo = y

oder geht das mit dem != nicht? Sprich: Funktioniert die Verbundbedingung so nicht?

Wenn der Sturm losbricht,
verstummen die Einen vor Schrecken,
und die Anderen breiten, einem Adler gleich, die Flügel aus
und schwingen sich empor.

schau ich mir nach dem Essen an

edit

du meinst also, ich solls einfach mit einem Inner Join probieren?

edit 2

hm ich glaub, das macht wenig Sinn, da ich ja die Tabellen irgendwie joinen muss und ob ich jetzt die 160.000 IDs mit einem Join exkludiere oder nicht, macht ja im Endeffekt keinen Unterschied!

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

16.06.2008, 12:49 Uhr - Editiert von -Transformer2K-, alte Version: hier

Im Prinzip ein umgekehrtes Inner Join, ja ...

Es kommt halt drauf an was der DB Optimizer macht, vielleicht findet er nur keine brauchbare Lösung.

Wenn der Sturm losbricht,
verstummen die Einen vor Schrecken,
und die Anderen breiten, einem Adler gleich, die Flügel aus
und schwingen sich empor.

ich hab ja die 160.000 IDs sogar schon index-like sortiert, damit er nicht herumsuchen muss, aber trotzdem hatte ich nach 2 Stunden noch kein Ergebnis

glaubst macht es einen Unterschied, wenn ich die 160.000 IDs in eine temporäre Tabelle importiere und diese dann per Subquery exkludiere?

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

glaubst macht es einen Unterschied, wenn ich die 160.000 IDs in eine temporäre Tabelle importiere und diese dann per Subquery exkludiere?

auch ohne ms sql zu kennen, gehe ich stark davon aus dass das (sehr viel) besser ist, wenn die (temp) tabelle indexiert ist - fuer ein RDBMS ist es das taegliche brot, joins und selects zu optimieren.

join ist vermutlich am besten, subquery mit exists (select 1 from ... where ...) wird vermutlich auch einges besser sein als in (select id from ...).

http://forum.geizhals.at/t576679,4858671.html#4858671

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

Also schön langsam nervts wenn man immer fragen muss, um welche Datenbank es sich überhaupt handelt. Poste mal was der Planner sagt, wenn du ein explain analyze machst. Wenn die DB das kann.
--
DiTech postete:
Sie haben offensichtlich 7mal mit dem Goldadler positive Erfahrung gemacht. Bei dieser Menge an Glück sollten Sie Lotto spielen.

GHF Watcher 1.3 - Firefox Erweiterung für Geizhals User

16.06.2008, 12:52 Uhr - Editiert von Dr. Watson, alte Version: hier

Also schön langsam nervts wenn man immer fragen muss, um welche Datenbank es sich überhaupt handelt.

sorry, bin auch nur ein Mensch und deshalb nicht unfehlbar!

btw. es ist eine MS SQL2005-DB!

Poste mal was der Planner sagt, wenn du ein explain analyze machst.

läuft grad ... ich schreib dann das Ergebnis, sofern es in einer angemessen Zeit kommt!

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

Kein Problem, danke.

Naja, wenn das Query selbst schon 2 Stunden gelaufen ist, war das mit dem analyze doch keine so gute Idee. Normales Explain sollte erst mal reichen.

Sags aber gleich dazu, MS-SQL ist nicht mein Gebiet, also bevor du deine Arbeitszeit verschwendest um mir ein explain zu posten...
--
DiTech postete:
Sie haben offensichtlich 7mal mit dem Goldadler positive Erfahrung gemacht. Bei dieser Menge an Glück sollten Sie Lotto spielen.

GHF Watcher 1.3 - Firefox Erweiterung für Geizhals User

Naja, wenn das Query selbst schon 2 Stunden gelaufen ist, war das mit dem analyze doch keine so gute Idee. Normales Explain sollte erst mal reichen.

ich habs jetzt nach 7 Minuten abgebrochen - scheint also auch ned wirklich weiter zu helfen - trotzdem danke!

hm ich glaub ja nicht, dass es ein Hardware-Problem ist (DB-Server hat ca. 12 GHz und RAM haben wir auch 10GB zugewiesen ... also muss es wohl an der Abfrage liegen ... aber ich hab echt keinen Plan, wie ich so eine - eh schon einfache - Abfrage einfacher gestalten könnte!

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

Naja, "einfach" ist gut - wegen deinem Count muss die DB sowieso physikalisch die komplette Tabelle (9,5 Millionen Zeilen lt. deinen angaben) durchwetzen, und das dauert nun mal seine Zeit.

Ich sehe das Problem ganz einfach in der Planung - den Fall "einzelne mega tabelle" und "abteilung x braucht spezielle daten" hätte man sich vorher überlegen müssen.

Für solche Fälle (extrem viele Zeilen) hätte ich (unter PostgreSQL) Tabellen Partitionierung verwendet, die Tabellen damit nach Monaten (oder sogar weniger, je nach Menge) aufgeteilt, und jeder zeile ein created Datum gegeben, auf dieses einen Index setzen und diesen clustern.

Hat dann nicht nur den Vorteil, dass die abfragen extrem schnell sind, weil du keine mega-großen indizes hast, sondern auch die Wartung ist schneller, weil eben nicht bei jedem update / insert / delete ein mega-großer index aktualisiert werden muss.

Kann jetzt natürlich nur raten, aber diese 160.000 Ids müssen doch auch irgendwo her kommen - gibt es für die z.b. ein zeitliches Kriterium, das du von deinem "Auftraggeber" erfragen kannst? Dann könntest du auch auf das eingrenzen, was die performance sicher drastisch verbessern würde.
--
DiTech postete:
Sie haben offensichtlich 7mal mit dem Goldadler positive Erfahrung gemacht. Bei dieser Menge an Glück sollten Sie Lotto spielen.

GHF Watcher 1.3 - Firefox Erweiterung für Geizhals User

16.06.2008, 13:15 Uhr - Editiert von Dr. Watson, alte Version: hier

Naja, "einfach" ist gut

war auch ironisch gemeint! edit vom Aufbau her ist die Query aber trotzdem sehr einfach! ich musste auch schon Abfragen für den Verkauf machen, die unzählige Joins und Subqueries drin hatten - die sind dann aber auch über 30min auf unserem neuen System gelaufen

Ich sehe das Problem ganz einfach in der Planung - den Fall "einzelne mega tabelle" und "abteilung x braucht spezielle daten" hätte man sich vorher überlegen müssen.

stimmt - aber mit der Datenmenge hat man vor ca. 1 Jahr nicht gerechnet

Für solche Fälle (extrem viele Zeilen) hätte ich ... Tabellen Partitionierung verwendet

ja ich bin grad dabei, mit meinem Vorgesetzten zu klären, ob wir nicht ein paar Tabellen zu partitioned views ummodellieren!

gibt es für die z.b. ein zeitliches Kriterium, das du von deinem "Auftraggeber" erfragen kannst

na leider nicht und mir fällt auch kein Kriterium ein, auf das ich es eingrenzen könnte ...

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

16.06.2008, 14:00 Uhr - Editiert von -Transformer2K-, alte Version: hier

ich musste auch schon Abfragen für den Verkauf machen, die unzählige Joins und
Subqueries drin hatten - die sind dann aber auch über 30min auf unserem neuen
System gelaufen

Und in dem Fall kannst das Query nicht einfach über Nacht laufen lassen? Klingt ja nicht so, als würde das Ergebnis sich oft ändern, oder ständig benötigt wird.
--
DiTech postete:
Sie haben offensichtlich 7mal mit dem Goldadler positive Erfahrung gemacht. Bei dieser Menge an Glück sollten Sie Lotto spielen.

GHF Watcher 1.3 - Firefox Erweiterung für Geizhals User

Und in dem Fall kannst das Query nicht einfach über Nacht laufen lassen?

hm mir wird wohl eh nix anderes übrig bleiben! allerdings wollt ich da schon den Tuning-Advisor mit einer 2GB-Profiler-Datei beschäftigen ... hm das Backup sollte auch noch irgendwann laufen ... najo dem Server is am Abend jo eh immer so fad

Klingt ja nicht so, als würde das Ergebnis sich oft ändern, oder ständig benötigt wird.

stimmt - ein einziges Ergebnis mit den IDs würde mir schon reichen!

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

am besten, du schreibst die 160k Id's in eine temp. Tabelle, legst einen Index drauf und fragst mit "exists" ab.
Das sollte die Abfrage auf max. ein paar Minuten beschränken.

so ich hab die IDs importiert, ein Subquery aus der Abfrage gemacht und innerhalb von 11 Sekunden

ein Ergebnis erhalten

thx an alle, die sich bemüht haben, mir zu helfen!

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

es wird sich ja keiner hingesetzt haben und die 160.000 IDs ausgewählt haben ..

sondern dies nach irgendeiner "logik" gemacht haben

diese sollte man halt nachbauen

(hätte auch den vorteil, dass die 3 IDs die versehentlich in der liste mit den 160000 drinnen sind, und nicht rein gehören würden auch gleich weg wären

)

(wennst die tabelle ändern darfst, könntest natürlich ein zusätzliches feld "istin160000" machen...)

jo diese Logik zählt zu den 30min-Abfragen

ansonsten http://forum.geizhals.at/t576679,4858671.html#4858671

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

select count(*)
from tabelle
where aktiv=1 --0 u. 1 erlaubt
and blabla=1 --0 u. 1 erlaubt
and uniqueid not in (160.000 IDs aufgelistet)

Solch eine Abfrage (query) muss langsam sein, denn fuer jede Zeile (row) in der Resultatsmenge (result set) wird die "AND uniqueid NOT IN (value-list)" Suchbedingeung (search-condition) neu ausgeführt.

Falls Ihr SQL WITH im SELECT statement unterstützt:

WITH myIDs AS (SELECT distinct myIDIdentifier
               FROM myIDTable
              )

select count(*)
from tabelle LEFT EXCEPTION JOIN myIDs
   ON tabelle.uniqueid = myIDs.myIDIdentifier
where         aktiv = 1 --0 u. 1 erlaubt
      and     blabla=1 --0 u. 1 erlaubt

Gibt Ihnen alle Element von tabelle zurück die
1.) aktiv = 1 haben
2.) blabla = 1 haben
3.) nicht in myIDs sind

mfG

Solch eine Abfrage (query) muss langsam sein, denn fuer jede Zeile (row) in der Resultatsmenge (result set) wird die "AND uniqueid NOT IN (value-list)" Suchbedingeung (search-condition) neu ausgeführt.

hm so stehts auch in dem Buch über SQL-Tuning, nur leider war mir bis vorhin keine andere Möglichkeit bekannt, diese Daten abzufragen!

ansonsten http://forum.geizhals.at/t576679,4858671.html#4858671

also nochmals danke!

my car @ Spritmonitor.de

Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved

Dieses Forum ist eine frei zugängliche Diskussionsplattform.
Der Betreiber übernimmt keine Verantwortung für den Inhalt der Beiträge und behält sich das Recht vor, Beiträge mit rechtswidrigem oder anstößigem Inhalt zu löschen.
Datenschutzerklärung