perfomant und sauber eine datenbank mit ~500mb und 4318402 einträgen auf 1 doppelte spalte prüfen und nur doppelte ausgeben

Triple-Axe · 6. Juni 2011, 00:28

wie der titel schon sagt habe ich mal wieder ärger

ich hab hier local ne datenbank die ich nicht weiter fülllen will bevor ich nicht die altlasten die ich durch das einspielen 2er backups habe los bin
konkret geht es dabei um eine spalte (ben) die u.U. doppelte werte enthält

gespeichert sind nur 30 stellige zahlen ohne sonderzeichen oder buchstaben

mein problem oder frage ist nun, wie bekomme ich die sauber und vorallem performant so bearbeitet, das ich alle doppelten angezeigt ( nicht gelöscht ) bekomme sodass ich selber entscheiden kann welche von beiden ich später löschen will

v1

SQL-Abfrage

select *,count(*) as menge from ben group by HASH order by menge desc

v2

SQL-Abfrage

select *,count(*) as menge from ben group by HASH order by menge desc LIMIT 0, 20

ist zwar ganz ok aber braucht massiv resourcen auf meinem server daher die frage ob die anweisung noch irgendwie performanter gemacht
werden könnte ?

edit die v2 sollte ja nur max 19-21 zeilen ausgeben mal schauen ...

edit : fixed

wie :

die komplette db gebackupt in 50 mb stücken
dann die tabelle geleert
nun der spalte ben ein uniq verpasst
nun die komplette db wieder eingespielt mit "fehler ignorieren"

mal schauen was in 20 min rauskommt *G

Rinecamo · 6. Juni 2011, 05:54

Ich glaub da lässt sich nichts weiter verbessern, da die Auslastung ja nicht direkt wegen dem Befehl ist sondern eher wegen der Datei.
Du könntest vll. versuchen die Datei zu splitten, und immer nur kleine Teile zu analysieren.

fichz · 6. Juni 2011, 09:39

Du willst aus 1 Spalte die doppelten Werte anzeigen lassen?
Dann würde ich nicht mit * arbeiten.
Wähle die Spalten aus die du angezeigt bekommen willst aber nicht alles (kA wieviele Columns du hast).

Ist "ben" nun eine Tabelle oder Spalte? (oben steht Spalte aber in der Abfrage ist eine Tabelle)

lg

raist10 · 6. Juni 2011, 10:32

Hier mal eine mögliche Performance-Verbesserung Deiner Abfrage:

[MySQL]
SELECT NameSpalte, Hash, count(NameSpalte) AS menge FROM ben GROUP BY Hash WHERE menge > 1[/MySQL]

NameSpalte steht natürlich die Spalte die Du durchsuchen willst.

Das '*' bei Count zu verwenden verlangsamt die Abfrage und ein Order By bremst die Abfrage auch (bis zu 50%). Durch die WHERE-Einschränkung nur auf die DS die wirklich mehr als einmal vorhanden sind wird der Ressourcenverbrauch geschont weil nicht alle DS zum Ergebnis gehören.

Nur einen Feldnamen auszugeben reduziert auch den Ressourcenverbrauch und erhöht die Performance.

So wäre jetzt mal zumindest mein Ansatz die Performance zu erhöhen und den Ressourcen-Verbrauch zu senken.

Kannst es ja mal ausprobieren und berichten ob das damit erreicht wird.

Gruß

Rainer

picoflop · 6. Juni 2011, 10:39

raist10 schrieb:

Das '*' bei Count zu verwenden verlangsamt die Abfrage

Vermutlich eher umgekehrt. MW heißt das für normale SQL-Dialekte soviel wie: Anzahl aller Zeilen. Und es wird NICHT nach jeder Spalte oder EINER SPEZIELLEN Spalte gesucht.

zb
stackoverflow.com/questions/457263/should-i-count-or-not

Also, some database systems, like MySQL employ an optimization when you ask for count(*) which makes such queries a bit faster than the specific one.

Triple-Axe · 6. Juni 2011, 10:40

ja sry dir tabelle heißt ben ( is n test für ne queersummenbildung )

ich teste das gerade das dauert aber wieder 20 min *G ich editier meinen post und berichte

edit :

@picoflop was ist mw ? (wildcard aber m ? )

picoflop · 6. Juni 2011, 10:44

mw = Meines Wissens ...

Triple-Axe · 6. Juni 2011, 10:47

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM ben GROUP BY Hash

bringt mir nach 10 min ergebnisse

aber :

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM ben GROUP BY Hash WHERE menge > 1

bringt n fehler

mom die db is bisl überlastet *G kann den fehler nich posten

fichz · 6. Juni 2011, 11:54

picoflop schrieb:

Vermutlich eher umgekehrt. MW heißt das für normale SQL-Dialekte soviel wie: Anzahl aller Zeilen. Und es wird NICHT nach jeder Spalte oder EINER SPEZIELLEN Spalte gesucht.

Mit * werden auch NULL-Values mitgerechnet was mit count(SPALTE) vermieden wird. Also wird die Abfrage mit * langsamer laufen.
Kommt halt darauf an wieviele NULL-Values man hat und ob man diese angezeigt haben will oder nicht.

lg

picoflop · 6. Juni 2011, 12:00

fichz schrieb:

Also wird die Abfrage mit * langsamer laufen.

Nö
Genau wegen dieser Argumentation ist sie sicherlich schneller.

-> Wieviele Zeilen eine Tabelle enthält dürfte der Server wissen, ohne die Tabelle anzuschauen. Das steht üblicherweise in irgendwelchen Headern etc. Diese nachzuschauende Zahl liest er aus und feddich.

Wenn er alle Zeilen lesen soll, die NICHT Null sind, muss er das ja prüfen. Prüfen dauert üblicherweise länger als auslesen ohne prüfen.

EDIT MySQL zb:
mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

fichz · 6. Juni 2011, 12:13

Intressehalber hab ich das eben getestet.
SQL-Server 2008 R2 und einer Tabelle mit knapp 100k Einträgen.

Resultat: kein Unterschied.
Wenn man die Abfragen einmal mit * und einmal ohne macht dauert einmal diese und dann wieder die andere länger (im Zentelbereich).

Wie es aber bei 4318402 Einträgen wie beim TE aussieht kann ich nicht sagen.

lg

Triple-Axe · 6. Juni 2011, 12:15

also meine db habe ich nun in eine neue kopiert die tabelle heißt "rohdaten" und beinhaltet 15345 zeilen oder datensätze

in hash enthält eine 30 stellige zahlen ohne sonderzeichen oder buchstaben die aber leider doppelt ist

SQL-Abfrage

SELECT Hash,count(Hash) FROM rohdaten GROUP BY Hash

gibt mir 15345 von 15345 datensätzen aus

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash WHERE menge > 1

MySQL meldet:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE menge > 1 LIMIT 0, 30' at line 1

Fehler bei der Anfrage:
SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash WHERE menge > 1 LIMIT 0, 30;

die spalte ist auf typ uniq nun eingestellt was aber bei den alten daten nicht hilft

@ fichz wie misst du die laufzeit innerhalb von sql ohne einen php parser ? bzw ein php script ( kein webserver auf dem db server)

fichz · 6. Juni 2011, 12:23

Eventuell kann er den Alias menge nicht auflösen.
Probier hierzu

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash WHERE (count(Hash)) > 1 LIMIT 0, 30;

Hab erst jetzt gesehen, dass du MySql nutzt.
Im Management Studio von MSSQL-Server (nicht MySQL) kann man sich genau anschauen was bei einem SELECT so passiert. Unter anderem auch die Laufzeit der Abfrage.

lg

picoflop · 6. Juni 2011, 12:29

Triple-Axe schrieb:

@ fichz wie misst du die laufzeit innerhalb von sql ohne einen php parser ? bzw ein php script ( kein webserver auf dem db server)

Schreib vor dein Select statement mal EXPLAIN
Also EXPLAIN SELECT COUNT(*) FROM Tabelle

Triple-Axe · 6. Juni 2011, 12:51

ich habe nun das mich aufregte *G die tabelle verkleinert damit es richtig fix ist

es sind nur 6 einträge

2 x ( 30x die 1 )
2 x ( 30x die 3 )
und 2 die anders sind zu allen

111111111112221111111111111111
333333333333333333331234567890

nun habe ich mal was krankes gebaut

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash ORDER BY Hash

das klappt auch

aber das hier

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash ORDER BY Hash WHERE (hash not NULL OR hash not "")

damit ich nur die hash spalten bekomme wo nicht ein NULL drin ist ( nix ) die bekomm ich nicht gefiltert ( brauch ich das überhaupt ?)

ich weis im moment gibt es keine leeren aber die dicke db hat da ein "paar" xD die filter ich aber später anders

fehler :

Spoiler anzeigen

@ picoflop : EXPLAIN werd ich mir ansehen wenn

klappt danke dir

fichz · 6. Juni 2011, 12:54

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash ORDER BY Hash WHERE (hash IS NOT NULL OR hash <> "") LIMIT 0, 30;

lg

Triple-Axe · 6. Juni 2011, 12:59

ne leider nicht

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash ORDER BY Hash WHERE (hash IS NOT NULL OR hash <> "") LIMIT 0, 30;

Spoiler anzeigen

und mein umbau

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash ORDER BY Hash WHERE hash IS NOT NULL OR hash <> "" LIMIT 0, 30

Spoiler anzeigen

und

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM rohdaten GROUP BY Hash ORDER BY Hash WHERE hash <> ""

Spoiler anzeigen

alle 3 bringen den where fehler

ich geh nun einkaufen brb

edit : ich habe eine spalte drin in hash nun die leer ist aber irgnoriert sie und gibt sie bei : aus

SQL-Abfrage

SELECT * FROM `rohdaten` WHERE (hash IS NOT NULL OR hash <> "") LIMIT 0, 30

fichz · 6. Juni 2011, 15:18

in SQL ansich werden ja Strings mit Hochkomma (') abgefragt. Probier das mal.

lg

Triple-Axe · 6. Juni 2011, 16:20

alte inhalt :

Spoiler anzeigen

B A M

is habs nach x fehlermeldungen und x³ webseiten und google endlich geschafft

erklärung ( einfach gehalten @ trolle ) :

so sieht die datenbank aus ( den unwichtigen teil rausgeschnitten)

wenn man nun das hier abfragt :

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM `rohdaten` GROUP BY Hash

kommt eine tabelle raus, in welcher die doppelten einträge in der spalte menge einen wert >1 haben
oder anders : wenn da 1 steht gibt es dein eintrag 1x, wenn 2 dann 2x usw

so weiter geht es

nun haben ja die probleme angefangen : wie zum xxxxxxxxxxxxxxxxxx bekommt man die spalten NICHT
angezeit in denen nur eine 1 steht ?

nun wir haben ja gelernt das die menge >1 ist wenn mehr als 1 eintrag in der spalte hash ist. also mal prüfen

SQL-Abfrage

SELECT * FROM (SELECT Hash,count(Hash) AS menge FROM `rohdaten` GROUP BY Hash) AS t2 WHERE menge > 1

erklärung :

SQL-Abfrage

SELECT Hash,count(Hash) AS menge FROM `rohdaten` GROUP BY Hash

uninteresannt kennen wir ja schon. aber was treibt er denn da ? :

SQL-Abfrage

SELECT * FROM ( ) AS t2 WHERE menge > 1

als pseudocode :

wähle alles von( ....... ausgabe des klammerinhalt gespeichert in: ) t2 wovon die mengenspalte größer als 1 ist

grün ist kommentar

SQL-Abfrage

SELECT * FROM (SELECT Hash,count(Hash) AS menge FROM `rohdaten` GROUP BY Hash) AS t2 WHERE menge > 1

einmal laufen lassen :

geilo also sieht es doch schon toll aus

ErfinderDesRades · 6. Juni 2011, 18:38

solltest noch im Where - Abschnitt angeben: menge > 1, sodass du wirklich nur Gruppen kriegst mit mehr als einem Item drin.
Dürfte die Performance auch erheblich steigern, weil das die Datenmenge sehr reduziert.

Ach shit - das schrieb raist ja schon hier.

perfomant und sauber eine datenbank mit ~500mb und 4318402 einträgen auf 1 doppelte spalte prüfen und nur doppelte ausgeben

perfomant und sauber eine datenbank mit ~500mb und 4318402 einträgen auf 1 doppelte spalte prüfen und nur doppelte ausgeben

SQL-Abfrage

SQL-Abfrage

raist10 schrieb:

SQL-Abfrage

SQL-Abfrage

picoflop schrieb:

fichz schrieb:

SQL-Abfrage

SQL-Abfrage

Triple-Axe schrieb:

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

SQL-Abfrage

Ähnliche Themen

4 Benutzer haben hier geschrieben