Laden...

Ermitteln eines NICHT vorhandenen Wertes mittels SELECT

Erstellt von Humsch vor 14 Jahren Letzter Beitrag vor 14 Jahren 7.051 Views
Humsch Themenstarter:in
56 Beiträge seit 2009
vor 14 Jahren
Ermitteln eines NICHT vorhandenen Wertes mittels SELECT

verwendetes Datenbanksystem: SQL-Express 2008

Hallo,

in einer Kundendatei mit etlichen 10000 Datensätzen sind die Kundennummern nicht fortlaufend vergeben.
Wie man die nächste oder die vorhergehende Nummer, ausgehend von der aktuellen, ermittelt, ist keine große
Sache. Allerdings möchte ich die vorhandenen Lücken auffüllen und benötige daher von der aktuellen Nummer
ausgehend die nächste nicht vergebene Nummer. Gelöst habe ich dies mit Hilfe eines Cursors, was auch tadellos funktioniert.
Wofür ich aber keine Lösungs finde: Wie realisiert man dies mittels SELECT, will sagen, wie wähle ich den nächsten nicht vorhandenen Datensatz?

Mit freundlichen Grüßen Humsch

5.657 Beiträge seit 2006
vor 14 Jahren

In Postgres gibt es die Funktion generate_series(), damit kann man sich alle Zahlen von 0 bis max(id) ausgeben lassen und über die vorhandenen Datensätze joinen. Damit bekommst du die nicht verwendeten Nummern heraus.

Der Haken ist, generate_series() gibt es nicht für MSSQL, d.h. du müßtest dir eine eigene Funktion dafür schreiben. Vielleicht hat aber noch jemand eine bessere Idee. Was spricht gegen die Variante mit dem Cursor?

Weeks of programming can save you hours of planning

X
1.177 Beiträge seit 2006
vor 14 Jahren

huhu,

http://www.sqlservercentral.com/articles/T-SQL/62867/

Benutze eine "Tally Table"

z.B:


declare @table table (i int not null, data varchar(50) not null)

insert into @table (i, data) 
values (1, 'a'),(2,'b'),(3,'c'),(5,'d')

select top 1 N from @table as missing
full outer join Tally as t on missing.i=t.n
where i is null

ergibt 4 -> erster nicht vokommender Wert. Wenn Du das Top 1 erhöhst, dann bekommst du auch mehr Werte.

Edit: @table ist deine Kundenliste

😃

Xynratron

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

343 Beiträge seit 2007
vor 14 Jahren

Hallo Humsch,

1.) Hoffe ich dass es einen guten Grund gibt warum du die Nummern auffüllen möchtest.

2.) so würds auch gehen


Select min(KundenID+1)
from Kunde
where KundenID+1 not in (Select KundenID from Kunde)

das Gute an diesem Code ist, wenn es keine freien Nummern mehr gibt, liefert dieser Teil einfach die nächste Nummer zurück.

Liebe Grüße
Philipp

[- www.saftware.net -](http://www.saftware.net/)
X
1.177 Beiträge seit 2006
vor 14 Jahren

huhu,

  
Select min(KundenID+1)  
from Kunde  
where KundenID+1 not in (Select KundenID from Kunde)  
  

bei mehreren 10000 Datensätzen wird das Not In aber ziemlich langsam.

😃

Xynratron

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

343 Beiträge seit 2007
vor 14 Jahren

Hab gerade einen Test gemacht mit 1.000.000 (eine Million) Datensätzen. Mein Statement braucht um Durchschnitt ca. 0,5 Sekunden.
Sollte das zu langsam sein gibts immer noch diese Möglichkeit:


Select top 1 (KundenID+1)
from Kunde
where KundenID+1 not in (Select KundenID from Kunde) 

Zeit: 0,008 Sekunden 😁

Weiß aber nicht sicher ob bei dieser Version sichergestellt ist, dass die Zahlen der Reihe nach kommen.

//edit: KundenID muss logischerweise Primärschlüssel sein oder sonst einen Index haben damits einigermaßen schnell funktioniert.

Liebe Grüße
Preli

[- www.saftware.net -](http://www.saftware.net/)
1.564 Beiträge seit 2007
vor 14 Jahren

Hi

Funktionen (worunter auch "Id + 1" zählt) sollten grundsätzlich in der WHERE Klausel vermieden werden. Da SQL Server keinen Index auf "Id + 1" hat muss er einen Full-Index-Scan durchführen. Daher auch die (sehr schlechte) Performance von 0,5 Sekunden bei 1.000.000 Datensätzen.

@Humsch:
Wenn du's jetzt einmalig benötigst ist das so okay. Wenn du das jedoch regelmäßig brauchst solltest du eine COMPUTED Column auf deiner Tabelle definieren welche "Id + 1" darstellt und diese indizieren.

@preli
Ich bevorzuge entweder einen LEFT JOIN welches fast sicher einen Inline-Cursor vermeidet oder NOT EXISTS da es einen Left Semi Join ausführt, also keine Daten von rechts anfasst.

Grüße
Flo

Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+

Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.

343 Beiträge seit 2007
vor 14 Jahren

@Florian
Ich habs jetzt mit einer Computed Column (Id+1) versucht. Performance bleibt ungefähr gleich.
Der Ausführungsplan sieht auch eigentlich gleich aus:

  • Hauptsächlich Left Anti Semi Join und Index Scan

Liebe Grüße
Preli

[- www.saftware.net -](http://www.saftware.net/)
X
1.177 Beiträge seit 2006
vor 14 Jahren

soo,

dafür hab ich jetzt die ursprüngliche Frage nochmal gelesen und mal nen DB-Server bemüht.

@Preli: überprüf doch mal die Werte welche du zurück bekommst. Die sind nämlich nur beim min() der erste Freie, aber garantiert erst der erste freie nach dem ersten belegten. (also ID 0 - 99 ist nicht vorhanden, 100 schon, dann gibt deine Query 101). Ausserdem ziehe ich meinen Einwand zum In hier erstmal zurück.

Davon abgesehen, man kanns ja abändern:


Select min(ID+1)
from Data
where ID+1 not in (Select ID from Data where ID>3633838)
GO

Select top 1 (ID+1)
from Data
where ID+1 not in (Select ID from Data where ID>3633838)
order by ID+1
GO

select min(n) from Data as j right outer join (select n from tally where n>3633838) as t 
on j.ID=t.n where ID is null
Go

select top 1 n from Data as j right outer join (select n from tally where n>3633838) as t 
on j.ID=t.n where ID is null
order by ID
Go

Testumgebung ist eine Produktive Tabelle mit 5.624.351 Einträgen IDs beginnen bei 3.633.838 und enden bei 9.523.796.

Ist also zwischendrin was frei. Die Ausgangsbasis war "von einer bekannten ID" aus, deswegen der Vergleich, die könnte ja auch höher oder niedriger liegen als der erste vorhandene Datensatz.

Ausführungszeiten - % im Execution-Plan
2507 ms - 39 %
0001 ms - 17 %
1442 ms - 43 %
0001 ms - 00 %

(die 0% liegen vermutlich an nicht angezeigten Nachkommastellen)

Conclusion: Wenn man die ersten freien ignorieren kann, dann bietet sich evtl. Prelis Lösung 2 an (aber das sortieren nicht vergessen!) - wenn man auch die ersten haben möchte, dann braucht man eine andere Lösung, z.B: eine Sequenz-Table.

😃

Xynratron

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

343 Beiträge seit 2007
vor 14 Jahren

sooo,

@Xynratron schau doch bitte nochmal genau meinen Nicknamen an (ich bin immerhin kein Kalender 🙁 )

  1. muss ich dir recht geben, an das hab ich nicht gedacht. Meine Lösung bringt natürlich nur den ersten freien ab dem ersten Benutzer zurück X(

Aber so würd's gehen:


Select top 1 (KundenID+1)
from V_Kunde
where KundenID+1 not in (Select KundenID from Kunde)
order by KundenID

V_Kunde ist eine View die wie folgt aussieht:


SELECT KundenID
FROM Kunde
UNION SELECT 0

Liebe Grüße
Preli

[- www.saftware.net -](http://www.saftware.net/)
Humsch Themenstarter:in
56 Beiträge seit 2009
vor 14 Jahren

Hallo,

vielen Dank an alle für die schnellen Antworten.
@preli
Der Grund liegt beim GF. Der will auf seinen Listen fortlaufende Nummern sehen. Das ist alles.
Deine Lösung hatte ich tatsächlich schon auf'm Schmierzettel stehen und ohne nähere Betrachtung verworfen.
Das ID+1 hat mich irritiert. Das lag an der von mir vorab festgelegten Vorgehensweise. Ich wollte erst alle Lücken im Kundennummernfeld ermitteln und dann die nächste zu Verwendenden raussuchen. Für Lücken, in denen mehr als eine Nummer fehlt, ist die +1-Variante dann nicht brauchbar. Für min(KundenID+1) klappt es aber wunderbar. 😃

@Florian
Wenn man ID+1 in der WHERE-Klausel aus performancegründen vermeiden sollte, dann kann ich doch gleich meinen Cursor weiterbenutzen. Es wird vermutlich nicht übermäßig oft benutzt werden. Mal wieder 'ne Ergänzung für'n Eimer.


 @"DECLARE @iCursorNr INT  = @iStartWert
   SET     @iKundenNr 	   = @iStartWert
   DECLARE KndCursor CURSOR FOR
	SELECT DISTINCT D1.KndNr
	FROM tblKunden D1
	WHERE D1.KndNr > @iStartWert
	FOR READ ONLY
   OPEN KndCursor
   WHILE(@iKundenNr = @iCursorNr)
   BEGIN
	SET @iKundenNr = @iKundenNr + 1
	FETCH Next
	FROM KndCursor
	INTO @iCursorNr
   END
   CLOSE KndCursor
   DEALLOCATE KndCursor";

Der Startwert ist Benutzereingabe -1.

Der Cursor hat den Vorteil, dass er mir, nach überschaubarer Ergänzung, eine Liste ALLER fehlenden Nummern liefern könnte. Was spräche gegen die Verwendung des Cursors?

Mit freundlichen Grüßen Humsch

X
1.177 Beiträge seit 2006
vor 14 Jahren

huhu,

@preli: Ohhh, Sorry, habs korrigiert. Das war ein Freudscher Fehler - liegt an meiner Arbeit - kommt nicht mehr vor^^

@Humsch: Wenn Du weiterhin alle freien willst, dann nimm die 4. Lösung aus meinem Post, ohne TOP.

Zu Cursor bzw. Schleifen (die ja trozdem funktionieren) gibts hier http://www.sqlservercentral.com/articles/T-SQL/62867/ einen sehr schönen Artikel.

😃

Xynratron

Herr, schmeiss Hirn vom Himmel - Autsch!

Die Erfahrung zeigt immer wieder, dass viele Probleme sich in Luft auslösen, wenn man sich den nötigen Abstand bzw. Schlaf gönnt.

1.564 Beiträge seit 2007
vor 14 Jahren

Servus

@preli
Sorry, unabhängig vom eventuell nicht 100% richtigen Ergebnis hatte ich dein Statement falsch gelesen. Der von mir angesprochene Inline-Cursor entsteht sobald du werte von außen in die Sub-Query hinein gibst:

SELECT * 
FROM Foo f1
WHERE f1.Id IN (
   SELECT f2.Id
   FROM Foo f2
   -- !!! HIER !!!
   WHERE f2.Id = f1.Id + 1)

An der Stelle entscheidet sich SQL Server normalerweise die Sache als Cursor zu verarbeiten.

Deswegen mein Hinweis auf NOT EXISTS:

SELECT * 
FROM Foo f1
WHERE NOT EXISTS (
   SELECT *
   FROM Foo f2
   -- !!! HIER !!!
   WHERE f2.Id = f1.Id + 1)

Sieht gleich aus, verhält sich aber komplett anders. Hier werde die Daten eben gejoint, wobei ein Semi-Join verwendet wird da man ja nicht wirklich Daten aus f2 zurückliefert sondern nur ihre Existenz prüft. Das ist übrigens auch bei deinem IN-Statement ohne Parameterübergabe an die Sub-Query passiert. ((Witzig, hatte ich noch nicht gewusst das SQL Server das jetzt so auflöst. Bilde mir ein dass das bei SS2k noch anders war.)

Zu dem Execution-Plan mit Id+1 und einer zusätzlichen Computed Spalte muss ich sagen dass ich dein Ergebnis nicht ganz verstehe. Ich habe zwar (warum auch immer, habe ich jetzt nicht weiter erforscht) auch bei beiden Wegen einen Index-Scan, nichtsdestotrotz ist der Weg über die Computed Column bei mir ca. 3-4 mal schneller.

@Humsch
Cursor oder mengenbasiert - wurscht? Versuch's doch mal 😉. Selbst wenn die hier gezeigten mengenbasierten Lösungen einen Index-Scan verursachen (und unterwegs noch Weihnachtsgeschenke kaufen) bleiben sie um ein vielfaches schneller alle jeder Cursor (auch wenn du einen Firehouse Cursor verwendest).

@Xynratron:
LOL! Wusste gar nicht dass du auch eine Tally-Table verwendest (heißt bei mir mittlerweile Numbers wegen häufigen Verwirrungen). Auch dass du auf Jeff's Artikel verweist finde ich witzig. Ist menschlich, fachlich ein super Typ! Ich kann die anderen Artikel von ihm übrigens auch wärmstens empfehlen. 😃

Grüße
Flo

Edit: Typo

Blog: Things about Software Architecture, .NET development and SQL Server
Twitter
Google+

Je mehr ich weiß, desto mehr weiß ich was ich noch nicht weiß.