SQL: Query performanter machen

Re(2): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 13:32:22 GMT

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!

Re(2): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 13:28:19 GMT

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

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

Re(8): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 13:27:44 GMT

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

Re(2): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 13:27:32 GMT

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!

Re: SQL: Query performanter machen

Alois_de_Saster — Mon, 16 Jun 2008 13:21:26 GMT

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

Re: SQL: Query performanter machen

adhoc — Mon, 16 Jun 2008 13:08:51 GMT

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

Re(7): SQL: Query performanter machen

japh — Mon, 16 Jun 2008 12:41:14 GMT

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

Re: SQL: Query performanter machen

chaka — Mon, 16 Jun 2008 12:37:12 GMT
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.

Re(8): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 12:08:11 GMT

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!

Re(7): SQL: Query performanter machen

Dr. Watson — Mon, 16 Jun 2008 12:04:02 GMT

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.

Re(6): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 11:46:46 GMT

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

Re(6): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 11:46:46 GMT

Naja, "einfach" ist gut

war auch ironisch gemeint!

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

Re(5): SQL: Query performanter machen

Dr. Watson — Mon, 16 Jun 2008 11:15:36 GMT
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.

Re(5): SQL: Query performanter machen

Dr. Watson — Mon, 16 Jun 2008 11:15:36 GMT
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 (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.

Re(4): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 11:03:43 GMT

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!

Re(6): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 11:01:12 GMT
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?

Re(3): SQL: Query performanter machen

Dr. Watson — Mon, 16 Jun 2008 10:59:17 GMT
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...

Re(2): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 10:55:42 GMT

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!

Re(5): SQL: Query performanter machen

Somnatic — Mon, 16 Jun 2008 10:54:06 GMT
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.

Re: SQL: Query performanter machen

Dr. Watson — Mon, 16 Jun 2008 10:50:54 GMT
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.

Re: SQL: Query performanter machen

Dr. Watson — Mon, 16 Jun 2008 10:50:54 GMT
Also schön langsam nervts wenn man immer fragen muss, um welche Datenbank es sich überhaupt handelt.

Re(4): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 10:39:22 GMT
schau ich mir nach dem Essen an

Re(4): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 10:39:22 GMT
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!

Re(4): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 10:39:22 GMT
schau ich mir nach dem Essen an

edit

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

Re(3): SQL: Query performanter machen

Somnatic — Mon, 16 Jun 2008 10:34:47 GMT
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?

Re(2): SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 10:15:47 GMT

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

Re: SQL: Query performanter machen

Somnatic — Mon, 16 Jun 2008 10:07:34 GMT
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 .." ?

SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 09:45:36 GMT
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

SQL: Query performanter machen

-Transformer2K- — Mon, 16 Jun 2008 09:45:36 GMT
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!