19
SQL Transaction - Software-Version - nur höchste anzeigen
Hallo Community,
Ich bin mit SQL nicht so firm, erstelle aber Custom Reports für SCCM/MECM. Ja ich weiß! Das passt doch gar nicht zusammen 😂🤷♂️🙈 Dank Google hat es immer wieder gereicht und die Reports sind ganz passabel.
Nun stehe ich aber vor einer neuen Herausforderung. Ich möchte nur die Zeilen ausgeben mit der jeweils höchsten Version. Leider liegt die Version als String vor mit "." getrennt. Bsp:
Alle Beispiele die ich so gefunden habe berücksichtigen das nicht.
meine Query bisher. Und da müsste irgendwas mit mit MAX()?? CAST()?? SUBSTRING()?? CHARINDEX()??... Keine Ahnung() rein damit da pro Hostname nur die jeweils höchste Version drinnen steht. Vermutlich ist das gleichzeitig auch der letzte Eintrag in die Table, aber ich will wirklich gerne nur nach Version filtern:
Danke für eure Unterstützung!
Beste Grüße!
Mayho
Ich bin mit SQL nicht so firm, erstelle aber Custom Reports für SCCM/MECM. Ja ich weiß! Das passt doch gar nicht zusammen 😂🤷♂️🙈 Dank Google hat es immer wieder gereicht und die Reports sind ganz passabel.
Nun stehe ich aber vor einer neuen Herausforderung. Ich möchte nur die Zeilen ausgeben mit der jeweils höchsten Version. Leider liegt die Version als String vor mit "." getrennt. Bsp:
Alle Beispiele die ich so gefunden habe berücksichtigen das nicht.
meine Query bisher. Und da müsste irgendwas mit mit MAX()?? CAST()?? SUBSTRING()?? CHARINDEX()??... Keine Ahnung() rein damit da pro Hostname nur die jeweils höchste Version drinnen steht. Vermutlich ist das gleichzeitig auch der letzte Eintrag in die Table, aber ich will wirklich gerne nur nach Version filtern:
select distinct
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
where
opi.IsProPlusInstalled0 = 1Danke für eure Unterstützung!
Beste Grüße!
Mayho
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 64182035700
Url: https://administrator.de/forum/sql-transaction-software-version-nur-hoechste-anzeigen-64182035700.html
Ausgedruckt am: 31.12.2024 um 09:12 Uhr
19 Kommentare
Neuester Kommentar
Also auch Strings können mit max() ausgewertet werden aber ich bevorzuge eine Window-Function mit einem ORDER BY. Hier mal eine allgemein gängige Variante:
Es gibt natürlich noch andere Wege das ganze zu tun aber so ist es recht übersichtlich und kann bequem erweitert werden.
WITH t AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Channel ORDER BY Version DESC) AS zeile FROM tabelle
)
SELECT t.* FROM t WHERE t.zeile = 1
Eine etwas komplexere Lösung, die aber für künftige Auswertungen vorteilhaft sein könnte, wäre:
1. eine gesonderte Tabelle anlegen, mit der Spalte "Version", in der der Versionsstring abgelegt wird, und vier Computed Columns, die die vier Bestandteile der Versionsnummer seperat als Zahl wiedergeben.
2. regelmäßiges / getriggertes / .... Aufrufen von INSERT, mit dem die noch nicht in der Tabelle erfassten Versionsnummern (jede nur ein einziges Mal vorhanden!) nachgetragen werden
3. bei Auswertungen werden lediglich noch die Versionsspalten der abzufragenden Tabelle (bei Dir opi) mit dieser neuen Tabelle über ein JOIN der Wahl verknüpft werden. Dann kannst Du künftig nach Belieben über die Computed Columns MAX, MIN, ... differenzieren und auswählen. Aufgrund der Zahlenwerte ist das schließlich ein Kinderspiel. Weil Du außerdem die Spalten der relativ klein( bleibend)en Tabelle indizieren kannst, kannst Du zugleich die Abfrageleistung deutlich optimieren.
Voraussetzung für diesen Ansatz:
- Du / Dein Unternehmen hat die Berechtigung neue Tabellen sowie Proceduren, Trigger etc. hinzuzufügen.
- Du oder jemand anderes bei Euch ist bereit, sich hier etwas einzudenken/-arbeiten.
Viele Grüße
HansDampf06
1. eine gesonderte Tabelle anlegen, mit der Spalte "Version", in der der Versionsstring abgelegt wird, und vier Computed Columns, die die vier Bestandteile der Versionsnummer seperat als Zahl wiedergeben.
2. regelmäßiges / getriggertes / .... Aufrufen von INSERT, mit dem die noch nicht in der Tabelle erfassten Versionsnummern (jede nur ein einziges Mal vorhanden!) nachgetragen werden
3. bei Auswertungen werden lediglich noch die Versionsspalten der abzufragenden Tabelle (bei Dir opi) mit dieser neuen Tabelle über ein JOIN der Wahl verknüpft werden. Dann kannst Du künftig nach Belieben über die Computed Columns MAX, MIN, ... differenzieren und auswählen. Aufgrund der Zahlenwerte ist das schließlich ein Kinderspiel. Weil Du außerdem die Spalten der relativ klein( bleibend)en Tabelle indizieren kannst, kannst Du zugleich die Abfrageleistung deutlich optimieren.
Voraussetzung für diesen Ansatz:
- Du / Dein Unternehmen hat die Berechtigung neue Tabellen sowie Proceduren, Trigger etc. hinzuzufügen.
- Du oder jemand anderes bei Euch ist bereit, sich hier etwas einzudenken/-arbeiten.
Viele Grüße
HansDampf06
Zitat von @HansDampf06:
Eine etwas komplexere Lösung, die aber für künftige Auswertungen vorteilhaft sein könnte, wäre:
1. eine gesonderte Tabelle anlegen, mit der Spalte "Version", in der der Versionsstring abgelegt wird, und vier Computed Columns, die die vier Bestandteile der Versionsnummer seperat als Zahl wiedergeben.
2. regelmäßiges / getriggertes / .... Aufrufen von INSERT, mit dem die noch nicht in der Tabelle erfassten Versionsnummern (jede nur ein einziges Mal vorhanden!) nachgetragen werden
3. bei Auswertungen werden lediglich noch die Versionsspalten der abzufragenden Tabelle (bei Dir opi) mit dieser neuen Tabelle über ein JOIN der Wahl verknüpft werden. Dann kannst Du künftig nach Belieben über die Computed Columns MAX, MIN, ... differenzieren und auswählen. Aufgrund der Zahlenwerte ist das schließlich ein Kinderspiel. Weil Du außerdem die Spalten der relativ klein( bleibend)en Tabelle indizieren kannst, kannst Du zugleich die Abfrageleistung deutlich optimieren.
Der Lösung kann ich nicht viel abgewinnen, wozu das ganze genau? max() und min() z.B. gehen auch auf Zeichenketten, also warum dann in eine Zahl umwandeln und vor allem warum gleich in vier?Eine etwas komplexere Lösung, die aber für künftige Auswertungen vorteilhaft sein könnte, wäre:
1. eine gesonderte Tabelle anlegen, mit der Spalte "Version", in der der Versionsstring abgelegt wird, und vier Computed Columns, die die vier Bestandteile der Versionsnummer seperat als Zahl wiedergeben.
2. regelmäßiges / getriggertes / .... Aufrufen von INSERT, mit dem die noch nicht in der Tabelle erfassten Versionsnummern (jede nur ein einziges Mal vorhanden!) nachgetragen werden
3. bei Auswertungen werden lediglich noch die Versionsspalten der abzufragenden Tabelle (bei Dir opi) mit dieser neuen Tabelle über ein JOIN der Wahl verknüpft werden. Dann kannst Du künftig nach Belieben über die Computed Columns MAX, MIN, ... differenzieren und auswählen. Aufgrund der Zahlenwerte ist das schließlich ein Kinderspiel. Weil Du außerdem die Spalten der relativ klein( bleibend)en Tabelle indizieren kannst, kannst Du zugleich die Abfrageleistung deutlich optimieren.
Der Ansatz mit alter Schule wäre ein Join mit dem eigenen Aggregat:
WITH t AS (
SELECT Channel,max(Version) AS Version
FROM tabelle
GROUP BY Channel
)
SELECT tabelle.*
FROM tabelle
INNER JOIN t
ON tabelle.Channel = t.Channel
AND tabelle.Version = t.VersionZitat von @ukulele-7:
Der Lösung kann ich nicht viel abgewinnen, wozu das ganze genau? max() und min() z.B. gehen auch auf Zeichenketten, also warum dann in eine Zahl umwandeln und vor allem warum gleich in vier?
Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.Der Lösung kann ich nicht viel abgewinnen, wozu das ganze genau? max() und min() z.B. gehen auch auf Zeichenketten, also warum dann in eine Zahl umwandeln und vor allem warum gleich in vier?
Ganz gewiss kann man das alles - wenn es benötigt wird - auch immer in einer Abfrage differenzieren. Indes bedeutet diese Differenzierung auch immer wieder den gleichen Rechenaufwand. Eine Computed Column macht diesen Rechenaufwand nur ein einziges Mal: nämlich beim INSERT / UPDATE des Versionswertes. Alle nachfolgenden Abfragen können auf die bereits berechneten und gespeicherten Werte direkt zugreifen. Und genau darin liegt der unermessliche Mehrwert.
Du übersiehst außerdem, dass das Sortieren von Stringwerten deutlich mehr Ressourcen und Zeit erfordert als der Vergleich von Zahlenwerten. Hierfür schafft die Aufsplittung eine probate Abhilfe.
Freilich werden die Leistungsvorteile erst mit zunehmender Datenmenge deutlicher spürbar werden. Aber die anderen Vorteile greifen bei jeder beliebigen Datenmenge. Genauso kommt es natürlich darauf an, dass sinnvoll einzubauen, zum Beispiel mittels Indizes, wobei auch hier der Analyzer / Optimizer und die dynamischen Systemdaten vielfältige Hilfe anbieten.
Der Ansatz mit alter Schule wäre ein Join mit dem eigenen Aggregat: ...
Das mag alter Schule entsprechen, gewiss. Aber es bleibt bei dem verhaftet, weil es die geniale Idee und das enorme Potential der Verfachung, Beschleunigung und leichteren Wartung von Computed Columns außen vor lässt. Ich bin mir sicher, wer sich einmal mit Computed Columns beschäftigt und deren Nützlichkeit erkannt hat, sieht ganz neue Möglichkeiten - er wird es sogar lieben und sofort einsetzen, wo es sich anbietet.Übrigens verbleibt es auch mit Computed Columns bei der alten Schule. Denn die eigentliche Auswertung findet natürlich weiterhin per Aggregat etc. statt. Nur ist es im vorliegenden Fall dann nicht mehr ein Stringfeld, sondern beispielsweise die Updatenummer (, während beispielsweise über die Hauptversion und das Release eine Filterung per WHERE / HAVING möglich wäre oder eine Gruppierung oder ...).
Wenn der TO sich lediglich auf den Versionsstring beschränken will, macht er es halt, wie Du es ihm empfohlen hast. Mein Vorschlag geht aber darüber hinaus und eröffnet Möglichkeiten in der Zukunft, die ein Aggregat auf ein simples Stringfeld nicht liefern kann. Und genau so hatte ich es auch ausdrücklich formuliert:
Eine etwas komplexere Lösung, die aber für künftige Auswertungen vorteilhaft sein könnte, wäre:
Übrigens könnte der TO die notwendige Befüllung der anzulegenden neuen Versionstabelle zusammen mit seinem Report erledigen, wenn er das ganze in eine Function oder Procedure packt. MS SQL Server bietet eben eine ganze Menge an Gestaltungsmöglichkeiten, die nur darauf warten, benutzt zu werden.
Mit anderen Worten:
Mein Vorschlag hat gar nicht die Absicht gehabt, Deine zutreffende und einfache Lösung für das konkrete Problem in Abrede zu stellen. Vielmehr zeigt mein Vorschlag auf, was darüber hinaus noch möglich ist, wobei Deine Lösung ja weiterhin integraler Bestandteil bleibt, weil die konkrete Abfrage der höchsten Versionsnummer weiterhin gewünscht ist.
Noch ein wichtiger Hinweis:
Wie würdest Du die Versionswerte "16.0.1.23" und "16.0.1.3" ab-/aufsteigend sortieren und wie wird die Datenbank diese Strings definitiv sortieren? Spätestens an dieser Stelle scheitert die direkte Anwendung der Aggregate auf die Stringwerte beziehungsweise die Ergebnisse sind unzutreffend / falsch. Mein Lösungsansatz kennt dieses Problem nicht beziehungsweise beseitigt es!
Insoweit ist dem TO anzuraten, einmal zu prüfen, ob die Versionsnummern immer gleich lang sind und sich die Trennungspunkte immer an derselben Stelle befinden. Sobald das nicht gesichert ist, sind reine Stringwerte für einen treffsicheren Vergleich völlig ungeeignet. Es ist und bleibt dann ungewiss, dass der ermittelte höchste Versionsstring tatsächlich der neueste / aktuellste ist.
Viele Grüße
HansDampf06
PS: Bezüglich Hilfe des Analyzers / Optimizers von SQL Server - bei mir werden seit längerem alle benötigten Indizes vollautomatisch mittels der anfallenden Systemdaten erstellt / gelöscht und gepflegt. Das Stichwort ist hierbei unter anderem INCLUDE.
Die gezeigten Versionsnummern sind natürlich nicht ideal, ich denke das vielleicht die erste Nummer von variabler Länge sein kann. Das wissen wir aber nicht, das muss der OP beantworten. Genauso wie wir nicht wissen ob er z.B. überhaupt die Tabelle verändern kann und will.
Zitat von @ukulele-7:
Genauso wie wir nicht wissen ob er z.B. überhaupt die Tabelle verändern kann und will.
Aufgrund seiner Ausführungen und seines SQL-Konstrukts gehe ich davon aus, dass die Tabelle mit der Versionsnummer eine Systemtabelle ist, die eher nicht geändert werden kann, selbst wenn er es wollte. Deshalb hatte ich auf eine zusätzliche Tabelle abgestellt, weil das machbar erscheint.Genauso wie wir nicht wissen ob er z.B. überhaupt die Tabelle verändern kann und will.
Andernfalls könnte er - wenn er meinem Vorschlag folgen wollen möchte - natürlich die Computed Columns direkt dieser originalen Tabelle beifügen, was selbstredend am optimalsten wäre. Insbesondere entfiele dann die Notwendigkeit, die neue Tabelle aktuell zu halten.
Viele Grüße
HansDampf06
Moin,
wenn die Versionsnummern nicht fest das Format ##.#.#####.##### haben, sondern die einzelnen Nummern unterschiedlich lang sein können, dann kann man in diesem Fall aber eine SQL-Server-Funktion mißbrauchen: parsename. Die ist zwar eigentlich dafür gedacht, Objektnamen in ihre Bestandteile Server, Datenbank, Schema und Objekt zu zerlegen, kann aber auch hier verwendet werden, weil die Versionsnummer ebenfalls aus vier Teilen besteht, getrennt durch einen Punkt.
Dadurch, daß nur SQL-Server-Funktionen genutzt werden, könnte man das sogar für berechnete Spalten in der Tabelle nutzen und die einzelnen Bestandteile in Spalten aufnehmen und einen Index draufsetzten.
Für die Ermittlung der neuesten Version könnte dann die Lösung von ukulele von oben verwendet werden:
Gruß, Mad Max
wenn die Versionsnummern nicht fest das Format ##.#.#####.##### haben, sondern die einzelnen Nummern unterschiedlich lang sein können, dann kann man in diesem Fall aber eine SQL-Server-Funktion mißbrauchen: parsename. Die ist zwar eigentlich dafür gedacht, Objektnamen in ihre Bestandteile Server, Datenbank, Schema und Objekt zu zerlegen, kann aber auch hier verwendet werden, weil die Versionsnummer ebenfalls aus vier Teilen besteht, getrennt durch einen Punkt.
drop table if exists #test
create table #test (id int identity, v varchar (50))
insert into #test (v) values ('16.1.4711.0815'), ('16.0.4711.0815'), ('6.1.4711.0815')
select *, convert (int, parsename (v, 4)) as v1, convert (int, parsename (v, 3)) as v2, convert (int, parsename (v, 2)) as v3, convert (int, parsename (v, 1)) as v4 from #test order by v1, v2, v3, v4Dadurch, daß nur SQL-Server-Funktionen genutzt werden, könnte man das sogar für berechnete Spalten in der Tabelle nutzen und die einzelnen Bestandteile in Spalten aufnehmen und einen Index draufsetzten.
Für die Ermittlung der neuesten Version könnte dann die Lösung von ukulele von oben verwendet werden:
WITH t AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Hostname ORDER BY convert (int, parsename (Version, 4)) DESC, convert (int, parsename (Version, 3)) DESC, convert (int, parsename (Version, 2)) DESC, convert (int, parsename (Version, 1)) DESC) AS zeile FROM tabelle
)
SELECT * FROM t WHERE t.zeile = 1Gruß, Mad Max
1
Das ist in diesem Fall eine sehr gute Idee, zumal es immer besser ist, integrierte Funktionen zu verwenden anstatt es selber zu programmieren. Ansonsten müsste man es für das Befüllen einer Computed Column äquivalent über STRING_SPLIT realisieren:
- ab SQL Server 2022 wohl einfach nur:
Das ist mit parsename quasi identisch.
- für frühere Versionen etwas aufwendiger:
Spätestens hier zeigt sich, welchen Vorteil eine Computed Column hat, wenn man das nur einmal berechnen muss und später einfach der einmal berechnete Wert verwendet werden kann - insbesondere wenn keine integrierten Funktionen wie parsename zur Verfügung stehen und/oder die Wertberechnung komplexer wird.
Für das Befüllen der Computed Columns benötigt man natürlich eine geeignete Berechnung / Funktion. parsename ist für die Versionen 2019 und früher die hier beste Wahl. Ab Version 2022 ist STRING_SPLIT eine sinnvolle Alternative.
STRING_SPLIT ist in allen Versionen sogar ein Muss, wenn es um ähnlich gelagerte Fälle geht, bei denen parsename nicht nutzbar ist (z.B. anderes Trennzeichen oder größere Anzahl von Gliedern).
Wenn es nur um eine Umwandlung des Versionsstrings in eine Zahl geht, könnte aus dem Versionsstring (hier mittels parsename) auch ein vergleichssicherer Versionsstring gebildet werden:
Diese vereinfachte Form [Zur besseren Veranschaulichung habe ich die Zehnerpotenzen als Multiplikation belassen und nicht als Zahl zusammengefasst!] hat selbstredend durch die Definitionsbereiche (hier für bigint) ihre praktischen Grenzen, wenn es um mehr Glieder oder um Glieder mit mehr Stellen geht. Für Werte außerhalb des Definitionsbereichs müssen die einzelnen Glieder wie folgt gebildet
und dann auch wieder in der richtigen Reihenfolge zu einem String verbunden werden. Das heißt: Solange alle Versionsstrings nach ihrer derartigen Umwandlung als Zahl im Wertebereich von bigint bleiben, ist bigint besser als varchar etc. Könnte der Wertebereich überschritten/verlassen werden, bleibt nur varchar etc.
Mit diesem vereinfachten vergleichssicheren Versionsstrings würde nur eine Computed Column benötigt werden, wenn die Berechnung persistent gemacht werden soll. Nachteilig daran ist aber, dass eine differenziertere Auswertung wie bei vier Computed Columns (= je Glied eine) nicht direkt möglich wäre.
Es bietet sich sogar an, diesen vereinfachten Versionsstring mit der vierteiligen Differenzierung zu kombinieren (= fünf Computed Columns), wodurch die Freiheitsgrade und Gestaltungsmöglichkeiten für künftige Abfragen nochmals gesteigert werden.
Viele Grüße
HansDampf06
- ab SQL Server 2022 wohl einfach nur:
STRING_SPLIT('16.0.1.23','.', [gewünschte Stelle im String als Zahl, also 1, 2, 3 oder 4]) - für frühere Versionen etwas aufwendiger:
SELECT value FROM (SELECT ROW_NUMBER() OVER(ORDER BY Wert) as Zeile, value FROM STRING_SPLIT('16.0.1.23','.'), (SELECT 1 AS Wert) AS TBL) AS VTab WHERE Zeile = [gewünschte Stelle] könnte man das sogar für berechnete Spalten in der Tabelle nutzen und die einzelnen Bestandteile in Spalten aufnehmen und einen Index draufsetzten.
Richtig! Genau das ist der Kern meines weiterführenden Vorschlags.Für das Befüllen der Computed Columns benötigt man natürlich eine geeignete Berechnung / Funktion. parsename ist für die Versionen 2019 und früher die hier beste Wahl. Ab Version 2022 ist STRING_SPLIT eine sinnvolle Alternative.
STRING_SPLIT ist in allen Versionen sogar ein Muss, wenn es um ähnlich gelagerte Fälle geht, bei denen parsename nicht nutzbar ist (z.B. anderes Trennzeichen oder größere Anzahl von Gliedern).
Wenn es nur um eine Umwandlung des Versionsstrings in eine Zahl geht, könnte aus dem Versionsstring (hier mittels parsename) auch ein vergleichssicherer Versionsstring gebildet werden:
CONVERT(varchar(20),CONVERT(bigint,PARSENAME('16.0.1.23',4))*100*10000*10000+CONVERT(bigint,PARSENAME('16.0.1.23',3))*10000*10000+CONVERT(bigint,PARSENAME('16.0.1.23',2))*10000+CONVERT(int,PARSENAME('16.0.1.23',1))) RIGHT(CONVERT(varchar(5),CONVERT(int,PARSENAME('16.0.1.99',[gewünschte Stelle]))+10000),4) Mit diesem vereinfachten vergleichssicheren Versionsstrings würde nur eine Computed Column benötigt werden, wenn die Berechnung persistent gemacht werden soll. Nachteilig daran ist aber, dass eine differenziertere Auswertung wie bei vier Computed Columns (= je Glied eine) nicht direkt möglich wäre.
Es bietet sich sogar an, diesen vereinfachten Versionsstring mit der vierteiligen Differenzierung zu kombinieren (= fünf Computed Columns), wodurch die Freiheitsgrade und Gestaltungsmöglichkeiten für künftige Abfragen nochmals gesteigert werden.
Für die Ermittlung der neuesten Version könnte dann die Lösung von ukulele von oben verwendet werden:
Wichtig ist aber, dass im Abfragekonstrukt nach dem Channel und der dafür höchsten Version differenziert wird. Denn nicht alle Channel haben immer dieselbe höchste Versionsnummer (siehe Bild des TO und Lösungsvorschlag von @ukulele-7 in seinem zweiten Kommentar).Viele Grüße
HansDampf06
Hi,
und Danke für die angeregte Diskusion und die Hilfestellung.
Zur Version in der Tabelle wie @HansDampf06 das schon anspricht:
So auch diese Datenbank. Ist ja auch ein Microsoft SQL Server der da läuft. Hätte ich eventuell noch dazu erklären sollen. 🤷♂️
Auch jedes Setup das nach Microsoft Windows Installer Best Practice erstellt wurde hält sich an diese Nomenklatur. Selbst Software Identification (SWID) Tagging das nach ISO/IEC 19770-2 aufgebaut ist, berücksichtigt das.
Seit aber Windows Installer XML Einzug gehalten hat bei Microsoft gibt es kein Halten mehr und gefühlt jedes 3. Setup das ich automatisieren muss, darf ich vorher repackagen, weil sich an keinen Windows-Standard gehalten wird.
Das schlimmste das ich je gesehen habe war ein kostenpflichtiges Tool das sich zwar unter %Appdata% installiert, aber trotzdem administrative Rechte benötigte, weil sich der Entwickler offensichtlich nicht mit der NTFS-Rechteverwaltung auseinandergesetzt hatte. Jeder registry-Key landete nämlich explizit in HKLM oder HKCR 🤦♂️🤦♂️ Aus meiner Warte sehr frustrierend...
So! Genug gekotzt! Sorry dafür!
Habe ich noch verstanden, Wollte ich auch machen (siehe), aber dann hat das Know how nicht mehr gereicht um hier was brauchbares zustande zu bringen:
Kannst du mir bitte ein kleines Beispiel, aufbauend auf meine SQL-Query liefern? Ich weiß! Ist schwierig, wenn die DB nicht vorliegt, aber mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
Danke allen!
und Danke für die angeregte Diskusion und die Hilfestellung.
Zur Version in der Tabelle wie @HansDampf06 das schon anspricht:
Zitat von HansDampf06:
Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.
Für jemanden wie mich, der mit der Windows-Welt aufgewachsen ist und Insellösungen in einer Enterprise-Umgebung eher als hinderlich empfindet, ist eines der größten Undinge, Semantic Versioning in die Microsoft-Welt. Buchstaben an 3. oder 4. Stelle als Version? Was ist den schlecht an (siehe Assembly versioning)? Microsoft hat das fest in sein Dot.Net-Framework integriert. Egal ob Powershell, C#, C/C++ die Version Class ist Bestandteil jedes Microsoft-basierenden Systems.Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.
So auch diese Datenbank. Ist ja auch ein Microsoft SQL Server der da läuft. Hätte ich eventuell noch dazu erklären sollen. 🤷♂️
Auch jedes Setup das nach Microsoft Windows Installer Best Practice erstellt wurde hält sich an diese Nomenklatur. Selbst Software Identification (SWID) Tagging das nach ISO/IEC 19770-2 aufgebaut ist, berücksichtigt das.
Seit aber Windows Installer XML Einzug gehalten hat bei Microsoft gibt es kein Halten mehr und gefühlt jedes 3. Setup das ich automatisieren muss, darf ich vorher repackagen, weil sich an keinen Windows-Standard gehalten wird.
Das schlimmste das ich je gesehen habe war ein kostenpflichtiges Tool das sich zwar unter %Appdata% installiert, aber trotzdem administrative Rechte benötigte, weil sich der Entwickler offensichtlich nicht mit der NTFS-Rechteverwaltung auseinandergesetzt hatte. Jeder registry-Key landete nämlich explizit in HKLM oder HKCR 🤦♂️🤦♂️ Aus meiner Warte sehr frustrierend...
So! Genug gekotzt! Sorry dafür!
Habe ich noch verstanden, Wollte ich auch machen (siehe), aber dann hat das Know how nicht mehr gereicht um hier was brauchbares zustande zu bringen:
select
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 1)) AS [Major],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 2)) AS [Minor],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 3)) AS [Build],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 4)) AS [Revision],
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
where
opi.IsProPlusInstalled0 = 1 2. regelmäßiges / getriggertes / .... Aufrufen von INSERT, mit dem die noch nicht in der Tabelle erfassten Versionsnummern (jede nur ein einziges Mal vorhanden!) nachgetragen werden..
BREAK! Da bin ich jetzt ausgestiegen! "Regelmäßiges"? "Getriggertes"?...Kannst du mir bitte ein kleines Beispiel, aufbauend auf meine SQL-Query liefern? Ich weiß! Ist schwierig, wenn die DB nicht vorliegt, aber mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
Zitat von MadMax:
Moin,
wenn die Versionsnummern nicht fest das Format ##.#.#####.##### haben, sondern die einzelnen Nummern unterschiedlich lang sein können...
ist eben nicht der Fall. Ist ganz klar in [Major].[Minor].[Build].[Revision] dargestellt.Moin,
wenn die Versionsnummern nicht fest das Format ##.#.#####.##### haben, sondern die einzelnen Nummern unterschiedlich lang sein können...
...dann kann man in diesem Fall aber eine SQL-Server-Funktion mißbrauchen: parsename. Die ist zwar...
Ich bekomme das einfach nicht gebacken! Wie und wo muss ich das einbauen?Danke allen!
Gar kein Problem! Es geht schließlich um die Klärung DEINER Fragestellung und bisher verläuft die Diskussion ja auch sachlich und respektvoll, wie ich meine.
Ich glaube, das hast Du in Bezug auf das Zitat von mir missverstanden. Dass Microsoft und andere Softwareanbieter diese strukturierte Darstellung der Version nutzen, ist hinsichtlich der hiesigen Diskussion, wie eine konkrete Abfrage zu formulieren ist, eigentlich nebensächlich. Denn, dass die Versionsnummer so gebildet und in der Datenbank auch so abgespeichert wird, ist für die anschließende Auswertung der Daten einfach so als gegeben hinzunehmen.
Nach meinem Dafürhalten spielt es ebenos wenig eine Rolle, ob Du in der Windows-Welt verwurzelt bist oder ob Du den MS SQL Server als Datenbanksystem verwendest. Denn das, was wir hier bisher diskutiert haben, trifft gleichsam auf die anderen SQL-Datenbanksystem (MySQL, MariaDB, PostgreSQL ...) zu. Zwischen diesen Datenbanksystemen bestehen insoweit letztlich nur syntaktische Unterschiede. Aber im Kern ist es bei allen wohl identisch machbar.
Mein Ansatz hat also von all dem losgelöst allein den Blick darauf gerichtet, wie mit den konkret vorliegenden Daten umzugehen ist. Und dabei kommen wir an den Punkt, dass man sich bei der Datenverarbeitung immer vor Augen halten muss, was für Daten von ihrem Typ und von ihren Eigenschaften her eigentlich vorliegen und welche Vor-/Nachteile bestehen. Gerade zwischen Zeichenfolgen und Zahlen bestehen gravierende Unterschiede. Hierfür könntest Du Dir einmal näher anschauen, wie ein Computer ganz allgemein einen Vergleich von Zeichenfolgen im Gegensatz zu Zahlen vornimmt. Das führt über die Betrachtung, wie eine Zeichenfolgen in Bits und Bytes gegenüber einer Zahl für den Computer abgebildet / gespeichert wird.
Das ist der Ausgangspunkt, um das von mir oben gebildete Beispiel des Vergleichs von zwei Versionsnummer nachzuvollziehen. Wir Menschen werden ganz selbstverständlich sagen, dass "16.0.1.23" eine größe Versionsnummer ist als "16.0.1.3" ist, während der Computer beziehungsweise die Aggregatfunktioin MAX('Version') ganz gewiss die "16.0.1.3" als größere Versionsnummer benennen wird. Warum das? ".3" ist eben beim Vergleich von Zeichenketten größer als ".23". Möchte man richtige Ergebnisse, so muss man die Datenbank dazu veranlassen, was wir wie selbstverständlich gedanklich machen: ".3" als ".03" lesen und ".03" ist gewiss für die Datenbank kleiner als ".23".
Wird eine Zeichenkette "3"/"23" in einen Zahlentyp mit dem Wert 3 / 23 umgewandet, so wird auch der Computer / die Datenbank den Zahlenwert 23 immer größer als 3 ansehen und reagiert damit identisch mit unserem menschlichen Verständnis.
Die in Deiner Datenbank abgelegten Versionsnummern sind so, wie sie gespeichert sind, Zeichenketten. Denn der dreifach separierende Punkt ist keine Ziffer, sondern ein sonstiges Zeichen. [Würde es nur ein separierender Punkt sein, könnte die Versionsnummer auch als eine Dezimalzahl behandelt werden. Das ist aber ein anderer Sachverhalt ...]
Diese Problematik der hiesigen Versionsnummern besteht ganz allgemein immer dann, wenn Zahlenstrukturen als String vorliegen und keine feste Datenstruktur/-formatierung haben (so auch von @MadMax gemeint in Deinem Zitat). Um in solchen Fällen korrekte Ergebnisse sicherzustellen, muss man entweder die vorliegende Zeichenkette in ein festes Datenformat umwandeln/überführen oder man splittet die Zeichenkette in ihre Zahlenbestandteile auf, wandelt den jeweiligen Bestandteil in ein Zahlenformat (int, bigint, tiny, ...) um und arbeitet mit den daraus erhaltenen Zahlen weiter.
Wie bereits in der Diskussion dargestellt wurde. stehen für die Umwandlung / Formatierung der als Zeichenkette vorliegenden Versionsnummer die eingebauten Funktionen parsename und STRING_SPLIT (+ weitere String-/Konvertiungsfunktionen) zur Verfügung. Praktisch gibt es jetzt zwei Vorgehensweisen:
1. Immer dann, wenn mit einer Abfrage ein Vergleich der Versionsnummern erfolgen soll/muss, wird die Umwandlung der Versionsnummer ausgeführt, indem die genannten Funktionen eingesetzt werden. Zu dieser Vorgehensweise gehören per se die Lösungsvorschläge von @ukulele-7 und @MadMax.
Im Übrigen kannst Du Dir den Aufwand mit Reverse und Replace sparen, wenn Du bei parsename schlicht und ergreifend in umgekehrter Reihenfolge die Positionzahl angibst - also 4 für Major usw. (siehe Lösungsvorschlag von @MadMax). REPLACE beseitigt überdies genau das, was PARSENAME für das Separieren benötigt: den Punkt (das Bild in denem Eröffnungsbeitrag zeigt Punkte, keine Kommatas!). Zudem hast Du im zweiten Join bei ON einen Schreibfehler: OPC anstelle von OPI
2. Weil diese Umwandlung immer und immer wieder durchgeführt werden müsste, obschon sie immer wieder ein und dasselbe Ergebnis liefert, wäre es doch schlau, die Umwandlung nur ein einziges Mal auszuführen und deren Ergebnis sich zu merken, um später einfach direkt nur noch dieses Ergebnis für Vergleichsoperationen zu verwenden. Die nochmalige Umwandlung entfällt dann. Genau dazu dienen Computed Columns = ein zusätzliches Feld in einer Tabelle, dessen Wert direkt von einem anderen Feld desselben Datensatzes abhängt und immer dann (neu) berechnet wird, wenn der Datensatz erstmals gespeichert oder später modifiziert wird. Die für die 1. Vorgehensweise formulierte Abfrage wird somit dahin abgewandelt, dass die einmal berechneten Ergebnisse als Feld direkt verwendet werden.
Weil Computed Columns nicht im luftleeren Raum existieren können, besteht die Voraussetzung für deren Verwendung darin, dass sie einer bestimmten Tabelle hinzugefügt werden. Deswegen:
Sehr gut!
ABER: Diese zusätzliche Tabelle ist nicht erforderlich, wenn Du eine Computed Column der Tabelle "v_GS_OFFICE_PRODUCTINFO" anfügen kannst. Ich war bei diesem Punkt gedanklich davon ausgegangen, dass diese Tabelle "v_GS_OFFICE_PRODUCTINFO" eine Systemtabelle ist, bei der das nicht möglich wäre.
Ein einfacher Test würde im SQL Studio so gehen:
Wenn SQL Server diese Computed Column der Tabelle hinzufügt, ohne zu meckern, dann müsstest Du, wenn Du Dir die Tabelle solo anzeigen lässt, nunmehr als letzte Spalte diese neue Spalte "Major" sehen können. Ausgehend von dem Bild im Eingangsbeitrag müsste dort dann immer die Zahl 16 zu sehen sein.
Das würde ebenfalls entbehrlich werden, wenn der Tabelle "v_GS_OFFICE_PRODUCTINFO" eine Computed Column beigefügt werden kann.
Alles weitere daher im Anschluss an das Testergebnis und in Abhängigkeit davon!
Das hast Du missverstanden. Es geht bei der Frage nach einem festen Format darum, ob beispielsweise der Bestandteil Build immer aus vier Stellen besteht (=> 0001 <=> 1). Oder was ist, wenn das Build von seiner Zahl her doch einmal fünfstellig werden sollte? Bitte befasse ich Dich einmal mit der Formatierung (der Darstellung) von Zahlen und Datum. Dann wird Dir einiges klarer werden.
Diese beschriebenen Grundsätze gelten übrigens auch bei Excel - hier kann man das sogar sehr schön trocken üben.
Viele Grüße und Gute Nacht
HansDampf06
PS: Ich würde mir für ein sauberes Programmieren angewöhnen, gleiche Dinge immer gleich zu schreiben. Also beispielsweise den Alias OPI entweder immer vollständig klein oder groß, aber nicht bunt gemischt einmal so oder so. Hilfreich ist hier beispielsweise, die Schreibweise stringent zu übernehmen, die SQL Studio bei seinen IntelliSense-Vorschlägen anbietet.
Zur Version in der Tabelle wie @HansDampf06 das schon anspricht:
So auch diese Datenbank. Ist ja auch ein Microsoft SQL Server der da läuft. Hätte ich eventuell noch dazu erklären sollen. 🤷♂️
Auch jedes Setup das nach Microsoft Windows Installer Best Practice erstellt wurde hält sich an diese Nomenklatur. Selbst Software Identification (SWID) Tagging das nach ISO/IEC 19770-2 aufgebaut ist, berücksichtigt das.
Seit aber Windows Installer XML Einzug gehalten hat bei Microsoft gibt es kein Halten mehr und gefühlt jedes 3. Setup das ich automatisieren muss, darf ich vorher repackagen, weil sich an keinen Windows-Standard gehalten wird.
Das schlimmste das ich je gesehen habe war ein kostenpflichtiges Tool das sich zwar unter %Appdata% installiert, aber trotzdem administrative Rechte benötigte, weil sich der Entwickler offensichtlich nicht mit der NTFS-Rechteverwaltung auseinandergesetzt hatte. Jeder registry-Key landete nämlich explizit in HKLM oder HKCR 🤦♂️🤦♂️ Aus meiner Warte sehr frustrierend...
Zitat von HansDampf06:
Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.
Für jemanden wie mich, der mit der Windows-Welt aufgewachsen ist und Insellösungen in einer Enterprise-Umgebung eher als hinderlich empfindet, ist eines der größten Undinge, Semantic Versioning in die Microsoft-Welt. Buchstaben an 3. oder 4. Stelle als Version? Was ist den schlecht an (siehe Assembly versioning)? Microsoft hat das fest in sein Dot.Net-Framework integriert. Egal ob Powershell, C#, C/C++ die Version Class ist Bestandteil jedes Microsoft-basierenden Systems.Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.
So auch diese Datenbank. Ist ja auch ein Microsoft SQL Server der da läuft. Hätte ich eventuell noch dazu erklären sollen. 🤷♂️
Auch jedes Setup das nach Microsoft Windows Installer Best Practice erstellt wurde hält sich an diese Nomenklatur. Selbst Software Identification (SWID) Tagging das nach ISO/IEC 19770-2 aufgebaut ist, berücksichtigt das.
Seit aber Windows Installer XML Einzug gehalten hat bei Microsoft gibt es kein Halten mehr und gefühlt jedes 3. Setup das ich automatisieren muss, darf ich vorher repackagen, weil sich an keinen Windows-Standard gehalten wird.
Das schlimmste das ich je gesehen habe war ein kostenpflichtiges Tool das sich zwar unter %Appdata% installiert, aber trotzdem administrative Rechte benötigte, weil sich der Entwickler offensichtlich nicht mit der NTFS-Rechteverwaltung auseinandergesetzt hatte. Jeder registry-Key landete nämlich explizit in HKLM oder HKCR 🤦♂️🤦♂️ Aus meiner Warte sehr frustrierend...
Ich glaube, das hast Du in Bezug auf das Zitat von mir missverstanden. Dass Microsoft und andere Softwareanbieter diese strukturierte Darstellung der Version nutzen, ist hinsichtlich der hiesigen Diskussion, wie eine konkrete Abfrage zu formulieren ist, eigentlich nebensächlich. Denn, dass die Versionsnummer so gebildet und in der Datenbank auch so abgespeichert wird, ist für die anschließende Auswertung der Daten einfach so als gegeben hinzunehmen.
Nach meinem Dafürhalten spielt es ebenos wenig eine Rolle, ob Du in der Windows-Welt verwurzelt bist oder ob Du den MS SQL Server als Datenbanksystem verwendest. Denn das, was wir hier bisher diskutiert haben, trifft gleichsam auf die anderen SQL-Datenbanksystem (MySQL, MariaDB, PostgreSQL ...) zu. Zwischen diesen Datenbanksystemen bestehen insoweit letztlich nur syntaktische Unterschiede. Aber im Kern ist es bei allen wohl identisch machbar.
Mein Ansatz hat also von all dem losgelöst allein den Blick darauf gerichtet, wie mit den konkret vorliegenden Daten umzugehen ist. Und dabei kommen wir an den Punkt, dass man sich bei der Datenverarbeitung immer vor Augen halten muss, was für Daten von ihrem Typ und von ihren Eigenschaften her eigentlich vorliegen und welche Vor-/Nachteile bestehen. Gerade zwischen Zeichenfolgen und Zahlen bestehen gravierende Unterschiede. Hierfür könntest Du Dir einmal näher anschauen, wie ein Computer ganz allgemein einen Vergleich von Zeichenfolgen im Gegensatz zu Zahlen vornimmt. Das führt über die Betrachtung, wie eine Zeichenfolgen in Bits und Bytes gegenüber einer Zahl für den Computer abgebildet / gespeichert wird.
Das ist der Ausgangspunkt, um das von mir oben gebildete Beispiel des Vergleichs von zwei Versionsnummer nachzuvollziehen. Wir Menschen werden ganz selbstverständlich sagen, dass "16.0.1.23" eine größe Versionsnummer ist als "16.0.1.3" ist, während der Computer beziehungsweise die Aggregatfunktioin MAX('Version') ganz gewiss die "16.0.1.3" als größere Versionsnummer benennen wird. Warum das? ".3" ist eben beim Vergleich von Zeichenketten größer als ".23". Möchte man richtige Ergebnisse, so muss man die Datenbank dazu veranlassen, was wir wie selbstverständlich gedanklich machen: ".3" als ".03" lesen und ".03" ist gewiss für die Datenbank kleiner als ".23".
Wird eine Zeichenkette "3"/"23" in einen Zahlentyp mit dem Wert 3 / 23 umgewandet, so wird auch der Computer / die Datenbank den Zahlenwert 23 immer größer als 3 ansehen und reagiert damit identisch mit unserem menschlichen Verständnis.
Die in Deiner Datenbank abgelegten Versionsnummern sind so, wie sie gespeichert sind, Zeichenketten. Denn der dreifach separierende Punkt ist keine Ziffer, sondern ein sonstiges Zeichen. [Würde es nur ein separierender Punkt sein, könnte die Versionsnummer auch als eine Dezimalzahl behandelt werden. Das ist aber ein anderer Sachverhalt ...]
Diese Problematik der hiesigen Versionsnummern besteht ganz allgemein immer dann, wenn Zahlenstrukturen als String vorliegen und keine feste Datenstruktur/-formatierung haben (so auch von @MadMax gemeint in Deinem Zitat). Um in solchen Fällen korrekte Ergebnisse sicherzustellen, muss man entweder die vorliegende Zeichenkette in ein festes Datenformat umwandeln/überführen oder man splittet die Zeichenkette in ihre Zahlenbestandteile auf, wandelt den jeweiligen Bestandteil in ein Zahlenformat (int, bigint, tiny, ...) um und arbeitet mit den daraus erhaltenen Zahlen weiter.
Wie bereits in der Diskussion dargestellt wurde. stehen für die Umwandlung / Formatierung der als Zeichenkette vorliegenden Versionsnummer die eingebauten Funktionen parsename und STRING_SPLIT (+ weitere String-/Konvertiungsfunktionen) zur Verfügung. Praktisch gibt es jetzt zwei Vorgehensweisen:
1. Immer dann, wenn mit einer Abfrage ein Vergleich der Versionsnummern erfolgen soll/muss, wird die Umwandlung der Versionsnummer ausgeführt, indem die genannten Funktionen eingesetzt werden. Zu dieser Vorgehensweise gehören per se die Lösungsvorschläge von @ukulele-7 und @MadMax.
Wollte ich auch machen (siehe), aber dann hat das Know how nicht mehr gereicht um hier was brauchbares zustande zu bringen:
Schau Dir noch einmal genau die beiden genannten Lösungsvorschläge von @ukulele-7 und @MadMax an. Du benötigst noch das dem SELECT vorgeschaltete WITH-Konstrukt. In diesem WITH-Konstrukt wird nämlich die höchste Versionsnummer des betreffenden Channels ermittelt, um dann bei der eigentlichen Abfrage darauf gestützt eine Auswahl treffen zu können. Dort gehören auch Major etc. hin, sofern Du sie nicht zusätzlich als separaten Wert in der eigentlichen Abfrage darstellen möchtest (ich würde eher denken nein).select
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 1)) AS [Major],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 2)) AS [Minor],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 3)) AS [Build],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 4)) AS [Revision],
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
where
opi.IsProPlusInstalled0 = 1 Im Übrigen kannst Du Dir den Aufwand mit Reverse und Replace sparen, wenn Du bei parsename schlicht und ergreifend in umgekehrter Reihenfolge die Positionzahl angibst - also 4 für Major usw. (siehe Lösungsvorschlag von @MadMax). REPLACE beseitigt überdies genau das, was PARSENAME für das Separieren benötigt: den Punkt (das Bild in denem Eröffnungsbeitrag zeigt Punkte, keine Kommatas!). Zudem hast Du im zweiten Join bei ON einen Schreibfehler: OPC anstelle von OPI
2. Weil diese Umwandlung immer und immer wieder durchgeführt werden müsste, obschon sie immer wieder ein und dasselbe Ergebnis liefert, wäre es doch schlau, die Umwandlung nur ein einziges Mal auszuführen und deren Ergebnis sich zu merken, um später einfach direkt nur noch dieses Ergebnis für Vergleichsoperationen zu verwenden. Die nochmalige Umwandlung entfällt dann. Genau dazu dienen Computed Columns = ein zusätzliches Feld in einer Tabelle, dessen Wert direkt von einem anderen Feld desselben Datensatzes abhängt und immer dann (neu) berechnet wird, wenn der Datensatz erstmals gespeichert oder später modifiziert wird. Die für die 1. Vorgehensweise formulierte Abfrage wird somit dahin abgewandelt, dass die einmal berechneten Ergebnisse als Feld direkt verwendet werden.
Weil Computed Columns nicht im luftleeren Raum existieren können, besteht die Voraussetzung für deren Verwendung darin, dass sie einer bestimmten Tabelle hinzugefügt werden. Deswegen:
1. eine gesonderte Tabelle anlegen, mit der Spalte "Version"...
Habe ich noch verstanden,ABER: Diese zusätzliche Tabelle ist nicht erforderlich, wenn Du eine Computed Column der Tabelle "v_GS_OFFICE_PRODUCTINFO" anfügen kannst. Ich war bei diesem Punkt gedanklich davon ausgegangen, dass diese Tabelle "v_GS_OFFICE_PRODUCTINFO" eine Systemtabelle ist, bei der das nicht möglich wäre.
Ein einfacher Test würde im SQL Studio so gehen:
ALTER TABLE [dbo].[v_GS_OFFICE_PRODUCTINFO] ADD [Major] AS (
CONVERT(int,PARSENAME(ProductVersion0,4))
)
PERSISTED
GO2. regelmäßiges / getriggertes / .... Aufrufen von INSERT, mit dem die noch nicht in der Tabelle erfassten Versionsnummern (jede nur ein einziges Mal vorhanden!) nachgetragen werden..
BREAK! Da bin ich jetzt ausgestiegen! "Regelmäßiges"? "Getriggertes"?...Alles weitere daher im Anschluss an das Testergebnis und in Abhängigkeit davon!
Zitat von MadMax:
wenn die Versionsnummern nicht fest das Format ##.#.#####.##### haben, sondern die einzelnen Nummern unterschiedlich lang sein können...
ist eben nicht der Fall. Ist ganz klar in [Major].[Minor].[Build].[Revision] dargestellt.wenn die Versionsnummern nicht fest das Format ##.#.#####.##### haben, sondern die einzelnen Nummern unterschiedlich lang sein können...
Diese beschriebenen Grundsätze gelten übrigens auch bei Excel - hier kann man das sogar sehr schön trocken üben.
Viele Grüße und Gute Nacht
HansDampf06
PS: Ich würde mir für ein sauberes Programmieren angewöhnen, gleiche Dinge immer gleich zu schreiben. Also beispielsweise den Alias OPI entweder immer vollständig klein oder groß, aber nicht bunt gemischt einmal so oder so. Hilfreich ist hier beispielsweise, die Schreibweise stringent zu übernehmen, die SQL Studio bei seinen IntelliSense-Vorschlägen anbietet.
Zitat von @mayho33:
...mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
Es ist erstmal ein bisschen verwirrend als SQL Anfänger weil man es einfach nicht gut in einer Abfrage allein unterbingen kann. Dadurch erscheint es erstmal unnötig kompliziert. Sobald man aber mit zwei in einander geschachtelten Abfragen arbeitet ist es relativ einfach möglich....mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
Variante #1a:
WITH t AS (
select
ROW_NUMBER() OVER (PARTITION BY sys.Name0,opi.Channel0 ORDER BY opi.ProductVersion0 DESC) AS zeile,
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 1)) AS [Major],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 2)) AS [Minor],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 3)) AS [Build],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 4)) AS [Revision],
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
where
opi.IsProPlusInstalled0 = 1
)
SELECT t.* FROM t WHERE t.zeile = 1SELECT t.* FROM (
select
ROW_NUMBER() OVER (PARTITION BY sys.Name0,opi.Channel0 ORDER BY opi.ProductVersion0 DESC) AS zeile,
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 1)) AS [Major],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 2)) AS [Minor],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 3)) AS [Build],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 4)) AS [Revision],
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
where
opi.IsProPlusInstalled0 = 1
) t
WHERE t.zeile = 1PS: Ich habe noch den Hostname berücksichtigt, ich glaube du willst tatsächlich nicht nur nach Channel gruppieren.
Und es gibt andere Möglichkeiten, Variante #2 arbeitet mit einem gejointen Aggregat. Das ist vom Prinzip her recht simpel aber kann schnell viel Code bedeuten und ist daher schwieriger zu überblicken und anzupassen:
select
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 1)) AS [Major],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 2)) AS [Minor],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 3)) AS [Build],
REVERSE(PARSENAME(REPLACE(REVERSE(opi.ProductVersion0), ',', '.'), 4)) AS [Revision],
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
INNER JOIN (
select
sys.Name0 as 'Hostname',
opi.Channel0 as 'Channel',
max(opi.ProductVersion0) as 'Version',
opi.IsProPlusInstalled0
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
GROUP BY sys.Name0,opi.Channel0,opi.IsProPlusInstalled0
) t
ON sys.Name0 = t.Hostname
AND opi.Channel0 = t.Channel
AND opi.ProductVersion0 = t.[Version]
AND opi.IsProPlusInstalled0 = t.opi.IsProPlusInstalled0
where
opi.IsProPlusInstalled0 = 1Außerdem sortiere ich immer Textwerte, da gibt es einiges zu beachten. Z.B. wenn die Versionsnummern unterschiedlich viele Stellen haben oder irgendeine "interessante" Collation verwendet wird. Das Problem sollte man aber gesondert betrachten und auch lösen.
Zitat von @ukulele-7:
Ja, das stimmt schon. Aber das gilt doch immer: Aller Anfang ist schwer! Und für das Erstellen von (sinnvollen) SQL-Abfragen genügt es meist nicht, irgendwie ein Abfrageziel zu erreichen, sondern man muss sich mit den zu verarbeitenden Daten auseinandersetzen und von deren Eigenschaften ausgehend dann die Abfrage aufbauen. Und hier werden SQL-Konstrukte relativ schnell (sehr) komplex. Ohnehin stellt die SQL-gestützte Datenverarbeitung eine gesteigerte Abstraktion dar, die mit steigender Komplexität zusätzlich zunimmt. Die nächste Steigerungsstufe sind dann Abfrageergebnisse, die nur mehrschrittig zu generieren sind und daher den Einsatz von Funktionen / Prozeduren erfordern. Jedoch eröffnen sich dann auch erst Möglichkeiten, die mit einem reinen Abfragekonstrukt niemals erreichbar wären. Das beispielsweise unterscheidet MS Access von MS SQL Server - MS Access kennt diese Möglichkeiten eben nicht (sofern es sich nicht geändert haben sollte und Makros einmal außen vorgelassen)....mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
Es ist erstmal ein bisschen verwirrend als SQL AnfängerZur Komplexität von SQL-Abfragen trägt außerdem bei, dass es ja nicht nur darum geht, eine Abfrage hinzuklatschen. Denn man kann auch mit einfachen, aber wenig sinnvollen Abfragen die SQL-Datenbank kräftig beschäftigen. Es geht somit immer um eine performante Abfragegestaltung. Und hier sind SQL-Abfragen ganz schnell alles andere als etwas fürs Vorbeigehen.
Deswegen erscheint
Außerdem sortiere ich immer Textwerte,
als eine Faustregel oder ein generelles Herangehen für mich eher bedenklich, auch wenn es schön einfach erscheinen mag. Denn Sortieroperationen sind aufwendige und zeitraubende Vorgänge für die Datenbankengine. Nicht ohne Grund ist in UNION-Konstrukten die ORDER-BY-Klausel einmalig und nur am Ende des Konstrukts zulässig. Und wer einmal eine (umfangreichere) Abfrage mit und ohne ORDER-BY-Klausel verglichen hat, kennt den deutlichen Unterschied.Der Grundsatz muss meiner Meinung nach vielmehr lauten, dass ORDER-BY-Klauseln so weit wie möglich zu vermeiden sind, wenn es nicht wirklich darauf ankommt. Das gilt erst recht bei der Sortierung von Stringwerten: Alle Stringoperationen (nicht nur Sortierungen) sind immer rechenintensive und zeitaufwendige Operationen - das liegt in der Natur von Zeichenketten und darin, wie der Computer sie computertechnisch darstellt, begründet und ist somit unumgänglich / zwingend.
Hieraus folgt ersichtlich ein ganz entscheidender Nachteil der ROW_NUMBER()-OVER-Klausel, weil sie bei SQL Server immer ein ORDER BY erfordert. Kann ich das für dasselbe Abfrageergebnis (geschickt) vermeiden, bin ich immer um einiges schneller. Und das ist nur ein Aspekt, der unter
da gibt es einiges zu beachten.
fällt. Ein weiterer Aspekt in diesem Zusammenhang betrifft die Kerneigenschaft eines relationalen Datenbanksystems: Indizes und JOIN-Verknüpfungen. Ist eine Datenbank sinnvoll entworfen, wird sie sich insbesondere an diesem Aspekt streng orientieren. Denn Indizes sind per se immer schon sortiert, so dass dann, wenn ein Index verwendet werden kann, zumeist gar keine ORDER-BY-Klauseln mehr erforderlich sind, weil die Abfrage bereits auf sortierte Daten zugreift.Wie gesagt genügt es aus meiner Sicht nicht, einfach nur irgendeine SQL-Abfrage aufschreiben zu können. Eine performante Datenbank und deren Leistungsoptimierung ist eben der schwierigste Teil der Administration und fängt bereits bei der Datenbankplanung/-struktur an und setzt sich entsprechend bis ins Detail der Datenbanknutzung fort. Hierfür kann man sich eine Arbeitsweise aneignen, die per se immer auf eine Leistungsoptimierung ausgerichtet ist. Dafür ist letztlich sehr viel Verständnis und Einfallsreichtum gefragt, die Möglichkeiten eines Datenbanksystems nicht nur stumpf anzuwenden, sondern kreativ und zielorientiert auszuschöpfen. Der Vorschlag von @MadMax hinsichtlich des "Missbrauchs" von PARSENAME ist ein solches beispielhaftes Ausschöpfen, wenn die Verwendung über den eigentlich gedachten Anwendungsfall hinausgeht ...
Warum sehe ich das alles so? Wer beispielsweise ein datenbankgestütztes Webfrontend benutzt, weiß, dass man ungern auf die Datenbank im Hintergrund warten möchte. Dieses Echtzeitproblem ist nur angemessen zu lösen, wenn die gesamte Datenbank auf stringente Leistungsoptimierung und schnellstmögliche Abfrageausführung ausgerichtet ist/wird.
Vor diesem Hintergrund wird umso verständlicher, warum mein weiterführender Ansatz mit Computed Columns als am Ende vorzugswürdig erscheint. Gleichwohl ist es zutreffend, dass dieser Ansatz
neuen Werte in Form von Spalten oder Tabellen erzeugen
beinhaltet und damit ganz klar nicht unterich nenne das jetzt mal "minimal invasiv"
fällt. Deswegen setzt mein Ansatz zwingend voraus, dass entweder (1. = bessere Variante) der genannten Tabelle die Computed Columns hinzugefügt werden können oder (2.) wenigstens eine zusätzliche Tabelle für die Werte des Versionsstrings nebst den Computed Columns erstellt werden können. Ist keines von beiden möglich, ist mein weiterführender Ansatz per se aus dem Rennen!Für die beiden Varianten ist übrigens zu berücksichtigen, auf welcher Basis diese Datenbank des TO beruht. Wenn es sich um die Datenbank aus einer genutzten Anwendung handelt, so muss bedacht werden, wie die Anwendung bei Updates mit der Datenbank und deren Struktur umgeht. Das könnte der 1. Variante der direkten Hinzufügung von Computed Columns entgegenstehen. Dann bleibt selbstredend nur die 2. Variante.
Wenn der TO seine gewünschte Abfrage aufgrund entsprechender Zugriffsrechte direkt in der Datenbank ablegen kann und nicht bloß von außen auf die beteiligten Tabellen per (dynamischem) SQL zugreift, sollte mindestens die zweite Variante umsetzbar sein.
Ich glaube auch nicht das das wirklich zu Problemen in Sachen Geschwindigkeit führen würde
Das halte ich eher für eine Illusion. Es mag ja richtig sein, dass sich die Geschwindigkeitsunterschiede bei kleineren Datenmengen noch nicht so gravierend bemerkbar machen. Wir reden hier aber über eine Datenbank, bei der es um eine Vielzahl von Hosts (Hunderte?) mit jeweils einer Vielzahl von Channels pro Host (zwei- bis dreistellig?) und diese obendrein auch noch mit einer Vielzahl von Versionsnummern (zwei- bis vierstellig?) geht - oder anders ausgedrückt reden wir wahrscheinlich von einer Datensatzanzahl von 500.000 bis weit in den zwei- oder gar dreistelligen Millionenbereich, wenn nicht sogar darüber hinaus. Bei einer solchen Datensatzmenge wird jede (vermeidbare) Leistungsbremse mehr als nur spürbar sein - der Leistungsbedarf von Stringoperationen (e)skaliert dabei erheblich. Es mag sein, dass die potente Hardware des Servers selbst bei diesem Datenumfang für ein einigermaßen schnelles Abfrageergebnis sorgt. Aber eine optimierte Abfragegestaltung wird dennoch unvergleichbar schneller sein. Immerhin wird die Hardware / Datenbank ja wohl nicht darauf warten, wann denn nun endlich einmal eine Abfrage abzuarbeiten ist, sondern eine gute Grundauslastung aufweisen - dann kann sich eine Abfrage wie die hier diskutierte Abfrage für die Dauer ihrer Ausführung als ein erheblicher Ressourcenfresser erweisen.Mithin wage ich die Prognose, dass der einfache Lösungsansatz bei der Abarbeitung eine nicht unerhebliche Abarbeitungsdauer aufweisen wird. Mein weiterführender Ansatz sollte demgegenüber um ein Vielfaches schneller sein können. Selbst eine nur doppelte Ausführungsgeschwindigkeit wird in zeitlicher Hinsicht greifbar (erwartbar wohl viele Sekunden) schneller sein.
Letztendlich kommt es also darauf an, ob dem TO in seiner Datenbank die Verwendung von Computed Columns in einer der beiden genannten Varianten möglich ist und ob er das nach der Unternehmensdoktrin überhaupt möchte sowie ob die Supportbedingungen des Softwareanbieters es zulassen. Ansonsten muss der TO bei dem einfachen Lösungsansatz verbleiben.
Viele Grüße
HansDampf06
Hallo Mayho,
probier mal das:
Was habe ich gemacht?
Deine Abfrage wurde um die "Zeile" ergänzt und in eine Unterabfrage aufgenommen. In der Hauptabfrage filtere ich dann nur noch auf die Zeile 1, das ist dann der Datensatz mit der höchsten Version.
Zur Zeile:
Hinter "partition by" steht Deine Gruppierung, d.h. die Gruppe, für die Du die höchste Version ermitteln willst. Du hast den Hostnamen genannt, ich kann mir vorstellen, daß da noch mindestens der Produktname interessant ist. Ist es nur der Hostname, dann muß bei "partition by" nur "sys.Name0" stehen, sind es Hostname und Produktname, dann wären es "sys.Name0, opi.ProductName0". Was Du nicht in der Gruppe brauchst, löschst Du raus, brauchst Du noch anderes in der Gruppe, nimmst Du es in die Liste dazu.
Hinter "order by" stehen die einzelnen Komponenten der Versionsnummer, absteigend sortiert. So bekommt die höchste Version die Zeilennr. 1 und wird dann Zeile für Zeile hochgezählt.
Du willst dann nur die höchste Versionsnr. haben, also filterst Du in der äußeren Abfrage dann nach Zeilennr. 1 und hast Dein erwünschtes Ergebnis.
So, ich hoffe, das war jetzt verständlich
Gruß, Mad Max
probier mal das:
select *
from (
select
row_number () over (
partition by sys.Name0,
OPI.Architecture0,
opi.Channel0,
opi.LicenseState,
opi.ProductName0
order by convert (int, parsename (opi.ProductVersion0, 4)) desc,
convert (int, parsename (opi.ProductVersion0, 3)) desc,
convert (int, parsename (opi.ProductVersion0, 2)) desc,
convert (int, parsename (opi.ProductVersion0, 1)) desc
) as Zeile,
sys.Name0 as 'Hostname',
OPI.Architecture0 as 'OfficeArch',
opi.Channel0 as 'Channel',
opi.LicenseState0 as 'LicState',
opi.ProductName0 as 'ProdName',
opi.ProductVersion0 as 'Version',
OPC.AutoUpgrade0 as 'Auto Upgrade',
OPC.CCMManaged0 as 'CCM Managed',
OPC.GPOChannel0 as 'GPOChannel',
OPC.GPOOfficeMgmtCOM0 as 'GPOOfficeMgmtCOM',
opc.UpdatesEnabled0 as 'UpdatesEnabled'
from v_R_System sys
inner join v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC on sys.ResourceID=OPC.ResourceID
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
where
opi.IsProPlusInstalled0 = 1
) t
where Zeile = 1Was habe ich gemacht?
Deine Abfrage wurde um die "Zeile" ergänzt und in eine Unterabfrage aufgenommen. In der Hauptabfrage filtere ich dann nur noch auf die Zeile 1, das ist dann der Datensatz mit der höchsten Version.
Zur Zeile:
row_number () over (
partition by sys.Name0,
OPI.Architecture0,
opi.Channel0,
opi.LicenseState,
opi.ProductName0
order by convert (int, parsename (opi.ProductVersion0, 4)) desc,
convert (int, parsename (opi.ProductVersion0, 3)) desc,
convert (int, parsename (opi.ProductVersion0, 2)) desc,
convert (int, parsename (opi.ProductVersion0, 1)) desc
) as Zeile,Hinter "partition by" steht Deine Gruppierung, d.h. die Gruppe, für die Du die höchste Version ermitteln willst. Du hast den Hostnamen genannt, ich kann mir vorstellen, daß da noch mindestens der Produktname interessant ist. Ist es nur der Hostname, dann muß bei "partition by" nur "sys.Name0" stehen, sind es Hostname und Produktname, dann wären es "sys.Name0, opi.ProductName0". Was Du nicht in der Gruppe brauchst, löschst Du raus, brauchst Du noch anderes in der Gruppe, nimmst Du es in die Liste dazu.
Hinter "order by" stehen die einzelnen Komponenten der Versionsnummer, absteigend sortiert. So bekommt die höchste Version die Zeilennr. 1 und wird dann Zeile für Zeile hochgezählt.
Du willst dann nur die höchste Versionsnr. haben, also filterst Du in der äußeren Abfrage dann nach Zeilennr. 1 und hast Dein erwünschtes Ergebnis.
So, ich hoffe, das war jetzt verständlich
Gruß, Mad Max
Zitat von @HansDampf06:
Gar kein Problem! Es geht schließlich um die Klärung DEINER Fragestellung und bisher verläuft die Diskussion ja auch sachlich und respektvoll, wie ich meine.
Vielen Dank! Ich denke auch, dass das so ist und wie meine Mom immer zu sagen pflegte: "So wie man in den Wald ruft, kommt es auch wieder zurück!", sprich, mit Höflichkeit und Respekt kommt man immer weiter.Gar kein Problem! Es geht schließlich um die Klärung DEINER Fragestellung und bisher verläuft die Diskussion ja auch sachlich und respektvoll, wie ich meine.
Zitat von @mayho33:
Zur Version in der Tabelle wie @HansDampf06 das schon anspricht:
Zur Version in der Tabelle wie @HansDampf06 das schon anspricht:
Zitat von HansDampf06:
Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.
Für jemanden wie mich, der mit der Windows-Welt aufgewachsen...Hast Du Dir einmal die Struktur der Versionsnummern angesehen? Sie hat auf der Abbildung des TO die Hauptversion (= 16), das aktuelle Release (= 0), das Update (z.B. 13929) und eine Unternummer. Das schreit doch regelrecht danach, dies sinnvoll zu (be)nutzen.
Ich glaube, das hast Du in Bezug auf das Zitat von mir missverstanden. Dass Microsoft und andere Softwareanbieter diese strukturierte Darstellung der Version nutzen, ist hinsichtlich der hiesigen Diskussion, wie eine konkrete Abfrage zu formulieren ist, eigentlich nebensächlich. [...]
...Wir Menschen werden ganz selbstverständlich sagen, dass "16.0.1.23" eine größe Versionsnummer ist als "16.0.1.3" ist, während der Computer beziehungsweise die Aggregatfunktioin MAX('Version') ganz gewiss die "16.0.1.3" als größere Versionsnummer benennen wird. Warum das? ".3" ist eben beim Vergleich von Zeichenketten größer als ".23".
Ich kann mir nur vorstellen, dass MAX() einen String charakterweise von links nach recht, auf Basis der ASCII-Codierung vergleicht. und beim ersten Unterschied beendet. So ließe sich auch erklären, dass die Version 16.0.16127.20540 als niedriger darstellt wird als 16.0.4477.25.Eine Entsprechung der Dot.Net-Framework | Version.CompareTo Methode gibt es in SQL vermutlich nicht? Sowas kommt doch sicher ganz oft vor, dass eine Version aus einer History-Tabelle gezogen werden muss oder nicht?
Weil Computed Columns nicht im luftleeren Raum existieren können, besteht die Voraussetzung für deren Verwendung darin, dass sie einer bestimmten Tabelle hinzugefügt werden. Deswegen:
Sehr gut!
ABER: Diese zusätzliche Tabelle ist nicht erforderlich, wenn Du eine Computed Column der Tabelle "v_GS_OFFICE_PRODUCTINFO" anfügen kannst. Ich war bei diesem Punkt gedanklich davon ausgegangen, dass diese Tabelle "v_GS_OFFICE_PRODUCTINFO" eine Systemtabelle ist, bei der das nicht möglich wäre.
Ich bin mir jetzt nicht sicher was du genau mit "Systemtabelle" meinst. Nach meiner Erfahrung ist die gesamte SCCM-Datenbank "SYSTEM...". Ein falscher Eingriffe und schon herrscht Chaos. Entweder weil die Daten nicht mehr stimmen oder weil was nicht mehr funktioniert. Da greifen einfach zu viele Services drauf zu um auch noch das unvorhersehbarste Glied in der Kette, den Menschen, kompensieren zu können. 🙈🙈1. eine gesonderte Tabelle anlegen, mit der Spalte "Version"...
Habe ich noch verstanden,ABER: Diese zusätzliche Tabelle ist nicht erforderlich, wenn Du eine Computed Column der Tabelle "v_GS_OFFICE_PRODUCTINFO" anfügen kannst. Ich war bei diesem Punkt gedanklich davon ausgegangen, dass diese Tabelle "v_GS_OFFICE_PRODUCTINFO" eine Systemtabelle ist, bei der das nicht möglich wäre.
PS: Ich würde mir für ein sauberes Programmieren angewöhnen, gleiche Dinge immer gleich zu schreiben. Also beispielsweise den Alias OPI entweder immer vollständig klein oder groß, aber nicht bunt gemischt einmal so oder so. Hilfreich ist hier beispielsweise, die Schreibweise stringent zu übernehmen, die SQL Studio bei seinen IntelliSense-Vorschlägen anbietet.
Eigentlich ist ist mir Intellisense aus der objektorientierten Programmierung nicht unbekannt. Visual-Studio, VS Code, Powershell ISE... Verwenden alle Intellisense. SSMS sollte eigentlich automatisch die Groß-Kleinschreibung anpassen mit STRG-Leertaste. Aber ich glaube ich habe das Beispiel zusammenkopiert.Und da fällt mich auch gleich ein Fehler auf:
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
-- sollte lauten:
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPI.ResourceIDAuf jeden Fall Danke @HansDampf06! Das war alles sehr aufschlussreich. 😊
Zitat von ukulele-7
Danke! Danke! Danke fürs Ergänzen für Dummies! Jetzt habe ich es auch verstanden. Zumindest syntaktisch. 😅 Ich wusste einfach nicht wo ich was einbauen muss. Semantisch ist SQL für mich (noch) einfach ein Buch mit vielen Siegeln.Zitat von @mayho33:
...mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
Es ist erstmal ein bisschen verwirrend als SQL Anfänger weil man es einfach nicht gut in einer Abfrage allein unterbingen kann. Dadurch erscheint es erstmal unnötig kompliziert. Sobald man aber mit zwei in einander geschachtelten Abfragen arbeitet ist es relativ einfach möglich....mit z.B. dem Beispiel von @ukulele-7 (übrigens danke dafür @ukulele-7) konnte ich nicht viel anfangen bzw. brachte es nicht zum Laufen, obwohl es mir genial erscheint.
@MadMax: Auch dir mein Dank für das Beispiel! Ich werde wohl erst am Montag Rückmeldung geben.
Euch allen einen schönen Abend und ein schönes Wochenende!
Mayho
Aufgrund des letzten Kommentars von @MadMax sind mir die hier diskutierte Fragestellung und die von mir formulierten Gedanken / Anforderungen nochmals durch den Kopf gegangen. Denn ungeachtet dessen, ob mein weiterführender Ansatz mit den Computed Columns überhaupt zielführend / durchführbar ist, hat es mit den Computed Columns nicht sein Bewenden. Vielmehr muss das am Ende in eine geeignete Abfrage gegossen werden.
Deshalb will ich mich dem eigentlichen Abfrageproblem - ausgegeben werden soll die jeweils höchste Versionsnummer einer Ressource - zunächst völlig losgelöst von den Computed Columns anhand der abzufragenden Datensätze nähern. Ausgangspunkt ist zunächst, dass drei Tabellen über einen JOIN beteiligt sind. Beide JOIN-Konstrukte stellen auf dasselbe Verknüpfungselement ab: ResourceID. Dieses Feld ResourceID ist in allen drei Tabellen vorhanden. Aber die minimale Einbindung der Tabelle v_R_System (aka sys) legt nahe, dass ResourceID das Synonym für den Host / Client / Server / ... ist.
Außerdem wird ersichtlich, dass die relevante höchste Versionsnummer wohl ausschließlich durch die Datensätze der Tabelle OPI determiniert ist.
Daraus kann ich ableiten, dass es geboten erscheint, in der PARTITION-BY-Klausel "sys.Name0" durch "OPI.ResourceID" zu ersetzen. Denn hierdurch beschränkt sich die Abarbeitung des ROW_NUMBER-Konstrukts auf eine einzige Tabelle, nämlich OPI, und muss nicht implizit auch noch einen JOIN zur Tabelle "sys" berücksichtigen. Das verringert den Abarbeitungsaufwand und beschleunigt die Abfrage.
Also kommt es zunächst ausschließlich auf die Tabelle OPI an.
Die nächste Frage ist, ob wie bei @MadMax alle Felder der Tabelle OPI zur zutreffenden Ermittlung der höchsten Versionsnummer notwendig sind. Hier hatte nämlich @ukulele-7 lediglich den Hostnamen als (mindestens) erforderlich erkannt, also das Feld ResourceID (siehe zuvor). Sind die anderen Felder erforderlich? Darauf kommen wir später zurück, wenn wir das Problem der Ermittlung der höchsten Versionsnummer unter Vermeidung einer ORDER-BY-Sortierung im Kern gelöst haben.
Erforderlich ist in jedem Fall - bezogen auf eine bestimmte Versionsnummer -, dass der Zusammenhang von Major-Release-Update-Revision durchgehend erhalten bleibt. Deshalb würde etwas wie
komplett in die Irre führen. Wie bei @MadMax mit der ORDER-BY-Klausel muss die Verarbeitung einheitlich zur gleichen Zeit erfolgen. Folglich kann das nur über
erfolgen. x ist dabei diejenige Zehnerpotenz, deren Anzahl an Null der größtmöglichen Anzahl von Stellen der Nummer für Release entspricht. Wegen der relativ kurzen "Innovations"zyklen zwischen den Majorversionen ist es eher weniger wahrscheinlich, dass die Stellenzahl mehr als Zwei betragen könnte. Daher x = 100. Weil innerhalb einer Majorversion wahrscheinlich mehrere Releaseversionen veröffentlicht werden, kann davon ausgegangen werden, dass die Nummer der Updateversionen den vierstelligen Bereich nicht verlassen wird. Gleichwohl kann aus Sorgfaltsgründen auf einen fünfstelligen Ansatz abgestellt und somit y = 100000 gesetzt werden. Ähnliches würde hinsichlich der Revisionsnummern und somit für z = 100000 zugrundeliegen. Also würden die Versionsnummern transformiert in eine Zahl bei 1|00|0.000.0|00.000 starten (Vorserien mit Major = 0 sind an dieser Stelle unbeachtlich).
Der Zahlentyp INT hat einen Wertebereich bis 2,147,483,647, so dass dieser Zahlentyp nicht ausreicht, um die umgewandelte Versionsnummer als Zahl abzubilden. Dann bleibt nur BIGINT mit einem Wertebereich bis 9,223,372,036,854,775,807, was in jedem Fall mehr als ausreichend ist. Hierdurch sind wir uns sicher, dass eine Darstellung der Versionsnummer als Zahl jederzeit vollständig abbildbar ist.
Daraus können wir jetzt unser vorbereitendes SQL-Konstrukt für das spätere MAX-Aggregat erstellen:
Die GROUP-BY-Auswahl in einer Unterabfrage stellt sicher, dass für jede Major-Release-Update-Revision-Kombination auch dann, wenn sie mehrfach vorkommt, die lahme Berechnung für VersNummer nur ein einziges Mal ausgeführt wird.
Dem folgt jetzt das SQL-Konstrukt für die Auswahl desjenigen Datensatzes eines Channels, der die höchste Versionsnummer hat:
Betreffend die bereits hier berücksichtigte WHERE-Klausel wäre zu prüfen, ob das möglicherweise zu einer ungewollten Gestaltung der Abfrageergebnisse führen könnte, weshalb die Klausel erst im Gesamt-SQL-Konstrukt zu verwenden wäre. Ich denke aber eher nicht.
[...,] ist der Platzhalter für diejenigen weiteren Felder, die bei der Ermittlung der höchsten Versionsnummer als Differenzierungsmerkmal zwingend berücksichtigt werden müssen. Das muss natürlich für diese weiteren Felder genau überlegt werden. Dann sind diese weiteren Felder sowohl oben beim SELECT als auch unten bei der GROUP-BY-Klausel anzugeben.
Beispielsweise das weitere Feld Architectur0 - (eher) nein, weil wohl eine aktuelle Installation des Hosts nur auf "x64" lauten wird. Aber selbst dann, wenn ein Produkt der x32-Gruppe angehören würde, würde es beim Office wohl nicht zugleich als x64-Version vorhanden sein können. Das dürfte sich bei den Officeprodukten auf ein und dem selben Host wohl ausschließen. Also wäre dieses Feld für die Bestimmung der höchsten Versionsnummer unbeachtlich. Gleichwohl muss das sachlich genau geprüft werden!
Natürlich muss an dieser Stelle berücksichtigt werden, was tatsächlich als Abfrageergebnis gewünscht ist. Denn wenn hier alle weiteren Felder berücksichtigt werden, die später im Abfrageergebnis darzustellen sind, kann sich der spätere Abfrageausdruck vereinfachen. Für die weitere Betrachtung unterstelle ich, dass nicht alle weiteren Felder des späteren Abfrageergebnisses für die Ermittlung der höchsten Versionsnummer herangezogen werden sollen / dürfen.
EDIT:
Das kommt davon, wenn man etwas im Trockenen (aka nur im Kopf) entwirft und es nicht auch ausprobiert / prüft. Alle Felder von OPI mit Ausnahme von ProductVersion0 müssen hinter dem SELECT und bei GROUP BY angegeben werden. An die Stelle von ProductVersion0 tritt MAX(ProductVersion0). Die HAVING-Klausel entfällt. Demgemäß sind auch nicht die weiteren Felder, die vorstehend durch [...,] symbolisiert sind, zu diskutieren, sondern schlichtweg erforderlich, soweit sie für spätere Filterungen und die Ausgabe im Gesamt-SQL-Konstrukt benötigt werden. Die SELECT- und die GROUP-BY-Zeilen ändern sich vorstehend zu
Der Gedankenansatz über die HAVING-Klausel als Filter ist somit nicht möglich und auch nicht erforderlich. Um im Abfrageergebnis des Gesamt-SQL-Konstrukts den Versionsstring des höchsten Werts von ProductVersion0 ausgeben zu können, muss nochmals MAX(VersNummer) mit ProductVersion0 in einen Zusammenhang gebracht werden. Das heißt, wir benötigen das obige SELECT-Konstrukt zur Berechnung von VersNummer ein zweites Mal. Jetzt könnte dafür an die Auslagerung in ein vorangestelltes WITH-Statement gedacht werden. Das scheint aber, weil das SELECT-Konstrukt in einer Unterabfrage benötigt wird, für die Abfrageausführung keine gute Idee zu sein, sondern erheblich mehr Zeit zu benötigen, als dieses SELECT-Konstrukt an den beiden benötigten Stellen mehrmals einzufügen. Der Zeitunterschied bei der hiesigen Simulation war immerhin 1:2. Das wiederum bestätigt ein weiteres Mal meine vielfältige Erfahrung, dass SQL Server bei der Auslagerung von Unterabfragen in separate Konstrukte / Views etc. deutliche Leistungseinbußen zeigt gegenüber des direkten (mehrmaligen) Einbaus der Unterabfrage in das Gesamtkonstrukt.
Somit kommen wir zum Gesamt-SQL-Konstrukt für die Abfrage:
[WHERE ...]: Hier können zusätzliche Filter eingebaut werden, wenn ein Bedarf dazu besteht
[ORDER BY ...]: An dieser Stelle ist diese Klausel dann unverzichtbar, wenn das abschließende Abfrageergebnis geordnet ausgegeben werden soll. Das hat aber mit einer ORDER-BY-Klausel zur Ermittlung der höchsten Versionsnummer nichts gemein.
Nach meiner Erfahrung und meinem darauf gestütztem Dafürhalten, wäre das Gesamt-SQL-Konstrukt einigermaßen leistungsoptimiert und wenn dann auch noch die Indizes auf die relevanten Felder sinnvoll gesetzt sind, ist das eine runde Sache. Weitere Optimierungen können nur anhand der Ausführung der Abfrage am lebenden Organismus hinreichend beurteilt werden - für mich wäre das aber ein Blick durch eine trübe Glaskugel.
Wie würde sich jetzt das Gesamt-SQL-Konstrukt verändern, wenn Computed Columns zum Einsatz kommen? Ich würde die Berechnung des Feldes VersNummer in eine Computed Column packen, weil das die größtmöglich Einsparung und Beschleunigung im vorliegenden Fall mit sich bringt. Die (zusätzliche) vierteilige Aufteilung der Versionsnummer würde als Option anderen Anwendungsfällen oder besonderen Filtermomenten vorbehalten bleiben. Das Gesamt-SQL-Konstrukt verkürzt sich demgemäß wie folgt:
Das ist doch äußerst griffig.
Die vorstehenden SQL-Konstrukte demonstrieren, wie eine strukturierte Formulierung dessen aussehen kann. Du wirst nämlich höchstwahrscheinlich feststellen, dass deren Struktur relativ leicht erfassbar ist, obschon es nicht aus Deiner Feder stammt ... Wird in SQL Server ein derart strukturiertes SQL-Konstrukt persistent abgelegt, dann behält SQL Server diese Struktur sogar bei.
Viele Grüße
HansDampf06
Deshalb will ich mich dem eigentlichen Abfrageproblem - ausgegeben werden soll die jeweils höchste Versionsnummer einer Ressource - zunächst völlig losgelöst von den Computed Columns anhand der abzufragenden Datensätze nähern. Ausgangspunkt ist zunächst, dass drei Tabellen über einen JOIN beteiligt sind. Beide JOIN-Konstrukte stellen auf dasselbe Verknüpfungselement ab: ResourceID. Dieses Feld ResourceID ist in allen drei Tabellen vorhanden. Aber die minimale Einbindung der Tabelle v_R_System (aka sys) legt nahe, dass ResourceID das Synonym für den Host / Client / Server / ... ist.
Außerdem wird ersichtlich, dass die relevante höchste Versionsnummer wohl ausschließlich durch die Datensätze der Tabelle OPI determiniert ist.
Daraus kann ich ableiten, dass es geboten erscheint, in der PARTITION-BY-Klausel "sys.Name0" durch "OPI.ResourceID" zu ersetzen. Denn hierdurch beschränkt sich die Abarbeitung des ROW_NUMBER-Konstrukts auf eine einzige Tabelle, nämlich OPI, und muss nicht implizit auch noch einen JOIN zur Tabelle "sys" berücksichtigen. Das verringert den Abarbeitungsaufwand und beschleunigt die Abfrage.
Also kommt es zunächst ausschließlich auf die Tabelle OPI an.
Die nächste Frage ist, ob wie bei @MadMax alle Felder der Tabelle OPI zur zutreffenden Ermittlung der höchsten Versionsnummer notwendig sind. Hier hatte nämlich @ukulele-7 lediglich den Hostnamen als (mindestens) erforderlich erkannt, also das Feld ResourceID (siehe zuvor). Sind die anderen Felder erforderlich? Darauf kommen wir später zurück, wenn wir das Problem der Ermittlung der höchsten Versionsnummer unter Vermeidung einer ORDER-BY-Sortierung im Kern gelöst haben.
Erforderlich ist in jedem Fall - bezogen auf eine bestimmte Versionsnummer -, dass der Zusammenhang von Major-Release-Update-Revision durchgehend erhalten bleibt. Deshalb würde etwas wie
... MAX([Major]), MAX([Release]), MAX([Update]), MAX([Revision]) ... komplett in die Irre führen. Wie bei @MadMax mit der ORDER-BY-Klausel muss die Verarbeitung einheitlich zur gleichen Zeit erfolgen. Folglich kann das nur über
... MAX((([Major]*x+[Release])*y+[Update])*z+[Revision]) ... erfolgen. x ist dabei diejenige Zehnerpotenz, deren Anzahl an Null der größtmöglichen Anzahl von Stellen der Nummer für Release entspricht. Wegen der relativ kurzen "Innovations"zyklen zwischen den Majorversionen ist es eher weniger wahrscheinlich, dass die Stellenzahl mehr als Zwei betragen könnte. Daher x = 100. Weil innerhalb einer Majorversion wahrscheinlich mehrere Releaseversionen veröffentlicht werden, kann davon ausgegangen werden, dass die Nummer der Updateversionen den vierstelligen Bereich nicht verlassen wird. Gleichwohl kann aus Sorgfaltsgründen auf einen fünfstelligen Ansatz abgestellt und somit y = 100000 gesetzt werden. Ähnliches würde hinsichlich der Revisionsnummern und somit für z = 100000 zugrundeliegen. Also würden die Versionsnummern transformiert in eine Zahl bei 1|00|0.000.0|00.000 starten (Vorserien mit Major = 0 sind an dieser Stelle unbeachtlich).
Der Zahlentyp INT hat einen Wertebereich bis 2,147,483,647, so dass dieser Zahlentyp nicht ausreicht, um die umgewandelte Versionsnummer als Zahl abzubilden. Dann bleibt nur BIGINT mit einem Wertebereich bis 9,223,372,036,854,775,807, was in jedem Fall mehr als ausreichend ist. Hierdurch sind wir uns sicher, dass eine Darstellung der Versionsnummer als Zahl jederzeit vollständig abbildbar ist.
Daraus können wir jetzt unser vorbereitendes SQL-Konstrukt für das spätere MAX-Aggregat erstellen:
SELECT ProductVersion0, ((CONVERT(BIGINT,PARSENAME(ProductVersion0,4))*100+CONVERT(BIGINT,PARSENAME(ProductVersion0,3)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,2)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,1)) AS VersNummer
FROM (
SELECT ProductVersion0
FROM v_GS_OFFICE_PRODUCTINFO
GROUP BY ProductVersion0
) AS TBL1Dem folgt jetzt das SQL-Konstrukt für die Auswahl desjenigen Datensatzes eines Channels, der die höchste Versionsnummer hat:
SELECT OPI.ResourceID, [...,] OPI.ProductName0, OPI.ProductVersion0
FROM v_GS_OFFICE_PRODUCTINFO OPI
INNER JOIN (SELECT ProductVersion0, ((CONVERT(BIGINT,PARSENAME(ProductVersion0,4))*100+CONVERT(BIGINT,PARSENAME(ProductVersion0,3)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,2)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,1)) AS VersNummer
FROM (
SELECT ProductVersion0
FROM v_GS_OFFICE_PRODUCTINFO
GROUP BY ProductVersion0
) AS TBL1
) AS TBL2 ON OPI.ProductVersion0 = TBL2.ProductVersion0
WHERE OPI.IsProPlusInstalled0 = 1
GROUP BY OPI.ResourceID, [...,] ProductName0, ProductVersion0
HAVING TBL2.VersNummer = MAX(TBL2.VersNummer)[...,] ist der Platzhalter für diejenigen weiteren Felder, die bei der Ermittlung der höchsten Versionsnummer als Differenzierungsmerkmal zwingend berücksichtigt werden müssen. Das muss natürlich für diese weiteren Felder genau überlegt werden. Dann sind diese weiteren Felder sowohl oben beim SELECT als auch unten bei der GROUP-BY-Klausel anzugeben.
Beispielsweise das weitere Feld Architectur0 - (eher) nein, weil wohl eine aktuelle Installation des Hosts nur auf "x64" lauten wird. Aber selbst dann, wenn ein Produkt der x32-Gruppe angehören würde, würde es beim Office wohl nicht zugleich als x64-Version vorhanden sein können. Das dürfte sich bei den Officeprodukten auf ein und dem selben Host wohl ausschließen. Also wäre dieses Feld für die Bestimmung der höchsten Versionsnummer unbeachtlich. Gleichwohl muss das sachlich genau geprüft werden!
Natürlich muss an dieser Stelle berücksichtigt werden, was tatsächlich als Abfrageergebnis gewünscht ist. Denn wenn hier alle weiteren Felder berücksichtigt werden, die später im Abfrageergebnis darzustellen sind, kann sich der spätere Abfrageausdruck vereinfachen. Für die weitere Betrachtung unterstelle ich, dass nicht alle weiteren Felder des späteren Abfrageergebnisses für die Ermittlung der höchsten Versionsnummer herangezogen werden sollen / dürfen.
EDIT:
Das kommt davon, wenn man etwas im Trockenen (aka nur im Kopf) entwirft und es nicht auch ausprobiert / prüft. Alle Felder von OPI mit Ausnahme von ProductVersion0 müssen hinter dem SELECT und bei GROUP BY angegeben werden. An die Stelle von ProductVersion0 tritt MAX(ProductVersion0). Die HAVING-Klausel entfällt. Demgemäß sind auch nicht die weiteren Felder, die vorstehend durch [...,] symbolisiert sind, zu diskutieren, sondern schlichtweg erforderlich, soweit sie für spätere Filterungen und die Ausgabe im Gesamt-SQL-Konstrukt benötigt werden. Die SELECT- und die GROUP-BY-Zeilen ändern sich vorstehend zu
SELECT OPI.ResourceID, [...,] OPI.ProductName0, MAX(TBL2.VersNummer) AS VNumMax
GROUP BY OPI.ResourceID, [...,] ProductName0Somit kommen wir zum Gesamt-SQL-Konstrukt für die Abfrage:
SELECT SYS.Name0 AS 'Hostname',
OPI.Architecture0 AS 'OfficeArch', OPI.Channel0 AS 'Channel', OPI.LicenseState0 AS 'LicState', OPI.ProductName0 AS 'ProdName',
TBL3.ProductVersion0 AS 'Version',
OPC.AutoUpgrade0 AS 'Auto Upgrade', OPC.CCMManaged0 AS 'CCM Managed', OPC.GPOChannel0 AS 'GPOChannel', OPC.GPOOfficeMgmtCOM0 AS 'GPOOfficeMgmtCOM', OPC.UpdatesEnabled0 AS 'UpdatesEnabled'
FROM v_R_System SYS
INNER JOIN v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC ON SYS.ResourceID = OPC.ResourceID
INNER JOIN (
SELECT OPI.ResourceID, [...,] OPI.ProductName0, MAX(TBL2.VersNummer) AS VNumMax
FROM v_GS_OFFICE_PRODUCTINFO OPI
INNER JOIN (SELECT ProductVersion0, ((CONVERT(BIGINT,PARSENAME(ProductVersion0,4))*100+CONVERT(BIGINT,PARSENAME(ProductVersion0,3)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,2)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,1)) AS VersNummer
FROM (
SELECT ProductVersion0
FROM v_GS_OFFICE_PRODUCTINFO
GROUP BY ProductVersion0
) AS TBL1
) AS TBL2 ON OPI.ProductVersion0 = TBL2.ProductVersion0
WHERE OPI.IsProPlusInstalled0 = 1
GROUP BY OPI.ResourceID, [...,] ProductName0
) AS OPI ON SYS.ResourceID = OPI.ResourceID
INNER JOIN (SELECT ProductVersion0, ((CONVERT(BIGINT,PARSENAME(ProductVersion0,4))*100+CONVERT(BIGINT,PARSENAME(ProductVersion0,3)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,2)))*100000+CONVERT(BIGINT,PARSENAME(ProductVersion0,1)) AS VersNummer
FROM (
SELECT ProductVersion0
FROM v_GS_OFFICE_PRODUCTINFO
GROUP BY ProductVersion0
) AS TBL1
) AS TBL3 ON OPI.VNumMax = TBL3.VersNummer
[WHERE ...]
[ORDER BY ...][ORDER BY ...]: An dieser Stelle ist diese Klausel dann unverzichtbar, wenn das abschließende Abfrageergebnis geordnet ausgegeben werden soll. Das hat aber mit einer ORDER-BY-Klausel zur Ermittlung der höchsten Versionsnummer nichts gemein.
Nach meiner Erfahrung und meinem darauf gestütztem Dafürhalten, wäre das Gesamt-SQL-Konstrukt einigermaßen leistungsoptimiert und wenn dann auch noch die Indizes auf die relevanten Felder sinnvoll gesetzt sind, ist das eine runde Sache. Weitere Optimierungen können nur anhand der Ausführung der Abfrage am lebenden Organismus hinreichend beurteilt werden - für mich wäre das aber ein Blick durch eine trübe Glaskugel.
Wie würde sich jetzt das Gesamt-SQL-Konstrukt verändern, wenn Computed Columns zum Einsatz kommen? Ich würde die Berechnung des Feldes VersNummer in eine Computed Column packen, weil das die größtmöglich Einsparung und Beschleunigung im vorliegenden Fall mit sich bringt. Die (zusätzliche) vierteilige Aufteilung der Versionsnummer würde als Option anderen Anwendungsfällen oder besonderen Filtermomenten vorbehalten bleiben. Das Gesamt-SQL-Konstrukt verkürzt sich demgemäß wie folgt:
SELECT SYS.Name0 AS 'Hostname',
OPI.Architecture0 AS 'OfficeArch', OPI.Channel0 AS 'Channel', OPI.LicenseState0 AS 'LicState', OPI.ProductName0 AS 'ProdName',
TBL1.ProductVersion0 AS 'Version',
OPC.AutoUpgrade0 AS 'Auto Upgrade', OPC.CCMManaged0 AS 'CCM Managed', OPC.GPOChannel0 AS 'GPOChannel', OPC.GPOOfficeMgmtCOM0 AS 'GPOOfficeMgmtCOM', OPC.UpdatesEnabled0 AS 'UpdatesEnabled'
FROM v_R_System SYS
INNER JOIN v_GS_OFFICE365PROPLUSCONFIGURATIONS OPC ON SYS.ResourceID = OPC.ResourceID
INNER JOIN (
SELECT ResourceID, [...,] ProductName0, MAX(TBL2.VersNummer) AS VNumMax
FROM v_GS_OFFICE_PRODUCTINFO
WHERE IsProPlusInstalled0 = 1
GROUP BY ResourceID, [...,] ProductName0
) AS OPI ON SYS.ResourceID = TBL1.ResourceID
INNER JOIN (SELECT ProductVersion0, VersNummer
FROM v_GS_OFFICE_PRODUCTINFO
GROUP BY ProductVersion0
) AS TBL1 ON OPI.VNumMax = TBL1.VersNummer
[WHERE ...]
[ORDER BY ...]Ich kann mir nur vorstellen, dass MAX() einen String charakterweise von links nach recht, auf Basis der ASCII-Codierung vergleicht. und beim ersten Unterschied beendet. So ließe sich auch erklären, dass die Version 16.0.16127.20540 als niedriger darstellt wird als 16.0.4477.25.
Genau so ist das!Eine Entsprechung der Dot.Net-Framework | Version.CompareTo Methode gibt es in SQL vermutlich nicht? Sowas kommt doch sicher ganz oft vor, dass eine Version aus einer History-Tabelle gezogen werden muss oder nicht?
Ist mir jedenfalls nicht bekannt. Wenn eine Konstellation öfters vorkommt, lagert man das halt in Computed Columns, Funktionen und/oder Prozeduren aus. Es ist quasi wie beim Script schreiben: Mit der Zeit baut man sich seinen individuellen Werkzeugkasten auf und erweitert sein anwendungsbereites Repertoire.Ich bin mir jetzt nicht sicher was du genau mit "Systemtabelle" meinst. Nach meiner Erfahrung ist die gesamte SCCM-Datenbank "SYSTEM...". Ein falscher Eingriffe und schon herrscht Chaos. Entweder weil die Daten nicht mehr stimmen oder weil was nicht mehr funktioniert. Da greifen einfach zu viele Services drauf zu um auch noch das unvorhersehbarste Glied in der Kette, den Menschen, kompensieren zu können. 🙈🙈
So in die Richtung: Entweder unterlässt man eine Abänderung aus den von mir bereits genannten Gründen oder man geht ganz prinzipiell so mit originalen Tabellen um. Andererseits kann man das ja in einer Testumgebung untersuchen. Überdies würde ich auch in Erwägung ziehen, für derartiges mit SQL-Studio auf die Datenbank zuzugreifen.Eigentlich ist ... nicht unbekannt. ... Aber ich glaube ich habe das Beispiel zusammenkopiert.
Nichts gegen Anregungen von dritter Seite. Dennoch ist das jederzeit saubere Programmieren nach meinem Dafürhalten eine grundlegende Arbeitsweise. Andernfalls kann eine nachlässigere Herangehensweise zu vergleichbaren Situationen führen, die Du hinsichtlich heutiger Unzulänglichkeiten von Software monierst. Im Übrigen willst Du ja auch noch nach Jahr und Tag die eigenen Ergüsse leicht nachvollziehen können. Dafür ist ein jederzeit sauberes Programmieren unerlässlich.Die vorstehenden SQL-Konstrukte demonstrieren, wie eine strukturierte Formulierung dessen aussehen kann. Du wirst nämlich höchstwahrscheinlich feststellen, dass deren Struktur relativ leicht erfassbar ist, obschon es nicht aus Deiner Feder stammt ... Wird in SQL Server ein derart strukturiertes SQL-Konstrukt persistent abgelegt, dann behält SQL Server diese Struktur sogar bei.
Und da fällt mich auch gleich ein Fehler auf:
Ganz richtig.inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPC.ResourceID
-- sollte lauten:
inner join v_GS_OFFICE_PRODUCTINFO OPI on sys.ResourceID=OPI.ResourceIDAuf jeden Fall Danke @HansDampf06! Das war alles sehr aufschlussreich. 😊
Danke für die Blumen! Ich bin an dieser Stelle etwas ausführlicher geworden, um näher darzustellen, was mir gedanklich durch den Kopf gegangen war, um meinen weitergehenden Ansatz in meinem ersten Kommentar in seinen wesentlichen Aspekten zu formulieren.Danke fürs Ergänzen für Dummies! Jetzt habe ich es auch verstanden. Zumindest syntaktisch. 😅 Ich wusste einfach nicht wo ich was einbauen muss. Semantisch ist SQL für mich (noch) einfach ein Buch mit vielen Siegeln.
Tipp: Im SQL Studio auf einen Befehl den Cursor setzen und die Taste F1 drücken. Das führt zu Microsofts Befehlsreferenz und somit zu einem perfekten Nachschlagewerk. Ansonsten wie immer: Tante Google & Co. sind in diesem Fall gleichfalls Deine helfenden Freunde.Viele Grüße
HansDampf06
@mayho33:
Irgendwie hat mich jetzt die Leidenschaft / der Ehrgeiz gepackt und ich will es dann auch genauer wissen. Tue uns Diskutanten doch einen Gefallen und rufe im SQL Studio die Datenbank des SCCM auf.
Gehe bitte zuerst in die Eigenschaften der Tabelle v_GS_OFFICE_PRODUCTINFO und dort unter der Rubrik Speicher siehst Du die Zeilenanzahl. Sofern Du uns diese Zeilenanzahl verraten möchtest, wäre das ganz interessant für die Leistungsbeurteilung der Abfrageentwürfe.
Dann öffnest Du zwei neue Abfragefenster. In das erste Abfragefenster davon kopierst Du den (kompletten) Abfrageentwurf von @MadMax aus seinem Kommentar vom 16.02.2024 um 13:22:03 Uhr. In das zweite Abfragefenster kopierst Du meinen Entwurf des Gesamt-SQL-Konstrukts (= ohne Computed Columns) aus meinem Kommentar vom 16.02.2024 um 22:23:09 Uhr.
Dann führst Du zuerst die Abfrage im ersten Abfragefenster viermal aus. Nach jeder Ausführung notierst Du die Ausführungszeit, die rechts unten in der gelben Statuszeile angzeigt wird. Eine viermalige Ausführung ist nötig, damit sich der Analyzer / Optimizer von SQL Server auf die Abfrage einschießen kann, was zu einer Reduktion der Ausführungszeit führt. Die dritte und vierte Ausführung sollte bereits in etwa eine gleichlange Ausführungsdauer haben. Sollte das noch einen nennenswerten Unterschied aufweisen, wiederholst Du die Ausführung eben ein fünftes Mal. Bitte gib aber acht, dass die Datenbank in diesem Moment nicht anderweitig ungleichförmig beansprucht wird.
Danach machst Du das ebenso im zweiten Abfragefenster.
Es macht für einen ordnungsgemäßen Vergleich der beiden Abfrageentwürfe einen Sinn, jeweils dieselbe Anzahl von Ausführungen vorzunehmen.
Sollte SQL Studio beim erstmaligen Ausführen der Abfrageentwürfe meckern, es würde die Tabelle(n) nicht kennen, musst Du noch ein
dem jeweiligen Abfrageentwurf voranstellen, damit SQL Studio den Ausführungskontext kennt.
Im Anschluss könntest Du uns die Ausführungszeiten für die Abfrageentwürfe mitteilen. Ich bin wirklich gespannt, womit uns SQL Server bei Deiner Datenbank überraschen wird.
Viele Grüße
HansDampf06
PS: Bitte nimmt zuvor keine Veränderungen an der Datenbank in Bezug auf die Abfrageentwürfe vor, also keine Indizes etc. hinzufügen / ändern / ...
EDIT: Ich habe mich dazu hinreißen lassen, meine Ergüsse im vorangegangenen Kommentar selbst einmal in einer Simulation zu prüfen. Dafür habe ich eine Tabelle verwendet, die zwei Felder (ID-Wert, Datum) umfasst. Diese Tabelle habe ich für die Simulation geklont und eine dritte Spalte ProductVersion0 hinzugefügt, in der ich aus dem Datumsfeld einen Versionsstring wie beim TO generierte. Zu jedem ID-Wert gibt es von 1 bis 24 Datumswerte und es sind insgesamt 294.795 ID-Werte bei insgesamt 6.852.382 Zeilen. Später kam noch die Computed Column 'VersNummer' wie im vorangegangenen Kommentar erörtert hinzu.
Für die Simulation habe ich den SQL-Entwurf von @MadMax und meine beiden SQL-Entwürfe ohne und mit Computed Columns an diese eine Tabelle angepasst. Die Ausführungszeiten der jeweils fünften Ausführung dieser drei angepassten SQL-Entwürfe in der Simulation bei 294.795 Ergebniszeilen lauten:
- MadMax: 51 Sekunden
- ohne Computed Column: 1 Minute 26 Sekunden
- mit Computed Column: 30 Sekunden
Das zeigt sehr anschaulich die Leistungsvorteile von Computed Columns.
Für die vorstehenden Wert waren noch keine Indizes auf ProductVersion0 und auf VersNummer gesetzt. Mit einem Einzelindex auf ProductVersion0 ergeben sich folgene Werte:
- MadMax: eine marginale Erhöung auf 54 Sekunden und somit erwartungsgemäß, weil ORDER BY in ROW_NUMBER auf adhoc berechneten Werten beruht und diese Werte nicht Gegenstand eines Index sein können. Auch sonst bietet dieser Abfrageentwurf insoweit kein Optimierungspotential für Indizes, soweit das hier von Interesse ist.
- ohne Computed Column: 1 Minute 7 Sekunden, was immerhin eine Zeitreduzierung um rund 22 Prozent bedeutet und signifikant ist
- mit Computed Column: nunmehr 52 Sekunden, was zeigt, dass sich Indizes sogar negativ auswirken können, wenn diese für Abfragen nicht benötigt werden. Denn hier kann sich ein Einzelindex für ProductVersion0 überhaupt nicht förderlich auswirken, weil auf diesem Feld weder eine (direkte) Verknüpfung noch eine (direkte) Bedingung beruht.
Sodann kam ein Einzelindex für die Computed Column VersNummer hinzu:
- MadMax: schnellt auf 2 Minuten 49 Sekunden hoch, was gravierend ist und abermals die destruktive Wirkung falsch gesetzter Indizes offenbart. Kein einziger der hinzugefügten Einzelindizes ist hilfreich.
- ohne Computed Column: hier verbleibt es bei 1 Minute 7 Sekunden. Das demonstriert, dass SQL Server den passenden Einzelindex verwendet und den anderen schlicht zu ignorieren scheint.
- mit Computed Column: nochmals eine leichte Erhöhung auf 56 Sekunden. Auch das ist erwartbar, weil beide Einzelindizes bei diesem konkreten SQL-Konstrukt kein Beschleunigungspotential bieten können.
Während der Einzelindex für VersNummer belassen wird, wird der für ProductVersion0 wieder gelöscht:
- MadMax: verbleibt bei 2 Minuten 50 Sekunden. Damit scheint der verbliebene Einzelindex auf VersNummer eine besondere destruktive Wirkung auf diese Abfrage zu haben.
- ohne Computed Column: benötigt nun ebenfalls 2 Minuten 53 Sekunden. Ein zusätzlicher Hinweis darauf, dass der Einzelindex auf VersNummer erheblich destruktiv wirkt.
- mit Computed Column: verbleibt bei 57 Sekunden.
Schließlich wurde der verbliebene Einzelindex durch einen gemeinsamen Index für ProductVersion0 und VersNummer ersetzt:
- MadMax: sinkt wieder auf 54 Sekunden. Das war erwartbar, weil ProductVersion0 die erste Spalte des gemeinsamen Index ist.
- ohne Computed Column: ist wieder bei 1 Minute 9 Sekunden, was ebenso unterstreicht, dass der gemeinsame Index in seiner Wirkung sehr ähnlich wie der Einzelindex für ProductVersion0 ist. Die geringfügige Erhöhung um 2 Sekunden dürfte der Berücksichtigung der zweiten Indexspalte bei der Verarbeitung geschuldet sein.
- mit Computed Column: es sind nur noch 16 Sekunden. Hier kann augenscheinlich die Datenbankengine zusätzlichen Honig aus dem gemeinsamen Index ziehen. Die Ausführungszeit beträgt nur noch rund die Hälfte wie beim Ausführen ohne jeglichen Index für diese beiden Spalten. Dadurch besteht eine Tendenz, dass die Verwendung von Computed Columns in sinnvoller Kombination mit einem passenden Index den Abfrageentwurf von @MadMax um ein Vielfaches der Ausführungsgeschwindigkeit von bis zu 4 schlagen kann - so ja auch meine Behauptung. Denn wie vorstehend schon ausgeführt, bietet der Abfrageentwurf von MadMax keinen Ansatzpunkt für eine Unterstützung durch Indizes, während der Einsatz von Computed Columns das überhaupt erst ermöglicht. Überdies bestätigt sich praktisch, dass allein schon durch die mit Computed Columns einhergehende Vermeidung von Berechnungen bei der Abfrageausführung zu der von mir behaupteten Beschleunigung führt und durch geschickte Indizierung nochmals verbessert werden kann.
Warum kann aber der Abfrageentwurf von MadMax den Abfrageentwurf ohne Computed Column bei der Ausführungsdauer schlagen? Der Abfrageentwurf von MadMax ist im Wesentlichen nur eindimensional, weil er keine verschachtelten Unterabfragen inkludiert. Überdies bedeuten die vier Einzelberechnungen bei ORDER BY in Summe weniger Aufwand als die komplexere und vor allem doppelt erforderliche Berechnung von VersNummer. Immerhin kann dies durch den Einsatz von Indizes deutlich kompensiert werden, wenn auch nicht vollständig. Weil jedoch dieser Abfrageentwurf ohne Computed Column nur der gedankliche Zwischenschritt für die Verwendung von Computed Columns darstellt, wird abermals das Potential der Verwendung von Computed Columns gut nachvollziehbar veranschaulicht.
FREILICH: Die vorstehenden Untersuchungen für die Verwendung von Indizes war natürlich nur ein pures Herumprobieren. Richtigerweise würde man die Analysedaten des Analysers / Optimizers von SQL Server auswerten und darauf gestützt die Optimierung der jeweiligen Abfrage versuchen. Hierzu kann auch der Ausführungsplan näher betrachtet werden. Alles andere ist eigentlich sinnfrei und unprofessionell.
@mayho33: Aufgrund dieser Simulation tendiert mein Erwartungsbild dahin, dass es sich bei Deiner Datenbank ähnlich mit den Relationen bei den Ausführungszeiten der drei Abfrageentwürfe verhalten wird. Solange Du die Verwenbarkeit von Computed Columns noch nicht geprüft hast, muss der betreffende Abfrageentwurf (= mit Computed Column) natürlich außen vor bleiben.
Irgendwie hat mich jetzt die Leidenschaft / der Ehrgeiz gepackt und ich will es dann auch genauer wissen. Tue uns Diskutanten doch einen Gefallen und rufe im SQL Studio die Datenbank des SCCM auf.
Gehe bitte zuerst in die Eigenschaften der Tabelle v_GS_OFFICE_PRODUCTINFO und dort unter der Rubrik Speicher siehst Du die Zeilenanzahl. Sofern Du uns diese Zeilenanzahl verraten möchtest, wäre das ganz interessant für die Leistungsbeurteilung der Abfrageentwürfe.
Dann öffnest Du zwei neue Abfragefenster. In das erste Abfragefenster davon kopierst Du den (kompletten) Abfrageentwurf von @MadMax aus seinem Kommentar vom 16.02.2024 um 13:22:03 Uhr. In das zweite Abfragefenster kopierst Du meinen Entwurf des Gesamt-SQL-Konstrukts (= ohne Computed Columns) aus meinem Kommentar vom 16.02.2024 um 22:23:09 Uhr.
Dann führst Du zuerst die Abfrage im ersten Abfragefenster viermal aus. Nach jeder Ausführung notierst Du die Ausführungszeit, die rechts unten in der gelben Statuszeile angzeigt wird. Eine viermalige Ausführung ist nötig, damit sich der Analyzer / Optimizer von SQL Server auf die Abfrage einschießen kann, was zu einer Reduktion der Ausführungszeit führt. Die dritte und vierte Ausführung sollte bereits in etwa eine gleichlange Ausführungsdauer haben. Sollte das noch einen nennenswerten Unterschied aufweisen, wiederholst Du die Ausführung eben ein fünftes Mal. Bitte gib aber acht, dass die Datenbank in diesem Moment nicht anderweitig ungleichförmig beansprucht wird.
Danach machst Du das ebenso im zweiten Abfragefenster.
Es macht für einen ordnungsgemäßen Vergleich der beiden Abfrageentwürfe einen Sinn, jeweils dieselbe Anzahl von Ausführungen vorzunehmen.
Sollte SQL Studio beim erstmaligen Ausführen der Abfrageentwürfe meckern, es würde die Tabelle(n) nicht kennen, musst Du noch ein
USE [Datenbankname]
GO Im Anschluss könntest Du uns die Ausführungszeiten für die Abfrageentwürfe mitteilen. Ich bin wirklich gespannt, womit uns SQL Server bei Deiner Datenbank überraschen wird.
Viele Grüße
HansDampf06
PS: Bitte nimmt zuvor keine Veränderungen an der Datenbank in Bezug auf die Abfrageentwürfe vor, also keine Indizes etc. hinzufügen / ändern / ...
EDIT: Ich habe mich dazu hinreißen lassen, meine Ergüsse im vorangegangenen Kommentar selbst einmal in einer Simulation zu prüfen. Dafür habe ich eine Tabelle verwendet, die zwei Felder (ID-Wert, Datum) umfasst. Diese Tabelle habe ich für die Simulation geklont und eine dritte Spalte ProductVersion0 hinzugefügt, in der ich aus dem Datumsfeld einen Versionsstring wie beim TO generierte. Zu jedem ID-Wert gibt es von 1 bis 24 Datumswerte und es sind insgesamt 294.795 ID-Werte bei insgesamt 6.852.382 Zeilen. Später kam noch die Computed Column 'VersNummer' wie im vorangegangenen Kommentar erörtert hinzu.
Für die Simulation habe ich den SQL-Entwurf von @MadMax und meine beiden SQL-Entwürfe ohne und mit Computed Columns an diese eine Tabelle angepasst. Die Ausführungszeiten der jeweils fünften Ausführung dieser drei angepassten SQL-Entwürfe in der Simulation bei 294.795 Ergebniszeilen lauten:
- MadMax: 51 Sekunden
- ohne Computed Column: 1 Minute 26 Sekunden
- mit Computed Column: 30 Sekunden
Das zeigt sehr anschaulich die Leistungsvorteile von Computed Columns.
Für die vorstehenden Wert waren noch keine Indizes auf ProductVersion0 und auf VersNummer gesetzt. Mit einem Einzelindex auf ProductVersion0 ergeben sich folgene Werte:
- MadMax: eine marginale Erhöung auf 54 Sekunden und somit erwartungsgemäß, weil ORDER BY in ROW_NUMBER auf adhoc berechneten Werten beruht und diese Werte nicht Gegenstand eines Index sein können. Auch sonst bietet dieser Abfrageentwurf insoweit kein Optimierungspotential für Indizes, soweit das hier von Interesse ist.
- ohne Computed Column: 1 Minute 7 Sekunden, was immerhin eine Zeitreduzierung um rund 22 Prozent bedeutet und signifikant ist
- mit Computed Column: nunmehr 52 Sekunden, was zeigt, dass sich Indizes sogar negativ auswirken können, wenn diese für Abfragen nicht benötigt werden. Denn hier kann sich ein Einzelindex für ProductVersion0 überhaupt nicht förderlich auswirken, weil auf diesem Feld weder eine (direkte) Verknüpfung noch eine (direkte) Bedingung beruht.
Sodann kam ein Einzelindex für die Computed Column VersNummer hinzu:
- MadMax: schnellt auf 2 Minuten 49 Sekunden hoch, was gravierend ist und abermals die destruktive Wirkung falsch gesetzter Indizes offenbart. Kein einziger der hinzugefügten Einzelindizes ist hilfreich.
- ohne Computed Column: hier verbleibt es bei 1 Minute 7 Sekunden. Das demonstriert, dass SQL Server den passenden Einzelindex verwendet und den anderen schlicht zu ignorieren scheint.
- mit Computed Column: nochmals eine leichte Erhöhung auf 56 Sekunden. Auch das ist erwartbar, weil beide Einzelindizes bei diesem konkreten SQL-Konstrukt kein Beschleunigungspotential bieten können.
Während der Einzelindex für VersNummer belassen wird, wird der für ProductVersion0 wieder gelöscht:
- MadMax: verbleibt bei 2 Minuten 50 Sekunden. Damit scheint der verbliebene Einzelindex auf VersNummer eine besondere destruktive Wirkung auf diese Abfrage zu haben.
- ohne Computed Column: benötigt nun ebenfalls 2 Minuten 53 Sekunden. Ein zusätzlicher Hinweis darauf, dass der Einzelindex auf VersNummer erheblich destruktiv wirkt.
- mit Computed Column: verbleibt bei 57 Sekunden.
Schließlich wurde der verbliebene Einzelindex durch einen gemeinsamen Index für ProductVersion0 und VersNummer ersetzt:
- MadMax: sinkt wieder auf 54 Sekunden. Das war erwartbar, weil ProductVersion0 die erste Spalte des gemeinsamen Index ist.
- ohne Computed Column: ist wieder bei 1 Minute 9 Sekunden, was ebenso unterstreicht, dass der gemeinsame Index in seiner Wirkung sehr ähnlich wie der Einzelindex für ProductVersion0 ist. Die geringfügige Erhöhung um 2 Sekunden dürfte der Berücksichtigung der zweiten Indexspalte bei der Verarbeitung geschuldet sein.
- mit Computed Column: es sind nur noch 16 Sekunden. Hier kann augenscheinlich die Datenbankengine zusätzlichen Honig aus dem gemeinsamen Index ziehen. Die Ausführungszeit beträgt nur noch rund die Hälfte wie beim Ausführen ohne jeglichen Index für diese beiden Spalten. Dadurch besteht eine Tendenz, dass die Verwendung von Computed Columns in sinnvoller Kombination mit einem passenden Index den Abfrageentwurf von @MadMax um ein Vielfaches der Ausführungsgeschwindigkeit von bis zu 4 schlagen kann - so ja auch meine Behauptung. Denn wie vorstehend schon ausgeführt, bietet der Abfrageentwurf von MadMax keinen Ansatzpunkt für eine Unterstützung durch Indizes, während der Einsatz von Computed Columns das überhaupt erst ermöglicht. Überdies bestätigt sich praktisch, dass allein schon durch die mit Computed Columns einhergehende Vermeidung von Berechnungen bei der Abfrageausführung zu der von mir behaupteten Beschleunigung führt und durch geschickte Indizierung nochmals verbessert werden kann.
Warum kann aber der Abfrageentwurf von MadMax den Abfrageentwurf ohne Computed Column bei der Ausführungsdauer schlagen? Der Abfrageentwurf von MadMax ist im Wesentlichen nur eindimensional, weil er keine verschachtelten Unterabfragen inkludiert. Überdies bedeuten die vier Einzelberechnungen bei ORDER BY in Summe weniger Aufwand als die komplexere und vor allem doppelt erforderliche Berechnung von VersNummer. Immerhin kann dies durch den Einsatz von Indizes deutlich kompensiert werden, wenn auch nicht vollständig. Weil jedoch dieser Abfrageentwurf ohne Computed Column nur der gedankliche Zwischenschritt für die Verwendung von Computed Columns darstellt, wird abermals das Potential der Verwendung von Computed Columns gut nachvollziehbar veranschaulicht.
FREILICH: Die vorstehenden Untersuchungen für die Verwendung von Indizes war natürlich nur ein pures Herumprobieren. Richtigerweise würde man die Analysedaten des Analysers / Optimizers von SQL Server auswerten und darauf gestützt die Optimierung der jeweiligen Abfrage versuchen. Hierzu kann auch der Ausführungsplan näher betrachtet werden. Alles andere ist eigentlich sinnfrei und unprofessionell.
@mayho33: Aufgrund dieser Simulation tendiert mein Erwartungsbild dahin, dass es sich bei Deiner Datenbank ähnlich mit den Relationen bei den Ausführungszeiten der drei Abfrageentwürfe verhalten wird. Solange Du die Verwenbarkeit von Computed Columns noch nicht geprüft hast, muss der betreffende Abfrageentwurf (= mit Computed Column) natürlich außen vor bleiben.
Da hat es dich ja gepackt...
Hier mal noch drei Gedanken (ich habe fast alles gelesen ):
1) Wenn dich der BIGINT stört kannst du es auch in Hexadezimal konvertieren und in anderer Form speichern. Das macht die Spalte kleiner und ggf. auch einen Index und das könnte interessant sein. Aber man kann das dann natürlich nicht wirklich gut lesen oder debuggen.
2) Indexe wirken glaube ich grundsätzlich nicht, wenn die Spalte (mit dem Index) durch eine Funktion aufgerufen wird. Also ein parsename(spalte) dürfte mit Index auf spalte genauso schnell sein wie ohne. Das jedenfalls ist mein Wissensstand, beschäftige mich nicht so oft mit Index-Optimierung.
3) Gibt es vielleicht eine Tabelle die jede Versionsnummer jeder ResourceID (oder jedes Software-Produktes) genau einmal auflistet und in die jede Versionsnummer chronologisch geschrieben wird? Vermutlich nicht, weil auch ältere Versionsstände später inventarisiert werden könnten und dann später gespeichert werden würden, es könnte aber sein. In dem Fall könnte man über einen TIMESTAMP oder eine AUTO INCREMENT ID in dieser Tabelle sehr gut nach max() gruppieren.
Alternativ kann man eine komplett eigenständige Tabelle zu diesem Zweck führen und mit einem Trigger aktuell halten. Das wird Schreibvorgänge stark ausbremsen aber Abfragen beschleunigen. Pro: Ein Trigger kann sehr viel Logik abbilden, er könnte die Versionsnummern rekursiv durchlaufen. Das würde ihn ziemlich unabhängig machen von den Ideen von Anwendungsentwicklern wie z.B. Buchstaben innerhalb der Versionsnummer, keine Ahnung was es da noch gibt.
Hier mal noch drei Gedanken (ich habe fast alles gelesen
):1) Wenn dich der BIGINT stört kannst du es auch in Hexadezimal konvertieren und in anderer Form speichern. Das macht die Spalte kleiner und ggf. auch einen Index und das könnte interessant sein. Aber man kann das dann natürlich nicht wirklich gut lesen oder debuggen.
2) Indexe wirken glaube ich grundsätzlich nicht, wenn die Spalte (mit dem Index) durch eine Funktion aufgerufen wird. Also ein parsename(spalte) dürfte mit Index auf spalte genauso schnell sein wie ohne. Das jedenfalls ist mein Wissensstand, beschäftige mich nicht so oft mit Index-Optimierung.
3) Gibt es vielleicht eine Tabelle die jede Versionsnummer jeder ResourceID (oder jedes Software-Produktes) genau einmal auflistet und in die jede Versionsnummer chronologisch geschrieben wird? Vermutlich nicht, weil auch ältere Versionsstände später inventarisiert werden könnten und dann später gespeichert werden würden, es könnte aber sein. In dem Fall könnte man über einen TIMESTAMP oder eine AUTO INCREMENT ID in dieser Tabelle sehr gut nach max() gruppieren.
Alternativ kann man eine komplett eigenständige Tabelle zu diesem Zweck führen und mit einem Trigger aktuell halten. Das wird Schreibvorgänge stark ausbremsen aber Abfragen beschleunigen. Pro: Ein Trigger kann sehr viel Logik abbilden, er könnte die Versionsnummern rekursiv durchlaufen. Das würde ihn ziemlich unabhängig machen von den Ideen von Anwendungsentwicklern wie z.B. Buchstaben innerhalb der Versionsnummer, keine Ahnung was es da noch gibt.
Wohl wahr! Und so etwas kostet auch richtig Zeit ...
Da schießt mir gerade durch den Kopf: Möglicherweise könnte der Lösungsentwurf von @MadMax sogar meinem Ansatz mit Computed Columns sehr nahe kommen, wenn die PARSENAME-Berechnungen der vier Glieder in Computed Columns ausgelagert und darauf ein gemeinsamer Index wie im ORDER-BY-Statement gelegt wird. Aber es wäre eben am Ende wieder der Einsatz von Computed Columns verbunden mit Indizes. In jeden Fall hätte man dann zwei gleichwertige Abfrageentwürfe, um nach weiteren Untersuchungen den besseren davon zu nehmen.
Insoweit müsste man ganz genau prüfen, ob ein Trigger oder eine Aktualisierung unmittelbar vor der eigentlichen Abfrage angenehmer ist. Das kann man nicht pauschal und aus der Ferne beurteilen.
Viele Grüße
HansDampf06
EDIT: Offiziell mein 500. Kommentar hier im Forum - Prosit!
Hier mal noch drei Gedanken (ich habe fast alles gelesen ):
1) Wenn dich der BIGINT stört kannst du es auch in Hexadezimal konvertieren und in anderer Form speichern. Das macht die Spalte kleiner und ggf. auch einen Index und das könnte interessant sein. Aber man kann das dann natürlich nicht wirklich gut lesen oder debuggen.
Diese Idee finde ich ganz allgemein sehr gut, insbesondere wenn die 8 Byte für BIGINT auf diese Weise verkleinert werden können. Denn bei hinreichend großer Zeilenanzahl kann es in vielerlei Hinsicht schnell auf jedes Byte ankommen. Ich werde mir das im Hinterkopf als Option abspeichern. ):1) Wenn dich der BIGINT stört kannst du es auch in Hexadezimal konvertieren und in anderer Form speichern. Das macht die Spalte kleiner und ggf. auch einen Index und das könnte interessant sein. Aber man kann das dann natürlich nicht wirklich gut lesen oder debuggen.
2) Indexe wirken glaube ich grundsätzlich nicht, wenn die Spalte (mit dem Index) durch eine Funktion aufgerufen wird. Also ein parsename(spalte) dürfte mit Index auf spalte genauso schnell sein wie ohne. Das jedenfalls ist mein Wissensstand, beschäftige mich nicht so oft mit Index-Optimierung.
Das würde ich ebenso sehen. Denn erst nach der Berechnung wird es ja für die Abfrage und einen Index interessant. Deshalb sollten statische Berechnungen die jederzeit dasselbe Ergebnis liefern in Computed Columns ausgelagert werden. Meine Simulation gibt ja einen Hinweis in welche Richtung das führen kann/wird.Da schießt mir gerade durch den Kopf: Möglicherweise könnte der Lösungsentwurf von @MadMax sogar meinem Ansatz mit Computed Columns sehr nahe kommen, wenn die PARSENAME-Berechnungen der vier Glieder in Computed Columns ausgelagert und darauf ein gemeinsamer Index wie im ORDER-BY-Statement gelegt wird. Aber es wäre eben am Ende wieder der Einsatz von Computed Columns verbunden mit Indizes. In jeden Fall hätte man dann zwei gleichwertige Abfrageentwürfe, um nach weiteren Untersuchungen den besseren davon zu nehmen.
3) Gibt es vielleicht eine Tabelle die jede Versionsnummer jeder ResourceID (oder jedes Software-Produktes) genau einmal auflistet und in die jede Versionsnummer chronologisch geschrieben wird? Vermutlich nicht, weil auch ältere Versionsstände später inventarisiert werden könnten und dann später gespeichert werden würden, es könnte aber sein.
Nach der Beschreibung des TO würde ich das ebenfalls verneinen.In dem Fall könnte man über einen TIMESTAMP oder eine AUTO INCREMENT ID in dieser Tabelle sehr gut nach max() gruppieren.
TIMESTAMP und AUTO INCREMENT würden auf jedenfalls ein weiteres Auswahlkriterium liefern, dass zu gegebener Zeit hilfreich sein könnte.Alternativ kann man eine komplett eigenständige Tabelle zu diesem Zweck führen und mit einem Trigger aktuell halten. Das wird Schreibvorgänge stark ausbremsen aber Abfragen beschleunigen. Pro: Ein Trigger kann sehr viel Logik abbilden, er könnte die Versionsnummern rekursiv durchlaufen. Das würde ihn ziemlich unabhängig machen von den Ideen von Anwendungsentwicklern wie z.B. Buchstaben innerhalb der Versionsnummer, keine Ahnung was es da noch gibt.
Ja, wenn man sich von den zu verarbeitenden Daten und deren Struktur her an ein Problem heranarbeitet, stellen sich plötzlich ganz viele Ideen und Lösungsansätze ein. MS SQL Server hat hier ganz viel zu bieten.Insoweit müsste man ganz genau prüfen, ob ein Trigger oder eine Aktualisierung unmittelbar vor der eigentlichen Abfrage angenehmer ist. Das kann man nicht pauschal und aus der Ferne beurteilen.
Viele Grüße
HansDampf06
EDIT: Offiziell mein 500. Kommentar hier im Forum - Prosit!
Hallo an alle Helfer in diesem Thread!
Leider bin ich beruflich momentan etwas (Hilfsausdruck für "sehr") unter Zeitdruck. Darum Sorry!! wegen der "Nichtrückmeldung"!
Sobald wieder ein wenig Luft ist (mitte März) nehme ich mir mit Freuden die Zeit und eure Hilfe, wenn ihr dann noch wollt.
Danke und LG
Mayho
Leider bin ich beruflich momentan etwas (Hilfsausdruck für "sehr") unter Zeitdruck. Darum Sorry!! wegen der "Nichtrückmeldung"!
Sobald wieder ein wenig Luft ist (mitte März) nehme ich mir mit Freuden die Zeit und eure Hilfe, wenn ihr dann noch wollt.
Danke und LG
Mayho
