SQL Select mit Subselect

Question

Nabend zusammen,

ich habe eine kleine Frage zu meiner SQL-Abfrage. Es geht um die Protkolierung von verschiedenen Geräten an Hand derer HttpRequests.

Kurz zum Aufbau meiner Datenbank: 

Location Tabelle - Primary Key: Location_UID, zweite Spalte: Location_Name

Devices Tabelle - Primary Key: Devices_UID, zweite Spalte: Device_Name, dritte Spalte: Location_UID Foreign Key mit Bezug auf den PK der Location Tabelle

StatusLog Tabelle - PK ID, automatisch gesetzt und erhöht sich immer um 1, zweite Spalte: Devices_UID dritte Spalte: CurrentFileName Wert wird hier aus dem HttpReq. gelesen,

vierte Spalte: LastAccess wird standardmäßig mit getdatetime() gesetzt beim Insert in die Tabelle

Nun möcht ich einen SQL-Befehl entwickeln, mit dem ich von jedem Device die passende Location und die Infos aus der StatusLog Tabelle angezeigt bekomme. Jedoch möchte ich nur den aktuelltste Spalte angezeigt bekommen.

Ein Kollege - der lange Zeit nun krank ist - hat mir folg. Befehl erstellt: 

SELECT     TOP (100) lg.Devices_UID, dv.Device_Name, dv.Location_UID, loc.Location_Name, lg.FileName, lg.CreationTime AS LastConnect

FROM         dbo.Devices AS dv INNER JOIN

                      dbo.Locations AS loc ON dv.Location_UID = loc.Location_UID INNER JOIN

                      dbo.Log AS lg ON dv.Devices_UID = lg.Devices_UID

WHERE     (lg.CreationTime =                          (SELECT     MAX(CreationTime) AS Expr1

                            FROM          dbo.Log AS l

                            WHERE      (dv.Devices_UID = lg.Devices_UID)))

Zurzeit wird jedoch bei mir nur der aller letzte Eintrag von allen Devices angezeigt. Sprich nur ein Eintrag immer. Jedoch will ich ja von jedem Device den aktuellsten Eintrag.

Jedoch funktioniert dieser bei mir nicht mehr? Findet ihr einen Fehler?

Answer 1

Hallo,

ich gehe mal davon aus, Du verwendest SQL Server 2005 oder später.
Dort wäre die Abfrage mittels CROSS APPLY am einfachsten geschrieben:

SELECT TOP (100) 
	dv.Devices_UID, 	-- lg. ersetzt durch dv., da passender
	dv.Device_Name, 
	dv.Location_UID, 
	loc.Location_Name, 
	lg.FileName, 
	lg.CreationTime AS LastConnect
FROM dbo.Devices AS dv 
INNER JOIN dbo.Locations AS loc 
	ON dv.Location_UID = loc.Location_UID
CROSS APPLY (SELECT TOP (1) 
		FileName, 
		CreationTime 
	FROM dbo.Log AS l
	WHERE dv.Devices_UID = l.Devices_UID
	ORDER BY l.CreationTime DESC) AS lg
ORDER BY dv.Devices_UID -- sollte für TOP(n) immer angegeben werden

Durch variieren von TOP(1) kannst Du Dir auch mehr als eine Zeile liefern lassen.

Gruß Elmar

Answer 2

Danke! Es funktioniert!

 

Gruß

Answer 3

Guten Tag,

mittlerweile sind sind einige Datenbestände angewachsen, laut dem Aktivitätsmonitor dauert die Abfrage ca. 3 Sekunden, gibt es vllt. eine "schnellere" Abfrage?

Danke im Vorraus!

Answer 4

hi,

Hat du deine Log-Tabelle indiziert? Ist ein Index (Devices_UID ASC, CreationTime DESC) vorhanden?

Auf der Device-Tabelle sollte ein Index (Location_UID[, Devices_UID]) include (Device_Name) existieren.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 5

Hallo,

welche Indizes hast Du für die Tabellen erstellt?

So wäre für den Abschnitt in CROSS APPLY ein Index auf das CreatetionDate + UID sinnvoll.
Auch die UID für die anderen Tabellen sollten als Primary Key bzw. mit einem Index versehen sein.

Prüfe mal den Ausführungsplan im SSMS (STRG+M).

Gruß Elmar