dabadepdu
Datenbank-Guru
- Beiträge
- 1.789
Versteh die Frage nicht, wenn Du gleichzeitig gültige Kriterien hast, musst Du mit ODER arbeiten.Als kleiner Nachtrag: wie könnte ich das ganze mit einfachen AND´s machen?
Folge dem Video um zu sehen, wie unsere Website als Web-App auf dem Startbildschirm installiert werden kann.
Anmerkung: Diese Funktion ist in einigen Browsern möglicherweise nicht verfügbar.
Versteh die Frage nicht, wenn Du gleichzeitig gültige Kriterien hast, musst Du mit ODER arbeiten.Als kleiner Nachtrag: wie könnte ich das ganze mit einfachen AND´s machen?
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.Ich schaue mir das ganze auch mal mit dem erwähnten JSON an, wäre auch eine Möglichkeit, aber ich habe die böse Vermutung das dies nicht sehr performant sein wird, oder? Vor allem weil JSON Parsing ja heavy Duty ist im Gegensatz zu einem einfach join, oder liege ich das falsch?
Die Datenmenge könnte schnell über 100000 "tokens" mit insgesamt 1-2 millionen variablen werden
ist meine Antwort auf ukulele´s AntwortVersteh die Frage nicht, wenn Du gleichzeitig gültige Kriterien hast, musst Du mit ODER arbeiten.
Vielen Dank! Ich werde das mal ausprobieren, wenn ich relevante Ergebnisse liefern kann, gebe ich bescheid!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.
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.@ukulele okay, ich werde mal probieren einen geeignetes Programm zu schreiben. Bei bedarf poste ich auch gerne den Quelltext (C#). Leider muss ich für jede UND verknüpfung einen eigenen join bauen, da komme ich aber nicht drum rum.
Aber diese ergänzung finde ich sehr hilfreich, damit lassen sich ODERs sehr einfach programmieren "AND t1.data IN ( 'GL','Lager' )"
Ein triftiger Grund bei MSSQL zu bleiben.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)
Das kann nicht schaden. Am besten du schreibst die Abfrage fertig, legst viele Testdaten an und machst dann das selbe mal mit JSON.danke! Sobald ich Zeit habe teste ich es mit JSON!
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.Vielen Dank! Ich werde das mal ausprobieren
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=#
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=#
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\...>
Tippfehler: Es sind 100000, nicht 1 Mio.Ich generiere 100000 Datensätze
habe schon angefangen ist aber in MSSQL schwerer