SQL Wie "WHERE ... IN" benutzen

[Squicky]

hallo

Ich habe zwei Tabellen: tab1 und tab2
Beide Tabellen haben zwei spalten: nachname, vorname.
Nachname und Vorname sind einzel nicht eindeutig. Aber zusammen sind sie eindeutig.

Nun nöchte möchte alle Nachnamen und Vornamen, die in tab1 aber nicht in tab2 sind.

Wie kann man dies am performantesten machen?

Select * from tab1 where tab1.nachanme + '-' + tab1.vorname not in (select tab2.nachanme + '-' + tab2.vorname from tab2)
ist sehr sehr langsam.

Danke

P.S. Es wird Microsoft SQL 2008 benutzt.

 

[FrAGgi]

http://www.w3schools.com/sql/

 

[Zeroflow]

wie wäre es mit einem JOIN ? da sollte das leicht machbar sein

alternativ falls es noch möglich ist die Datenbankstruktur zu ändern: für eine eindeutige user id ein, das is einmal arbeit, macht aber dann alles viel einfacher

 

[Tafelmolch]

Versuche es mal mit einem Join und USING (da du ja über 2 Spalten joinen musst). Das sollte eigentlich etwas schneller gehen.

EDIT: Zeroflow war schneller

 

[tm0975]

sowas macht man mit einem outer join nach dem motto

select a.*
from a
left outer join b on bedingung
where b.id is null

es werden ale in a angezeigt, die nicht in b sind. ist performant und gut lesbar.

nahtrag: grunsätzlich sind name + vormane nicht eindeutig, es bedarf hier einer id.

 

[Squicky]

@ all:

Eine id oder key gibt es nicht. Wenn ich eine id hätte, dann wäre dies für mich nicht ganz so schwer.
Wie kann man dies mit nachname und vorname machen???

 

[Yuri_Orlov]

Zeige, ohne SUBSELECT, jene ROWS, welche NICHT in tabelle2 sind:

SELECT CONCAT(a.name,'-',a.vorname) AS set1 FROM tabelle1 a 
LEFT JOIN tabelle2 b 
ON a.name = b.name AND a.vorname = b.vorname 
WHERE b.name IS NULL AND b.vorname IS NULL

 

[Azdak]

select * from tab1 a where not exists (select * from tab2 b where a.name+'-'+a.vorname = b.name+'-'+b.vorname)

Klingt vieleicht etwas "sprechender", der interne Optimierer von MSSql müsste aber das gleiche daraus machen wie aus dem Statement von Yuri_Orlov.

Bei deinem Statement kann ich mir gut vorstellen, dass für jede Zeile erst das komplette Subselect aufgebaut wird und dann geprüft wird. Das dauert dann natürlich...
Dennoch ist für das Verständniss - insbesondere der Laufzeit - allerdings klar Yuri_Orlovs Statement zu empfehlen. Stichwort nested loop vs. merge join.
Schau dir doch einfach mal den Ablaufplan an wenn du bei einer Abfrage Performanceprobleme hast.

Das "not in" eignet sich eher für z.B.
select * from tab1 a where name+'-'+vorname not in ('dieter-heinz', 'gustav-karl', 'mueller-hanz')

 

[BerniG]

Soweit ich weiß sind Konstruktionen mit "NOT" wenn möglich zu vermeiden da sie oft langsamer sind. Zudem ist dein not-exists-Beispiel auch etwas ungünstig weil durch das Konkatenieren der Strings vermutlich kein Index verwendet werden kann.
Der Optimizer kann das Konkatenieren NICHT auflösen und in das SQL von Yuri_Orlov überführen da es nicht dasselbe ist (es könnte sein dass a.name+'-'+a.vorname = b.name+'-'+b.vorname aber a.name <> b.name).

 

[Azdak]

Oh, stimmt!
'Heinz-Otto','Mueller' in tab1 und 'Heinz','Otto-Mueller' in tab2 würde er garnicht ausgeben.
In die Where-Klausel muss a.vorname=b.vorname and a.name=b.name

 

[Rossibaer]

@Squicky: Du hast geschrieben das du keine ID in beiden Tabellen hast, die du dann verwenden kannst. Aber du kannst doch auch nachträglich eine ID einführen, auf diese einen Index legen und dann immer performant deine Abfragen machen. Der einzige Aufwand wo es etwas länger dauern wird, wäre wenn du die ID einführst und entsprechend nachführst. Das wäre jedoch nur ein einmaliger Aufwand der dann bei späteren Abfragen nicht mehr aufgebracht werden muss und du so auch noch einen Performanceschub bekommen würdest. Das würde sich sicher lohnen, wenn deine Abfrage sehr häufig ausgeführt wird. Wenn du diesen Weg gehen möchtest, kann ich dir sicher schnell ein Wartungsscript generieren, das den ganzen Kram erledigt.

 

[Squicky]

@Rossibaer

Danke für deine Hilfe/Angebot.
Leider kommen aber sehr oft sehr viele Daten hinzu; und nach diesem Import wird dieser select nur ein mal gebraucht. So dass das Erzeugen einer ID das Problem verschieben, aber nicht lösen würde.

Ich werde den ganzen Programmablauf (inkl. Datenbanken) anpassen müssen.

Danke für eure Hilfe und eure Tipps.

 

[Rossibaer]

@Squicky: Mir kam noch folgende Idee. Und zwar könntest du in beiden Tabellen jeweils eine "computed" Column hinzufügen die das Konkatieren von Vorname und Name übernimmt. Diese computed column musst du als PERSISTED markieren und kannst im Anschluß dann auf diese Spalte einen Index legen. Somit könntest du die Abfragen beschleunigen. Statt in der abfrage dann das Konkat zu machen, verwendest du jeweils die entsprechende computed column.

siehe http://msdn.microsoft.com/en-us/library/ms191250.aspx

Persisted bedeutet lediglich dass die Werte der Spalte nicht beim SELECT neu berechnet werden, sondern bei Änderung von mind. 1 beteiligten Spalte während einem INSERT oder UPDATE.

 

[BerniG]

Die von Yuri_Orlov gepostete Lösung ist in der Regel optimal. Evtl. könnte man probieren ob es mit folgendem Statement schneller ist aber ich denke eher nicht:

SELECT name, vorname FROM tabelle1
EXCEPT 
SELECT name, vorname  FROM tabelle2

Bzgl. der Konkatenieridee: Wenn man einen Index auf Name und Vorname legt, kommt man performancetechnisch beim Select auf praktisch dasselbe wie wenn man die Felder vorher konkateniert (ist zumindest in DB2 so und ich vermute, dass Microsoft hier ebenso tickt). Zudem wird durch das Konkatenieren der Import verlangsamt was im gegebenen Szenario wohl durchaus relevant ist.

Anzumerken ist auch, dass der Index unter Umständen gar nichts bringt (auch er verlangsamt im Übrigen den Import). Er würde in diesem Szenario nur etwas bringen wenn die Tabelle abgesehen von den Indexspalten noch weitaus mehr Spalten hat. Es müssen ja schließlich letztlich soundso alle Sätze aus beiden Tabellen gelesen werden und das weiß auch der Optimizer. Ein kompletter Tablescan ist an sich schneller als ein Indexscan (in der Regel wird das durch einen leaf scan durchgeführt) wenn sowieso alle Sätze gelesen werden müssen (da weniger CPU-Last benötigt wird) und darum wählt der Optimizer diesen dann auch aus. Wenn es nun aber sehr große Sätze sind (also viele Spalten), so dass der Tablescan sehr viel I/O benötigen würde, so kehrt sich das irgendwann um und dann ist der Indexzugriff besser.