Hallo Forum,
verwendetes Datenbanksystem: MS SQL 2005.
ich möchte gerne eine Abfrage für mehere Tage machen (ab "heute" die nächsten 30 Tage). Hier möchte ich aber auch, wenn es für einen Tag keine Daten gibt, dass dieser angezeigt wird. Eine Tabelle für die Daten habe ich nicht, daher mache ich die Abfrage so:
RIGHT OUTER JOIN
(
SELECT (CONVERT(VARCHAR(12), @StartDate, 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,1, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,2, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,3, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,4, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,5, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,6, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,7, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,8, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,9, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,10, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,11, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,12, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,13, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,14, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,15, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,16, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,17, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,18, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,19, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,20, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,21, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,22, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,23, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,24, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,25, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,26, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,27, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,28, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,29, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT (CONVERT(VARCHAR(12), DATEADD(day,30, @StartDate), 112)) As Date, 'xxx' as Type UNION
SELECT 'Total' As Date, 'xxx' as Type
Meine Frage ist einfach, geht es besser mit "einfachen" Boardmittel?
lg Lion
Hallo dN!3L,
danke für deine Antwort, aber das habe ich auch versucht, funktioniert für sich selbst alleine, aber wenn ich es versuche, zu joinen (erste Zeile --> RIGHT OUTER JOIN) gibt es Probleme mit dem ";". Und ohne erhalte ich die Meldung, dass das Statement zuvor abgeschlossen sein soll.
Also ich brauch nicht eine "einfache" Ausgabe, ich brauch es in Verbindung mit dem JOIN.
Eine Tabelle wollte ich vermeinden, wo ich "einfach" pro Tag einen DS habe, weil ich gedacht habe, es muss doch auch so gehen.
Gruß,
Lion
lg Lion
Hallo Lion1984
Ich sehe leider nicht genau wo das Problem liegt.
Hier ein Beispiel (ich habe jetzt einen LEFT JOIN verwendet) bei dem alle Tage und die entsprechenden Daten angezeigt werden.
---========================================================
-- Some sample data
DECLARE @data TABLE (Id INT IDENTITY, Dt DATETIME, SomeInt INT)
INSERT INTO @data
SELECT '2009-07-01', 1
UNION ALL SELECT '2009-07-20', 5
UNION ALL SELECT '2009-07-15', 12
UNION ALL SELECT '2009-07-26', -1
---========================================================
-- A pseudo start date
DECLARE @start_date DATETIME
SELECT @start_date = '2009-07-01'
---========================================================
-- Show result
; WITH
Numbers AS
(
-- We need some numers; lets use master.sys.all_columns
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Num
FROM master.sys.all_columns
),
Days AS
(
-- Get the days for the specified month
SELECT
DATEADD(DAY, Num - 1, @start_date) Dt
FROM Numbers
WHERE DATEADD(DAY, Num - 1, @start_date) < DATEADD(MONTH, 1, @start_date)
)
-- Get all days and the corresponding data from @data table
SELECT *
FROM Days d
LEFT JOIN @data d2 ON d.Dt = d2.Dt
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ß.
Stimmt,
ich hatte woanders einen Fehler drinnen, und daher hatte ich auch die Probleme. Ich habe das Statement nun in Verwendung.
An die SQL Profis: Was wäre besser? Perfomancetechnisch?
Bzw. wie kann ich die Abfrage genau messen? Denn es scheint, dass die Abfrage nun ein Stück langsamer ist.
Lion
lg Lion
Hallo Lion1984
...Was wäre besser? Perfomancetechnisch?
Ich weiß jetzt nicht genau was du meinst. Wenn du das UNION im vergleich zu der "hingezimmerten" Numbers Table meinst, würde ich auf das UNION tippen, wobei der Unterschied im Millisekunden-Bereich liegen dürfte.
Ich habe für solche Sachen normalerweise auch eine feste Numbers-Tabelle in jeder Datenbank (meistens 11,000 Zeilen). So vermeidet man, dass die Werte immer wieder neu erzeugt werden müssen und der Optimizer kann das Statement besser handeln. Hier findest du einen super Artikel von Jeff Moden zum Thema: The Numers or Tally Table. Mit einer festen Numbers-Tabelle dürfte die Performance gleich sein (oder sich unterhalb einer Millisekunde unterscheiden).
Nachteil der UNION-Lösung ist halt zum einen, dass es bei einer Auswertung über ein Jahr recht unleserlich werden dürfte 😉 - und halt sehr unflexibel ist. Würde übrigens korrekterweise UNION ALL statt UNION verwenden, da ja keine Gruppierung gewünscht ist - was ebenfalls Performance kostet (siehe Execution Plan).
Für deine spezielle Anforderung könnte auch eine Calendar-Table die beste Lösung sein:
Why should I consider using an auxiliary calendar table?
Die User Defined Functions in dem Artikel sind zwar zum Teil etwas schrottig, aber die Arbeitsweise wird gut erklärt.
Bzw. wie kann ich die Abfrage genau messen? Denn es scheint, dass die Abfrage nun ein Stück langsamer ist.
Das beste Tool für Performance-Messungen war ist und bleibt der SQL Server Profiler.
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ß.
Hallo Florian,
danke für die Links, ich werde mir diese durchlesen.
Zur Erklärung, ich habe aktuell eine Abfrage gebastelt, welche als "normales" Statement funktioniert (d.h. auf der DB im Managment Studio rechte Maustaste, new Query, Statement reinkopiert), und das relativ perfomant.
Kopiere ich das selbe Statement in eine Stored Procedure, dann schnallzt mir der SQL Server auf 99% CPU Auslastung und bleibt da, bis die Abfrage fertig ist (>10sek).
Und da versuche ich, das Problem zu identifizieren und zu lösen, und u.a. war eben dies Union Statement für mich fragwürdig. Ich werde es mal mit einer Tabelle versuchen, wobei ch denke, dass der Hund woanders begraben ist.
Lion
lg Lion
Kopiere ich das selbe Statement in eine Stored Procedure, dann schnallzt mir der SQL Server auf 99% CPU Auslastung und bleibt da, bis die Abfrage fertig ist (>10sek).
Sehr viele UNION (ALL) Statement können im Compiler einmalig sehr lange dauern bis der passende Execution Plan erzeugt ist, danach sollte die Ausführung schnell gehen.
Kannst du die Prozedur bitte mal posten?
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ß.
Hallo,
Dank Florian's Hilfe habe ich meine Probleme gelöst, um nun auch mein neu erworbenes Wissen zu verbreitern, schreib ich es auch hier nieder 😃
Problem war, dass meinee Query sehr lange lief und 99% CPU Auslastung verursachte (und das bei jeder Query, auch wenn diese schon mal gelaufen ist).
Der Grund hierfür waren z.T. sehr tief verschachtelte Queries (wie z.B. das obrige SQL Statement) und sehr häufige Code wiederholungen.
Abhilfe waren CTE's und statt der PIVOT eine CROSS TAB Query, für mich hies das: PIVOT "überschätzt" (in meinem Fall), CTE's unterschätzt. Ich habe meinen Code um ca 50% Zeilen kürzen können und das Statement läuft mit zufriedener Geschwindigkeit.
In diesem Zusammenhang möcht auch zwei Links posten, welche mir bei den CROSS TAB Query sehr geholfen haben.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Danke an allen Helfern!
Lion
lg Lion
Freut mich, dass wir helfen konnten!
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ß.