Zeile mehrfach ausgeben

[Brodi87]

hallo,

habe Artikel und einen Mengenwert:

Art Menge

xx 2

yy 3

nun möchte ich jeden Artikel jeweils so oft ausgeben, wie die Menge ist

sprich:

Art Menge

xx 2

xx 2

yy 3

yy 3

yy 3

mfg

[Reinhold]

Moin,

ich wollte gerade reflexartig nach dem DBMS fragen, aber das dürfte hier keine Rolle spielen. Mit SQL-Bordmitteln geht das IMHO nicht.

Folglich musst du eine wie auch immer geartete und in welcher Sprache auch immer geschriebene Prozedur dafür häkeln.

... jetzt wäre es doch wieder interessant, womit du da arbeitest ...

Reinhold, das Ohrenwunder

Bearbeitet 2. Dezember 2010 von Reinhold

[Brodi87]

sorry ganz vergessen.

MS SQL 2005

[Reinhold]

Verrate uns Unwissenden doch einfach worum es geht? Willst du eine Art Seriennummernerfassung oä machen? Mir erschließt sich der Sinn des Ganzen nicht.

[Brodi87]

der Sinn wird wahrscheinlich auch nicht so einfach verständlich sein. Ich erklärs mal grob:

Wir wollen Etiketten drucken, die auf Artikel geklebt werden. Wenn also nun 100 Artikel mit einer bestimmten Seriennummer (z.B. '1') im Wareneingang erscheinen. Dann sollen 100 Aufkleber mit Seriennummer '1' darauf gedruckt werden.

Nun fragt sich bestimmt jemand, wieso der Trottel denn nicht einfach am Drucker die Druckanzahl ändert. Das Problem ist einfach, dass zum Teil 100 verschiedene Artikel mit einmal rein kommen...das sind 100 verschiedene Seriennummern mit verschiedenen Mengen. Also müsste man 100 mal manuell die Druckanzahl wählen. In unserer Lagersoftware gibt es eine SQL-Schnittstelle die mir ermöglicht solche Skripte zu verwenden und dann am Drucker auszugeben. Aus diesem Grund möchte ich den ganzen Ablauf vereinfachen.

Das eigentliche Skript ist weitaus komplexer, aber mir hilft es schon die Lösung für das "einfache" Problem zu finden.

[streffin]

Mhm ....

schön aussehen wirds nich .....

Ich meld mich in ner Stunde nochma, ich bastel da mal n bissl, des intressiert mich jetzt wie ma dat umsetzen könnte

[streffin]

Ok ne Stunde hats nich gedauert .....

hier mal bissl code dass dir zeigen sollte wie du das hinbekommst.

IF OBJECT_ID('tempdb.dbo.#temp') > 0

BEGIN

	DROP TABLE #temp

END



create table #test  (bezeichnung NVARCHAR(255), menge INT)

INSERT INTO #test VALUES ('apfel', 3), ('birne', 2)



DECLARE @bezeichnung NVARCHAR(255)

DECLARE @anzahl INT



CREATE TABLE #temp (id int not null, bezeichnung NVARCHAR(255))



DECLARE curs CURSOR FOR

SELECT bezeichnung, menge

FROM #test


OPEN curs

FETCH NEXT FROM curs INTO @bezeichnung, @anzahl


WHILE @@FETCH_STATUS <> -1

BEGIN

	WHILE @anzahl > 0

	BEGIN

		INSERT INTO #temp VALUES (@anzahl, @bezeichnung)

		set @anzahl = @anzahl -1 		

	END

FETCH NEXT FROM curs INTO @bezeichnung, @anzahl

END


SELECT * FROM #temp

(die temp table is bei mir halt dbo, weil auf der kiste bin ich dbo, das is was das muss so nich zwangsläufig bei dir au so sein, aber das is au nur copy & paste von dem was ich mir eben ausn fingern gesaugt hab)

Des Ding dürft netma übermäsig unperformant sein.

ergebnis vom select * :

ID Bezeichung

3 apfel

2 apfel

1 apfel

2 birne

1 birne

Ich denk auf der Grundlage dürfstest keine grossen Probleme haben, dein Query zu bauen.

Gruß

Sven

//Edit :

Ganz wichtig, und von mir vergessen :

CLOSE curs

DEALLOCATE curs

Ganz wichtig.

Btw, als freiwillige Spende nehme ich gerne 1 2 Flaschen Bier an, Adresse gäbs per pm ;P

Bearbeitet 3. Dezember 2010 von streffin

[Brodi87]

@streffin

wow...das ist gut! ich schwöre dir, dass ich ein Paket mit Bier zu Weihnachten schicke!!!

Wenns denn geht...

ich will mein einfaches (select art, menge from art) einbinden...aber leider funktioniert es nicht, mit der Fehlermeldung das ja eine Unterabfrage nicht zulässig sei. habs zwar schon komplett versucht wieder umzubauen aber irgendwie ist der wurm drin.

PS: das Bierangebot steht noch!

[streffin]

Post mal die komplette Fehlermeldung, und das Query bitte.

An und für sich musst du nur den Cursor auf deine Select anpassen.

Gruß

Sven

[Brodi87]

Okay... ich habs hinbekommen. Schuld war ein simpler Tippfehler.

Also...wo soll das Bier denn nun hin?

[Goos]

Das geht eleganter ohne Cursor - mit nem einzelnen Statement und dem Join auf eine Nummerntabelle. Die Nummerntabelle würd ich in dem Fall mal ad hoc erzeugen (insofern die Anzahl nicht zu gross wird).

Ich gehe mal von streffins #test Tabelle aus und würde es dann mit folgendem Statement versuchen. (Das Beispiel funktioniert bis zu einer Anzahl von 9999 ... andernfalls muss die TOP Bedinung angepasst werden)

WITH numbercte(number)

AS

(

	SELECT TOP 9999 ROW_NUMBER() OVER( ORDER BY a.number)  

	FROM master..spt_values AS A 

	INNER JOIN	master..spt_values AS B 

	ON a.Type = 'P'

)

SELECT Bezeichnung, Menge 

FROM #test a 

JOIN numbercte  

ON a.menge >= numbercte.number 

ORDER BY Bezeichnung

Goos

[streffin]

Ähm eleganter würde ich das nicht unbedingt sehen, wild ne systemtable mit sich selbst zu joinen, um genügend rows zu generieren damit ich mit ner row_number joinen kann muss ich sagen.

Also wenn schon würd ich mir nen select max(menge) in ne variable schreiben, und dann in ner schleife ne temp tabelle mit integern fütter und dann darauf joinen, aber mir irgendeine tabelle zusammenjoinen nur für ne row_number, ähm ja, ich würds nicht tun.

[Goos]

...und dann in ner schleife ne temp tabelle mit integern fütter und dann darauf joinen, aber mir irgendeine tabelle zusammenjoinen nur für ne row_number, ähm ja, ich würds nicht tun.

Solltest du aber tun. Der Ansatz, eine temp Tabelle in einer Schleife mit aufsteigenden Zahlen zu füllen ist so ziemlich der schlechtestmögliche.

Im folgenden Beispiel gewinnt mein mengenbasierter Ansatz (Systemtabellen joinen) gegenüber der prozeduralen Lösung (Schleife) schon bei 9999 Nummern um Längen.

Systemtabellen Join: 233 ms

Schleife: 2510 ms

SET STATISTICS TIME ON

DECLARE @i INT

DECLARE @start DATETIME

DECLARE @stop DATETIME


SET @i = 1


CREATE TABLE #numbers

(i INT)


--Prozedurale Erzeugung der Nummerntabelle


SET @start =  CURRENT_TIMESTAMP

WHILE @i < 9999

BEGIN

	INSERT #numbers SELECT @i

	SET @i = @i +1	

END

select i from #numbers

SET @stop =  CURRENT_TIMESTAMP

SELECT DATEDIFF(ms, @start, @stop)


DROP TABLE #numbers



--Mengenbasierte Generierung der Nummerntabelle


SET @start =  CURRENT_TIMESTAMP;

WITH numbercte(number)

AS

(

	SELECT TOP 9999 ROW_NUMBER() OVER( ORDER BY a.number)  

	FROM master..spt_values AS A 

	INNER JOIN	master..spt_values AS B 

	ON a.Type = 'P'

)

select number from numbercte


SET @stop =  CURRENT_TIMESTAMP

SELECT DATEDIFF(ms, @start, @stop)

Goos

[Goos]

Prinzipiell ging es mir bei meinem Ansatz aber nicht um die Generierung der Nummerntabelle. Im Idealfall besitzt die entsprechende Datenbank ja schon eine relativ grosse, indizierte Nummerntabelle.

Ich wollte eigentlich eher die Vorteile des mengenbasierten Joins gegenüber der prozeduralen Cursorlösung herausstellen.

Dazu hier ein Vergleichsbeispiel welches 15000 Zeilen generiert.

Cursorbasierter Ansatz: 1743 ms

Mengenbasierter Ansatz: 386 ms

DECLARE @start DATETIME

DECLARE @stop DATETIME


IF OBJECT_ID('tempdb.dbo.#temp') > 0

BEGIN

	DROP TABLE #temp

END


IF OBJECT_ID('tempdb.dbo.#test') > 0

BEGIN

	DROP TABLE #test

END



CREATE TABLE #test  (bezeichnung NVARCHAR(255), menge INT)

INSERT INTO #test VALUES 

('apfel', 5000)

, ('birne', 10000)




DECLARE @bezeichnung NVARCHAR(255)

DECLARE @anzahl INT



CREATE TABLE #temp (id INT NOT NULL, bezeichnung NVARCHAR(255))


SET @start =  CURRENT_TIMESTAMP


--Cursorbasierter prozeduraler Ansatz von streffin


DECLARE curs CURSOR FOR

SELECT bezeichnung, menge

FROM #test


OPEN curs

FETCH NEXT FROM curs INTO @bezeichnung, @anzahl


WHILE @@FETCH_STATUS <> -1

BEGIN

	WHILE @anzahl > 0

	BEGIN

		INSERT INTO #temp VALUES (@anzahl, @bezeichnung)

		SET @anzahl = @anzahl -1 		

	END

FETCH NEXT FROM curs INTO @bezeichnung, @anzahl

END


SELECT * FROM #temp

SET @stop =  CURRENT_TIMESTAMP

SELECT DATEDIFF(ms, @start, @stop)


CLOSE curs

DEALLOCATE curs



--Mengenbasierter Ansatz mit generierter Nummerntabelle


SET @start =  CURRENT_TIMESTAMP;


WITH numbercte(number)

AS

(

	SELECT TOP 50000 ROW_NUMBER() OVER( ORDER BY a.number)  

	FROM master..spt_values AS A 

	INNER JOIN	master..spt_values AS B 

	ON a.Type = 'P'

)

SELECT Bezeichnung, Menge 

FROM #test a 

JOIN numbercte  

ON a.menge >= numbercte.number 

ORDER BY Bezeichnung


SET @stop =  CURRENT_TIMESTAMP

SELECT DATEDIFF(ms, @start, @stop)

Goos

[streffin]

Das kannst du so direkt aber nicht wirklich vergleichen.

Mit dem Join generierst du dir eine "grosse" Menge Zahlen, die in jedem Fall ausreichend sein muss, also > MAX(Menge).

In dem Beispiel über ne Schleife in eine Temp Tabelle zu schreiben, generierst du aber exakt die benötigte Menge an Zahlen.

Es kommt hier auf den praktischen Anwendungsfall an. Wenn ich 100 Mal nur 1...5 für den Join brauche, aber einmal dazwischen drin, 1...15000, dann sieht der Vergleich im Mittel gleich anders aus, wobei das jetzt auch wieder ein Extrem Beispiel war. Auf jedenfall, SO schlecht kommt die Schleife nicht weg, weil die Schleife nur so oft durchlaufen wird, wie benötigt. Das Subquery musst du aber so auslegen, dass in jedem denkbaren Fall die generierte Menge ausreicht. Zusätzlich ist das eine extrem hässliche Fehlerquelle, weil eben kein "Fehler" vorkommt, wenn die generierte Menge mal nicht gereicht hat.

Das ist der Vorteil wenn man Dynamisch an die Sache rangeht.

Ich bin an der Sache allerdings auch nicht unter performance Optimierungsaspekten rangegangen. Da lässt sich verdammt viel, in meinem wie auch in deinem Vorschlag machen.

Btw, bissl offtopic, aber welcher hardware läuft dein server? das sieht beides irgendwie recht langsam aus.

Bearbeitet 8. Dezember 2010 von streffin

[Corto -sX-]

ich würde mal vermuten es ist sein Notebook oder sein Arbeitsplatzrechner mit einer lokalen DB.

da wo man solchen code eben mal ausführt

[Goos]

Hi streffin,

Scheinbar kommt hier nicht so recht an was ich ausdrücken wollte.

Im ersten Vergleich hab ich herausstellen wollen, dass ein Join zum Erstellen einer Nummerntabelle performanter ist als eine Schleife (Der Geschwindigkeitsvorteil steigt hier mit der Menge der generierten Nummern).

Im Zweiten Beispiel ging es mir allein um die Vorteile des Join anstelle der Schleife um entsprechende Duplikate zu erzeugen.

Auch hier gewinnt der Join mit zunehmender Menge der generierten Daten ganz deutlich, ist aber im Gegenzug nie wirklich langsamer als die Schleife. Die dabei verwendete Art der Nummerngenerierung ist sicherlich auch mit Nachteilen behaftet, eignet sich aber zu Beispielzwecken besser als eine grosse, statische Nummerntabelle.

Das Subquery musst du aber so auslegen, dass in jedem denkbaren Fall die generierte Menge ausreicht. Zusätzlich ist das eine extrem hässliche Fehlerquelle, weil eben kein "Fehler" vorkommt, wenn die generierte Menge mal nicht gereicht hat.

Wie in meinem vorherigen Beitrag schon erwähnt würde ich der Performance wegen aber eine indizierte Nummerntabelle mit einigen Millionen Nummern bevorzugen. Im Falle der Etiketten braucht man sich dann keinerlei Sorgen wegen eines Überlaufs machen, obwohl dein Einwand sonst natürlich gerechtfertigt ist und evtl. eine gesonderte Überprüfung erfordert.

Ich bin an der Sache allerdings auch nicht unter performance Optimierungsaspekten rangegangen. Da lässt sich verdammt viel, in meinem wie auch in deinem Vorschlag machen.

Diese Optimierungsversprechen halte ich für aus der Luft gegriffen. Zumindest behaupte ich, dass es dir nicht gelingen wird, meinen Ansatz (abgesehen von der schon erwähnten, indizierten Nummerntabelle) noch nennenswert zu beschleunigen. (Faktor < 0,7)

Wenn du magst, kannst du es aber gerne versuchen. Ich bin konstruktiven Vorschlägen gegenüber immer aufgeschlossen

Btw, bissl offtopic, aber welcher hardware läuft dein server? das sieht beides irgendwie recht langsam aus.

PS: Meine MS SQLServer laufen allesamt auf meinem Notebook

Goos