(MS) SQL Frage ... komme nich auf ne performante Idee ...

[McGizmo]

Hallo,

ich hätte da mal eine Frage. Kenne mich mit SQL allgemein und MS Sql im Speziellen nur mittelmäßig gut aus, vll. hat einer eine Idee.

Also, Situation:

Zugriffsrechte auf (Netzwerk)Ordner werden in einer Datenbank gespeichert. Eine Tabelle ordnet dabei den Berechtigten (egal ob Gruppe oder Benutzer) einem Verzeichnis zu
und hält die Rechte. Das sieht dann in etwa so aus (SDDL-String sind die Rechte).

Berechtigter 	| Pfad 		| SDDL-String
----------------------------------------------	

System		| C:\temp	| ----bla----
Administratoren	| C:\temp	| ----bla----
Benutzer	| C:\temp	| ----bla----	
System		| D:\temp	| ----bla----
Benutzer	| D:\temp	| ----bla----

So, die Fragestellung ist jetzt die Folgende - alle Ordner (bzw mit Einschränkung z.B. à la [WHERE pfad like 'C:%]) anzeigen, auf die die Gruppe Administratoren
keine Rechte hat.

So, meine bisherige Idee war jetzt diese. Ich mache eine Abfrage die mir alle Ordner gibt, die die WHERE pfad LIKe Bedingung erfüllen. Die empfange ich in der Anwendung
und führe für jeden String dann folgenden Befehl aus:

SELECT     pfad
FROM      dbo.[rg-vz]
WHERE     (pfad = 'PFAD_string') AND ('Administratoren' NOT IN
                          (SELECT     name
                            FROM          dbo.[rg-vz] AS [rg-vz_1]
                            WHERE      (pfad = 'PFAD_string')
                            GROUP BY name))

So, das klappt zwar theoretisch - dauert aber ewig, weil es sich ggf. um viele, viele (wir sprechen von hunderdtausenden und mehr) Ordner gibt, die das WHERE PFAD like
Statement erfüllen. Die müssen erst auf den Rechner geschaufelt weden und dannwird für jeden String ein neuer SQL Befehl abgesetzt...


Hat mir jemand ne Idee, was man machen kann? Vll. irgendwas um das direkt auf der Datenbank, ohne den "Programmzwischenschritt" machen zu können?


*E* Moment ich glaub ich hab's

 

[little_skunk]

Du kannst einerseits die Unterabfrage irgendwie ersetzen durch ein Inner Join. Ich habe allerdings in 9 Minuten ein Meating und kann dir erst hinterher hoffentlich ein passendes SQL Statement zusammenbasteln
Das 2. was du auf jeden Fall machen solltest, ist ein Index auf die Spalte, die du mit like abfragst, zu legen. Das sollte die Geschwindigkeit schonmal stark erhöhen. Inner Join würde ich aber trotzdem machen selbst wenn die Geschwindgkeit mit Index breits ausreichen sollte.

---------- Beitrag hinzugefügt um 11:04 ---------- Vorheriger Beitrag war um 09:53 ----------

So jetzt habe ich ein paar Minuten.

Du suchst alle Ordner auf die der Administrator keine Rechte hat.

Select pfad from dbo.[rg-vz] a
<left oder rigth join> dbo.[rg-vz] b on a.a.pfad=b.pfad and b.name='Administrator
where a.name!='Administrator' and b.name is null

Versuchs mal. Weiß nur nicht genau welcher Join der richtige war. Ich tippe auf left outer join. Sollte aber funktionieren und hoffentlich auch schneller sein.

In Worten passiert folgendes. Du suchst erst alle Pfade raus, die nicht Administrator im namen haben. Dann verknüpfst du die Liste mit sich selber über den Pfad aber nur mit denen, die Administrator im Namen haben. Genau die Verküpfungen, die fehlschlagen (b.name is Null) sind die, zu denen kein Eintrag mit Administrator gefunden wurde.

 

[McGizmo]

In etwa so habe ich es übrigens gemacht, nur ohne die Prüfung auf Null (Name ist Teil des Primärschlüssels), sondern mit NOT IN. Klappt auch sehr gut.

Dir aber vielen Dank für die Mühe!

 

[little_skunk]

ohne die Prüfung auf Null (Name ist Teil des Primärschlüssels)

Das es sich um den Primärschlüssel handelt spielt keine Rolle. Durch den Outher Join kommen Nullwerte zustande. Genau diese Datensätze willst du haben.

sondern mit NOT IN. Klappt auch sehr gut.

Das müsste aber meines Wissens langsamer sein als meine Lösung. Mich würde mal deine entgültige Lösung interessieren oder nimmst du die Wartezeit jetzt einfach nur in Kauf?

 

[McGizmo]

Ist eine relativ seltene Analyse, daher war ich mit der kurzen Wartezeit bisher einverstanden. Ich werd' aber gleich nochmal deine Lösung testen und vergleichen.

*e* Btw meine Abfrage sieht jetzt so aus. Ich will ja jeweils auf einen Pfad einschränken können, daher noch das where pfad like. Hässlich aber es tut ...

SELECT DISTINCT pfad FROM dbo.[rg-vz] 
WHERE  (name <> 'Administratoren') AND (pfad LIKE '"+folder+"%') AND (pfad NOT IN
                            (SELECT DISTINCT pfad
                             FROM dbo.[rg-vz] AS [rg-vz_1]
                             WHERE (pfad LIKE '"+folder+"%') AND (name = 'Administratoren')))

*e2* Deine Lösung führt leider bisher nicht zum Ziel. Es müsste, zumindest wenn ich das Statement richtig verstanden habe eigentlich der Left Join sein - die Anfrage dauert aber ewig und liefert falsche Ergebnisse. Muss mal noch schauen...

 

[little_skunk]

Danke dir

Falls es nicht klappt melde dich nochmal. Es kann sein, dass das b.name is null in die where Bedingung muss. Ich hoffe aber, dass er auch so dazu in der Lage ist.

 

[McGizmo]

So, hatte einen Fehler in der Adaption deines Statements, jetzt tut es. Habe es also mal genommen und angepasst (Distinct musste noch rein und die Einschränkung auf einen Pfad). Der Mist ist, dass ich hier das Management Studio nicht verwenden kann. MS SQL 2005 läuft auf den DB Servern, ich habe hier Visual Studio 2008 installiert - das verhindert die Installation vom 2005er Management Studio ... also kein Query Analyzer verfügbar, sackzement ...

SELECT DISTINCT a.pfad
FROM  dbo.[rg-vz] AS a LEFT OUTER JOIN
                      dbo.[rg-vz] AS b ON a.pfad = b.pfad AND b.name = 'Administratoren'
WHERE     (a.name <> 'Administratoren') AND (b.name IS NULL) AND (a.pfad LIKE ' + "PFAD"+ ')

Leider ist es nicht schneller als meine Anfrage. Ich weiß jetzt (dank fehlendem Analyzer) nicht, wie SQL Server sie abarbeitet, aber die Ausführungszeiten sind sich vom Gefühl her mal enorm ähnlich...

---------- Beitrag hinzugefügt um 08:36 ---------- Vorheriger Beitrag war um 08:26 ----------

Ich bin mir auch nicht mehr wirklich sicher, wie weit MS SQL die Statements noch optimiert und was er aus unseren Lösungen wohl macht. Ich vergesse auch immer ob jetzt MS SQL oder DB2 vergleichsweise wenig Aufwand in's Query Optimizing stecken und die Statements dafür lieber möglichst latenzfrei bearbeitet ... Siebhirn. ...

 

[little_skunk]

SELECT DISTINCT a.pfad
FROM  dbo.[rg-vz] AS a LEFT OUTER JOIN
                      dbo.[rg-vz] AS b ON a.pfad = b.pfad AND b.name = 'Administratoren' and a.name<>'Administratoren'
WHERE (b.name IS NULL) AND (a.pfad LIKE ' + "PFAD"+ ')

Das sollte (hoffentlich) nochmal ein wenig schneller sein.

Was ist mit dem Index? Soweit ich verstanden habe hast du Name und Pfad als zusammengesetzter Primärschlüssel. Jedenfalls würde ich das so machen. Da sollte dann eigentlich automatisch ein Index auf beiden Spalten liegen.

Gib mir mal 10 Minuten ich hab noch eine Idee.

 

[McGizmo]

Naja, die Situation ist etwas komplizierter. Ich habe die Tabelle hier etwas vereinfacht, weil das Prinzip das selbe sein müsste. Statt dem SDDL String stehen da jetzt noch 10 Spalten, von denen einige zum Primärschlüssel gehören (müssen), zusätzlich gibt es die Spalte "Tiefe" die die "Hierachietiefe" eines Pfades angibt - die brauche ich, um die Pfade effizient dynamisch in der Anwendung nachladen zu können, 'Tiefe' ist ebenfalls Teil vom Primärindex und hat nochmal einen eigenen Index.

Deine neue Abfrage dauert übrigens sogar länger, liefert dafür aber auch viele (alle?) Verzeichnisse, auf die Administratoren Rechte haben. Also eher falsch rum.

 

[little_skunk]

Verdammt jetzt hab ich nen Denkfehler. Deine Unterabfrage ist vollkommen richtig. Mein Join wird da nicht schneller sein. Deine Unterabfrage wird nicht wie von mir angenommen x mal ausgeführt sondern genau einmal.

Select xy from a where xy.id not in (select z from b where b.name=a.name)

So eine Unterabfrage würde nicht einmal sondern entsprechend oft ausgeführt werden. Den Grund habe ich Fett markiert. Nimmt man diesen Bereich raus, wird die Unterabfrage nur noch einmal ausgeführt werden. Jedenfalls habe ich das bei Oracle mal so gelernt.

 

[McGizmo]

Ja, das wurde uns "damals" auch so beigebracht - "Korellierte Subqueries sind der Teufel".