SQL-Abfrage ueber normalisierte Tabellen

[Klaus Trophob (Gast)]

Ich speichere Konfigurationen in einer Datenbank ab, die Daten 
normalisiere ich. DBMS ist Microsoft SQL Server 2008.
Beispiel:

Tabelle ID: (ID wird automatisch inkreementiert)
ID:
1
2
3

Tabelle Modul:
ID: ModulID:
1   1
1   2
2   1
3   3

Tabelle ModuleID: (ModuleID wird automatisch inkrementiert)
ModulID: Modul:
1        foo
2        bar
3        nope

Konfiguration 1 hat also die Module foo und bar eingebunden, Konfig. 2 
Modul foo und Konfiguration 3 Modul nope.

Bevor ich eine neue Konfiguration anlege, muss ich schauen, ob es sie 
schon gibt - habe ich also zB Modul bar und nope in einer Konfiguration, 
frage ich nach allen IDs, die die ModulIDs von bar und nope haben. 
Kommen keine zurueck, leg ich sie an, kommt eine ID zurueck, gibt es die 
Konfiguration, kommen mehrere zurueck, ist die DB zerschossen ;)
Soweit die Theorie. Real ist die DB wesentlich komplexer, es gibt noch 
mehr Tabellen als nur die Module und mehr Spalten in den Tabellen. Das 
ist ja ein Standardproblem: Wie fragt man sowas am geschicktesten ab? 
Wenn ich das alles zusammenjoine, kriege ich zigtausende Zeilen zurueck 
- die Tabellen multiplizieren sich ja. Ich moechte allerdings nur eine 
Spalte pro ID erhalten, und insgesamt eben 0,1 oder mehr als 1. Distinct 
koennte man benutzen, die Abfrage habe ich nach 20 Minuten abgebrochen 
;)
Irgendwie hakt es da gerade bei mir :(

[D. I. (Gast)]

Bei der Tabellenaufteilung und der Aufgabenstellung scheint mir das 
persönlich Applikationslogik zu sein. Im Wesentlichen muss zu jeder 
Konfiguration ein Modultupel gebildet werden und anschließend muss man 
diese mit dem zu suchenden Tupel vergleichen.

Erste Frage: Wie häufig wird diese Operation ausgeführt?

[Klaus Trophob (Gast)]

> Erste Frage: Wie häufig wird diese Operation ausgeführt?

Bei jeder neuen Hardwarekonstellation - selten, worst case ein paar Mal 
pro Minute, real wohl alle paar Monate.

[Klaus Trophob (Gast)]

Achso, Applikationslogik bedeutet, dass ich diese Operation nicht in der 
Datenbank, sondern der App. mache, indem ich die Abfragen nacheinander 
absetze und mir selber die IDs sortiere, die Ergebnisse in Arrays 
schiebe usw?

[D. I. (Gast)]

Der allererste Schuss wie ich es jetzt stumpf in Ruby on Rails gelöst 
hätte:

search_config = [4,1,2,3].sort (Konfiguration = Menge modulIDs)

config_hash = {}

Modul.each do |modul|
  config_hash[modul.ID] = [] unless config_hash.has_key?(modul.ID)
  config_hash[modul.ID] << modul.modulID
end

found = 0

config_hash.each do |id, config|
  found += 1 if config.sort == search_config  
end

if found == 0
  puts "nix"
elsif found == 1
  puts "eins"
else
  puts "mehr als eins"
end

[Peter II (Gast)]

Klaus Trophob schrieb:
> Ich moechte allerdings nur eine
> Spalte pro ID erhalten, und insgesamt eben 0,1 oder mehr als 1. Distinct
> koennte man benutzen, die Abfrage habe ich nach 20 Minuten abgebrochen
> ;)
> Irgendwie hakt es da gerade bei mir :(

distinct ist langsam, verwende group by dann bekommst du auch über 
count() die anzahl.

Wir haben hier tabellen mit 100.000.000 einträgen und das geht recht 
schnell. Wenn es bei dir länger dauert dann hast du keinen passenden 
Primary keys.

[D. I. (Gast)]

Achja mein Code ist mit Sicherheit nicht der Weisheit letzter Schluss ;)

[Klaus Trophob (Gast)]

Danke fuer die Tips - ich habe gleich Feierabend, werde aber morgen 
testen.
Den letzten Schuss der Weisheit brauch ich nicht, ein Denkansatz reicht 
voellig aus ;)

[Klaus Trophob (Gast)]

Soweit ich das bis jetzt beurteilen kann, funktioniert GROUP BY besser 
als DISTINCT.
Ich habe erstmal noch ein anderes Problem: Ich moechte im Beispiel ja 
die ID haben, die foo UND bar enthaelt. Frage ich also nach der 
Konfiguration, in der Modul.ModulID 1 UND 2 ist, kommt nichts zurueck, 
frage ich mit ODER, dann kaemen auch Ergebnisse zurueck, in denen nur 
eins passt - was uebersehe gedanklich?

[D. I. (Gast)]

Klaus Trophob schrieb:
> was uebersehe gedanklich?

Wie soll das gehen nach UND zu fragen?

Eine modulID die einer Konfiguration zugeordnet ist kann nur einen Wert 
haben und nicht mehrere gleichzeitig.

Mit der Gestaltung der Tabellen und dem geforderten Problem wirst du mit 
"normalem" SQL nicht weit kommen. Da wirst du dich in der 
Applikationslogik deutlich leichter tun.

[Peter II (Gast)]

Klaus Trophob schrieb:
> Ich moechte im Beispiel ja
> die ID haben, die foo UND bar enthaelt.

select distinct
   m.id
from Modul m
join ModuleId mi1
   on mi1.ModulId = m.ModulId
join ModuleId mi2
   on mi2.ModulId = m.ModulId
where
   mi1.Modul = 'foo'
   and mi2.Modul = 'bar'

> Mit der Gestaltung der Tabellen und dem geforderten Problem wirst du mit
> "normalem" SQL nicht weit kommen. Da wirst du dich in der
> Applikationslogik deutlich leichter tun.

das wird viel zu oft gesagt, aber es gibt fast immer eine lösung mit 
SQL. Das ist dann meist auch noch schneller weil weniger daten 
übertragen werden müssen.

[D. I. (Gast)]

Peter II schrieb:
> das wird viel zu oft gesagt, aber es gibt fast immer eine lösung mit
> SQL. Das ist dann meist auch noch schneller weil weniger daten
> übertragen werden müssen.

Da hast du schon recht.

Was nun wenn eine Konfiguration aus 10 oder mehr Moduln besteht. 10 
Joins? Müsste dann halt die Query dynamisch erstellen lassen, das ginge 
durchaus.

[Klaus Trophob (Gast)]

Soweit war ich schon - automatisch erstellte, hunderte Zeilen lange 
Queries - geklappt hats aber nicht.
Jetzt laeuft es erstmal in der Anwendung in Form eines feuerspuckendens 
Schleifen- und Arraymonster :D

[Peter II (Gast)]

D. I. schrieb:
> Was nun wenn eine Konfiguration aus 10 oder mehr Moduln besteht. 10
> Joins? Müsste dann halt die Query dynamisch erstellen lassen, das ginge
> durchaus.

dafür gibt es bei MSSQL sogar recursion im SQL. Damit sollte das möglich 
sein.

http://msdn.microsoft.com/en-us/library/ms175972.aspx

Using a recursive common table expression to display multiple levels of 
recursion

[Peter II (Gast)]

nicht schön sollte aber gehen

select
   m.id, dbo.concat( '#' + mi1.Modul + '#' )
from Modul m
join ModuleId mi
   on mi1.ModulId = m.ModulId
group by
   m.id
having
   dbo.concat( '#' + mi1.Modul + '#' ) like '%#foo#%'
   and dbo.concat( '#' + mi1.Modul + '#' ) like '%#bar#%'

[D. I. (Gast)]

Peter II schrieb:
> Klaus Trophob schrieb:
>> Ich moechte im Beispiel ja
>> die ID haben, die foo UND bar enthaelt.
>
> select distinct
>    m.id
> from Modul m
> join ModuleId mi1
>    on mi1.ModulId = m.ModulId
> join ModuleId mi2
>    on mi2.ModulId = m.ModulId
> where
>    mi1.Modul = 'foo'
>    and mi2.Modul = 'bar'


jetzt stellt sich aber noch die Frage ob eine Konfiguration 
Permutationsinvariant ist oder nicht. Bei deinem Beispiel wäre sie 
permutationsvariant.

[Jens G. (jensig)]

select id from (
  select count(id) a, id
  from modul
  where ModulID in (select modulid
                    from modulID
                    where Modul in ('foo','bar'))
  group by id)
where a=(select count(*)
         from modulID
         where Modul in ('foo','bar'))
  )




Ich gehe hierbei davon aus, daß in Tab ModulID der Modulname (foo, bar, 
nope, ...) ebenfalls unique ist, wie eben auch modulid - also eindeutig 
1:1.
Beide Subselects müssen natürlich immer gleiche Suchwerte enthalten, 
sonst gehts schief.
Allerdings findet dieses SQL auch die ID's, die mehr als foo AND bar 
haben.
Es findet also IDs, denen mindestens 'foo' AND 'bar' zugeordnet sind.

[Peter II (Gast)]

Jens G. schrieb:
> select id from (
..

Es fehlt der alias name des Subselects. Und eine Klammer ist zu viel.