dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
Versteh die Frage nicht, wenn Du gleichzeitig gültige Kriterien hast, musst Du mit ODER arbeiten.
Mehrere Spalten mit einem Schlüssel, anhand von gleichnamigen Spalten filtern
- Ersteller Flooo
- Erstellt am
Werbung:
dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
JSON wurde von @akretschmer schon genannt. In Postgres kann das als Binary Type gespeichert und indiziert werden. In MSSQL hat man nur pures JSON ohne Indexmöglichkeit (Soweit mir bekannt). Das ist erstmal nicht so toll. Ohne Intimkenntnis von MSSQL kann ich ebenfalls nicht sagen, ob die JSON Funktionen in MSSQL so mächtig sind wie in MSSQL, ich erlaube mir, das zu bezweifeln.
Aber (auch schon versucht zu erklären).
Wenn Du 2 Mio Variablen hast, die auf 100T Tokens gemappt sind, kann auch 2 Din A4 Seiten Where Clause kommen, ohne dass es ein Problem sein muss. Die Gesamtmenge ist egal, auch wenn Token und Variablen in einem fetten SQL abgefragt werden. Entscheidend ist, wie auch @ukulele schrieb, wie sehr die Gesamtmenge "direkt" eingeschränkt werden kann. Ich vermute in Deinem Fall wäre es ein spezifisches/konkretes Token, das dann nach Adam Riese für 20 Variablen zuständig ist.
Wenn das Token als einfache Spalte gespeichert und indiziert ist, dann dauert das finden von 20 Zeilen Millisekunden. 20 Zeilen auf die Attribute zu prüfen, auch auf viele verschiedene ist auch ohne Index nicht schlimm.
Als JSON Objekt wären diese 20 Zeilen vermutlich nur 1. Also nicht 2 Mio DS sondern genau so viel wie Tokens. So oder so, 20 oder 1 JSON Objekt zu parsen, macht keinen großen Unterschied.
Wenn Du Lust hast, kannst Du das alles schnell ausprobieren. Bau dir eine neue Tabelle, generiere Dir 100T Tokens mit JSON Elementen für Deine Berechtigungen und mach ein paar Abfragen. Dafür brauchst Du Deine Anwendung nicht umzubauen. Du erhälst sofort die Antwortzeiten für deine JSON Abfragen.
ist meine Antwort auf ukulele´s Antwort
"Verstehe ich jetzt noch nicht ganz, die entwickelst das Programm selber oder du hast Vorgaben durch ein bestehendes Programm? Warum musst du Joinen und kannst nicht AND verwenden?"
Vielen Dank! Ich werde das mal ausprobieren, wenn ich relevante Ergebnisse liefern kann, gebe ich bescheid!
ukulele
Datenbank-Guru
- Beiträge
- 5.170
Das hatte ich nicht ganz verstanden, ich nahm an das du eventuell durch das Programm eingeschränkt wirst, welches SQL du ausführen kannst und welches nicht. Ich kenne das aus unserem DMS, da geht nicht alles, z.B. wird der SELECT Teil komplett vom DMS gesetzt. Du hast erstmal alle SQL Befehle zur Verfügung.
Natürlich must du bei EAV pro Bedingung auf ein weiteres Attribut (das meintest du vermutlich mit AND) dieses weitere Attribut mit einem weiteren Join holen. Das ist aber bei diesem Prinzip völlig okay. Man könnte sich irgendwas exotisches basteln aber wie bereits gesagt ist das vermutlich nicht schneller oder irgendwie besser. Viele Joins sind ansich erstmal kein Problem.
Wenn du die Software frei entwickelst hast du das Glück noch frei entscheiden zu können was du wählst. Generell würde ich mir überlegen ob MSSQL die beste Wahl ist oder nicht vielleicht PostgreSQL. MSSQL finde ich ansich sehr gut aber Microsoft und seine Lizenzpolitik nicht. PostgreSQL ist für die grüne Wiese super und ich glaube man tut sich einen gefallen wenn man sich nicht an Microsoft bindet.
Im Vergleich zu EAV bieten sich JSON oder auch XML an, beides wird grundsätzlich unterstützt. Wie gut kann ich nicht sagen aber natürlich ist auch Microsoft daran interessiert das möglichst schnell und effizient zu gestalten. Ich würde mich jetzt nicht auf ein DBMS einschießen weil es hier und da schneller ist als ein anderes, das kann sich mit einer neuen Version ändern. Auch würde ich nicht EAV bevorzugen wenn es schneller als JSON wäre, auch das könnte sich schnell ändern. JSON oder XML wollen das ermöglichen was du suchst, wenn jetzt nicht grade irgendwas wirklich problematisch läuft würde ich versuchen eines der beiden zu nutzen.
Da unsere Kunden generell immer eine MSSQL Datenbank zur Verfügung haben ist dies für mich die erste Wahl, vor allem weil ich auch auf Daten des Unterliegenden Archives Zugreifen muss (dies liegt immer in der MSSQL Datenbank)
danke! Sobald ich Zeit habe teste ich es mit JSON!
danke! Sobald ich Zeit habe teste ich es mit JSON!
ukulele
Datenbank-Guru
- Beiträge
- 5.170
Ein triftiger Grund bei MSSQL zu bleiben.
Das kann nicht schaden. Am besten du schreibst die Abfrage fertig, legst viele Testdaten an und machst dann das selbe mal mit JSON.
dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
Ich habe das mal mit Postgres gemacht. Damit kannst Du zumindest meine Idee verstehen. Wenn Du 5 Minuten für Download und Installation von pg spendierst, kannst Du es selbst nachvollziehen.
So wie es hier gemacht ist, sollte es auch relativ ähnlich in MS funktionieren, aber das habe ich nicht und ich kenne gerade mal ein paar JSON Brocken in Postgres.
Ich generiere 100000 Datensätze und nehme an, du kommst immer über ein spezifisches Token rein. Weil ich die generierten Tokens selbst nicht kenne, habe ich zum Nachstellen dafür einen Like Ausdruck genommen. Bei Dir wäre es nach meinem Verständnis ein konkretes Token.
Was ich zeigen will, die Filterung von JSON ist in diesem Fall wurstegal (aus Performancesicht). Timing ist aktiviert, die Zeiten sind alle vertretbar, finde ich. Ist auf einem alten Bürolaptop unter Windows gelaufen.
Code:
postgres=# -- Testdaten generieren, 5 Datensätze
postgres=# select MD5(random() ::text) t_token, z.t_kv_json
postgres-# from (select s, json_agg(json_build_object(t_key, t_val)) t_kv_json
postgres(# from (select s, t_key,
postgres(# case
postgres(# when t_key = 'Abteilung'
postgres(# then t_kv_arr [ 2 ] [ floor(random() * 3 + 1) ]
postgres(# when t_key = 'Vertraulichkeit'
postgres(# then t_kv_arr [ 3 ] [ floor(random() * 3 + 1) ]
postgres(# when t_key = 'Schutzgrad'
postgres(# then t_kv_arr [ 4 ] [ floor(random() * 3 + 1) ]
postgres(# else 'error'
postgres(# end t_val
postgres(# from (select s, unnest(t_kv_arr [ 1:1 ]) t_key, t_kv_arr
postgres(# from (select s, array [ [ 'Abteilung','Vertraulichkeit','Schutzgrad' ],
postgres(# [ 'GL','Lager','Produktion' ],
postgres(# [ 'offen','intern','GL' ],
postgres(# [ '1','2','3' ] ] as t_kv_arr
postgres(# from generate_series(1, 5) s) ar) x) y
postgres(# group by s) z; ---------^ statt 5 auf 100000 setzen, um entsprechend viele Werte zu generieren
t_token | t_kv_json
----------------------------------+--------------------------------------------------------------------------------------
54e3eb58b97843ef42d611608ce0be8b | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "2"}]
1f9ccaf809808c3199bdffbb80ae6082 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "3"}]
5d7c2f5e7d8f37c280c1344f919a1010 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "2"}] c8c1523fa113d32941912ea0aca53a82 | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
79af436b406a1d527e5bc2bfa6baf220 | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "2"}]
(5 Zeilen)
postgres=#\timing
postgres=#
postgres=# \timing
Zeitmessung ist an.
postgres=#
postgres=#
postgres=# -- Tabelle erzeugen, für Export/Test/Weiterverarbeitung
postgres=# -- drop table test_eav_json;
postgres=# create table test_eav_json as
postgres-#
postgres-# -- reines Select der Rohdaten
postgres-# select MD5(random() ::text) t_token, z.t_kv_json
postgres-# from (select s, json_agg(json_build_object(t_key, t_val)) t_kv_json
postgres(# from (select s, t_key,
postgres(# case
postgres(# when t_key = 'Abteilung'
postgres(# then t_kv_arr [ 2 ] [ floor(random() * 3 + 1) ]
postgres(# when t_key = 'Vertraulichkeit'
postgres(# then t_kv_arr [ 3 ] [ floor(random() * 3 + 1) ]
postgres(# when t_key = 'Schutzgrad'
postgres(# then t_kv_arr [ 4 ] [ floor(random() * 3 + 1) ]
postgres(# else 'error'
postgres(# end t_val
postgres(# from (select s, unnest(t_kv_arr [ 1:1 ]) t_key, t_kv_arr
postgres(# from (select s, array [ [ 'Abteilung','Vertraulichkeit','Schutzgrad' ],
postgres(# [ 'GL','Lager','Produktion' ],
postgres(# [ 'offen','intern','GL' ],
postgres(# [ '1','2','3' ] ] as t_kv_arr
postgres(# from generate_series(1, 100000) s) ar) x) y
postgres(# group by s) z; ---------^ statt 5 auf 100000 setzen, um entsprechend viele Werte zu generieren
SELECT 100000
Zeit: 1333,417 ms (00:01,333)
postgres=#dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
Teil 2
Code:
postgres=# -- Abfrage zur Probe
postgres=# select * from test_eav_json;
t_token | t_kv_json
----------------------------------+--------------------------------------------------------------------------------------
711aa0c3eb15d4859e3d0a7b65107dc9 | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "1"}]
b6c5bfa8bb66e46337e6ab36d28b7164 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
6e67701ee4ef834789333ba84e7ce5e8 | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "3"}]
51494093252477fd906a93d51e39fa6b | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "1"}]
f17bbda778e4cba1d6d05b15ecd0dcfc | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "1"}]
85306e7a6a35161cbc79dc1cbff37609 | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
0292b9925d8014a76353124e54b1a71a | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "1"}]
a98b636999756179e7c254066dd2eef0 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "3"}]
75ee1f567a589b9e2f058408cc10b33e | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
3df7ee600579d37108c26359614090c2 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "2"}] f8fec8baa69f1365d10384d4136c14e4 | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "1"}]
e5a21c67cac788e7772830f4f6285c65 | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "2"}]
a550d39b32afb34552e8181911fd1e5d | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "3"}]
e3ebcd72ce34ecd353556782f78a768a | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "1"}]
a52f61b0b63bb299ccffa4a04e90e7fc | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "3"}]
0c83756c5efe7e9e662e412b3d83a748 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "3"}]
45ad41b7b83accf6bd59db3943647f92 | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "1"}]
545ae3e9253c1070efe07f6e93ae7b1b | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "GL"}, {"Schutzgrad" : "1"}]
efdf97cfc4d1d3579eaa634f27f2f5d4 | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "3"}]
97d6efffe8929c0ff0c659600c028a62 | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "3"}]
cce72a41233da7bc9241aaf5cbe112f7 | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "2"}]
f474b60ddac43007f957468998289fc7 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "intern"}, {"Schutzgrad" : "1"}] 81596d17befdd4787b62ea7c1f6d6894 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "2"}]
562e3e3c49a0599bde5b82bf37664a26 | [{"Abteilung" : "GL"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "3"}]
0c11e78853607250ba3e30b99ef96577 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
5487174e645e49e63a9fb82ab99f1d22 | [{"Abteilung" : "Produktion"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
2a7c6bb6c9d1f50bcb6619f7db4ae78f | [{"Abteilung" : "Lager"}, {"Vertraulichkeit" : "offen"}, {"Schutzgrad" : "1"}]
^CZeit: 107,333 ms
postgres=#
postgres=# -- Für direkte Abfrage in Postgres einen Index auf t_token anlegen
postgres=# -- drop index token_idx ;
postgres=# CREATE UNIQUE INDEX token_idx ON test_eav_json (t_token);
CREATE INDEX
Zeit: 802,018 ms
postgres=#
postgres=# -- alle records, die mit einem bestimmten Teil im Token starten ..
postgres=# -- und einer gesuchten Key Value Kombination
postgres=# select
postgres-# tj.t_token,
postgres-# x.j->'Vertraulichkeit' "Vertraulichkeit"
postgres-# from test_eav_json tj,
postgres-# lateral (select json_array_elements(tj.t_kv_json) j) x
postgres-# where
postgres-# tj.t_token like 'abc%' and -- diese Zeile auskommentieren, um einen Fulltablescann zu provozieren (oder ohne Index)
postgres-# x.j->>'Vertraulichkeit' = 'intern';
t_token | Vertraulichkeit
----------------------------------+-----------------
abc3a3e3f90c48cb9b5122b91b134541 | "intern"
abcf67b015a6bcda11c858c132a6aa7a | "intern"
abcc78e7b0e05a80b14c18b872a427a3 | "intern"
abcee8c907180710cd3dcbf3c07de9db | "intern"
abcd02569cd6b68a551eda5f80c0ae68 | "intern"
abcf157c8e79a40c1da759a36b3a4eb9 | "intern"
abc729e1e35420d7e6b1293a73981edb | "intern"
abc2c9efc254632f55c5f4cbbc5078c4 | "intern"
abcb7a12200315f6c4d4ab18471017a6 | "intern"
abc73b6ed44dee0e5f42ea1a5ea8256f | "intern"
(10 Zeilen)
Zeit: 26,325 ms
postgres=#
postgres=#dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
letzter Teil
Code:
postgres=# -- ähnliche Abfrage über alle 100T Datensätze als Count
postgres=# select count(*) from (
postgres(# -- alle records, die mit einem bestimmten Teil im Token starten ..
postgres(# -- und einer gesuchten Key Value Kombination
postgres(# select
postgres(# tj.t_token,
postgres(# x.j->'Vertraulichkeit' "Vertraulichkeit"
postgres(# from test_eav_json tj,
postgres(# lateral (select json_array_elements(tj.t_kv_json) j) x
postgres(# where
postgres(# tj.t_token like 'abc%' and -- remove this to provoke Full Table Scan
postgres(# (x.j->>'Vertraulichkeit' = 'intern'
postgres(# or x.j->>'Abteilung' = 'GL')
postgres(# ) al
postgres-# ;
count
-------
26
(1 Zeile)
Zeit: 13,642 ms
postgres=#
postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
(1 Zeile)
postgres-# \q
C:\Users\...>dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
Die JSON Kriterien Prüfung ist wahrscheinlich nicht super elegant. Mache ich selten.
Du kannst daraus MSSQL Inserts genererieren und eine analoge Abfrage unter MSSQL bauen. Oder natürlich Deine eigenen Daten in MSSQL zu JSON transformieren.
Achso, JSONB:
Also ganz deutlich, die JSON Elemente sind hier nicht indiziert, ähnlich wie es wahrscheinlich in MSSQL wäre. Man könnte in PG auch das noch machen und ausschließlich auf den indizierten JSON Daten noch schneller arbeiten.
Du kannst daraus MSSQL Inserts genererieren und eine analoge Abfrage unter MSSQL bauen. Oder natürlich Deine eigenen Daten in MSSQL zu JSON transformieren.
Achso, JSONB:
Also ganz deutlich, die JSON Elemente sind hier nicht indiziert, ähnlich wie es wahrscheinlich in MSSQL wäre. Man könnte in PG auch das noch machen und ausschließlich auf den indizierten JSON Daten noch schneller arbeiten.
dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
Tippfehler: Es sind 100000, nicht 1 Mio.
Die Anforderung war ja 100000 Token mit bis zu 20 Attributen(1-2 Mio Attributen), ich habe nur 100000 x 3 generiert, durch den JSON Ansatz bleibt es bei 100000 Datensätzen, im Beispiel für 300000 Attribute. Das sollte unauffällig skalieren, auch wenn man 20 oder mehr Attribute generiert.
dabadepdu
Datenbank-Guru
- Beiträge
- 1.704
Prima, es ist nicht viel Aufwand! Generieren brauchst Du ja nicht, Du hast ja Daten.
Wie gesagt, zum Test könntest Du aus den Postgresdaten auch Insert Statements für MS SQLgenerieren. oder halt Deine transformieren.
Hier gibt es auch MSSQL Spezialisten, die Dir bestimmt helfen können, ich kann mir nicht vorstellen, dass das nicht geht in MSSQL.
Werbung:
Ich habe mittlerweile in MSSQL verglichen wie das Zeitverhalten ist.
bei 1_000_000 Datensätzen dauert eine "schwierige Abfrage (10 Vergleiche)" mithilfe von EAV ~30 Sekunden.
Die Selbe Abfrage auf die Selbe Menge bei der die Tokens den Metadaten per JSON direkt relational zugewiesen sind ~5 Sekunden
Vielen Dank nochmals!
P.S. Sorry für die verspätete Rückmeldung
bei 1_000_000 Datensätzen dauert eine "schwierige Abfrage (10 Vergleiche)" mithilfe von EAV ~30 Sekunden.
Die Selbe Abfrage auf die Selbe Menge bei der die Tokens den Metadaten per JSON direkt relational zugewiesen sind ~5 Sekunden
Vielen Dank nochmals!
P.S. Sorry für die verspätete Rückmeldung