Laden...

MS SQL - Abfrage per Tag

Erstellt von Lion1984 vor 14 Jahren Letzter Beitrag vor 14 Jahren 1.719 Views
L
Lion1984 Themenstarter:in
770 Beiträge seit 2006
vor 14 Jahren
MS SQL - Abfrage per Tag

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

2.891 Beiträge seit 2004
vor 14 Jahren

Hallo Lion1984,

vielleicht hilft dir Folgendes: -10 bis +10 in 0.5 Schritten ausgeben

Gruß,
dN!3L

L
Lion1984 Themenstarter:in
770 Beiträge seit 2006
vor 14 Jahren

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

1.564 Beiträge seit 2007
vor 14 Jahren

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ß.

L
Lion1984 Themenstarter:in
770 Beiträge seit 2006
vor 14 Jahren

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

1.564 Beiträge seit 2007
vor 14 Jahren

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ß.

L
Lion1984 Themenstarter:in
770 Beiträge seit 2006
vor 14 Jahren

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

1.564 Beiträge seit 2007
vor 14 Jahren

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ß.

L
Lion1984 Themenstarter:in
770 Beiträge seit 2006
vor 14 Jahren

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

1.564 Beiträge seit 2007
vor 14 Jahren

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ß.