[php/MSSQL] selectie uit meerdere databases

[Verwijderd]

Hallo allemaal,

Ik ben bezig met een portal en probeer met php gegevens uit Microsoft SQL 2005 databases te krijgen.
Het zijn zo'n 50 verschillende databases die ik elke keer moet doorlopen. In elke database bevindt zich een tabel die ik raadpleeg om te kijken of de gebruiker toegang heeft.
Aangezien er telkens databases bijkomen gebruik ik de sys.databases om te kijken welke databases er zijn aangemaakt.

Even voor de beeldvorming doe ik de volgende dingen:

PHP:

$this->conn = mssql_connect ("*.*.*.*", "gebruikersnaam", "wachtwoord") or die (mssql_get_last_message());

      $q = mssql_query("select name from sys.databases WHERE NAME > '000' AND NAME < '999'");
      while ($r = mssql_fetch_array($q)) 
      {
         $this->checkPermission($r['name']);
      }

   
public function checkPermission($database) 
   {
      mssql_select_db('['.$database.']', $this->conn);
      $q = mssql_query("SELECT usr_id FROM humres WHERE usr_id = 'naam'");

      if (mssql_num_rows($q) > 0)
      {
         echo  "Heeft rechten in database ".$database."!!";
      }

   }

Het uitlezen van de databases duurt nu iets langer dan 60 sec. Iets waar een gebruiker niet graag op wil wachten.
Ik heb al gekeken om een view te maken zodat ik middels php maar 1 database uit hoef te lezen. Ik kan dit alleen niet met 1 sql query.
Hoe kan ik dit efficiënter aanpakken?

[MrQcue]

Kan je niet zoals bij MySQL een databasegebruiker (lees)toegang geven op alle databases zodat je niet op elke database hoeft te connecten, maar gewoon kan selecten op dbname.tablename.username

Dan kan je misschien in 1 query subqueries maken die de data ophaalt

Dus zoiets als dit:

SELECT
(SELECT COUNT(*) FROM dbname1.tablename WHERE username = 'username') AS recordsDb1Found,
(SELECT COUNT(*) FROM dbname2.tablename WHERE username = 'username') AS recordsDb2Found

[Verwijderd]

MrQcue schreef op vrijdag 19 februari 2010 @ 16:53:
Kan je niet zoals bij MySQL een databasegebruiker (lees)toegang geven op alle databases zodat je niet op elke database hoeft te connecten, maar gewoon kan selecten op dbname.tablename.username

Dan kan je misschien in 1 query subqueries maken die de data ophaalt

Dus zoiets als dit:

SELECT
(SELECT COUNT(*) FROM dbname1.tablename WHERE username = 'username') AS recordsDb1Found,
(SELECT COUNT(*) FROM dbname2.tablename WHERE username = 'username') AS recordsDb2Found

Dat kan in principe wel, maar het aantal databases (plus namen) is dynamisch. Ik kan dan niet vooraf al 1 hele query maken.

[MrQcue]

je kan toch dynamisch je query opbouwen? Of is het totale database aanbod niet bekend waaruit je select en weet je niet of je toegang tot die databases hebt ?

[RobIII]

Ik vraag me af waarom je zoveel DB's hebt en waarom je de users dan niet centraal in 1 db (aparte) zet met een koppeltabelletje op welke DB's die user rechten zou hebben

Overigens als je fully qualified namen gebruikt kun je dit doen:

SQL:

SELECT usr_id FROM [db1].dbo.humres WHERE usr_id = 'naam'
union
SELECT usr_id FROM [db2].dbo.humres WHERE usr_id = 'naam'
union
SELECT usr_id FROM [db3].dbo.humres WHERE usr_id = 'naam'
...

Dan kun je dus je query dynamisch opbouwen a.d.v.h. lijst van DB's die je ophaalt en dan in 1 query de zaak ophalen. Alse je de DB naam/namen nog wil hebben waar de user (o.a.) in gevonden is dan krijg je zoiets:

SQL:

SELECT usr_id, 'db1' as dbname FROM [db1].dbo.humres WHERE usr_id = 'naam'
union
SELECT usr_id, 'db2' as dbname FROM [db2].dbo.humres WHERE usr_id = 'naam'
union
SELECT usr_id, 'db3' as dbname FROM [db3].dbo.humres WHERE usr_id = 'naam'
...

Overigens zou je op eenzelfde manier ook een view kunnen bouwen.

Maar then again: of dit lekker gaat performen weet ik niet. Het is allicht sneller dan wat je nu hebt, maar ik denk dat je in de verkeerde richting bezig bent

Misschien een ander idee: Als je nou eens iedere nacht (of hell, voor mijn part ieder uur) een job aan trapt die uit alle DB's de namen haalt en ze in een centrale DB mikkert inc. dbnaam waar de user in stond. Dan hoef je maar 1 tabel in 1 DB te queryen.

[ Voor 126% gewijzigd door RobIII op 19-02-2010 17:31 ]

[Verwijderd]

RobIII schreef op vrijdag 19 februari 2010 @ 17:18:
Ik vraag me af waarom je zoveel DB's hebt en waarom je de users dan niet centraal in 1 db (aparte) zet met een koppeltabelletje op welke DB's die user rechten zou hebben

Databases zijn bestaande databases van een applicatie waar ik dus niets aan kan veranderen.

Misschien een ander idee: Als je nou eens iedere nacht (of hell, voor mijn part ieder uur) een job aan trapt die uit alle DB's de namen haalt en ze in een centrale DB mikkert inc. dbnaam waar de user in stond. Dan hoef je maar 1 tabel in 1 DB te queryen.

Had ik al aan zitten denken, alleen moet het eigenlijk realtime zijn. Maar als dit niet gaat lukken, ga ik inderdaad naar zo'n oplossing toe.

Ik zal je andere oplossing zsm proberen. Kan het nu helaas niet testen.

[RobIII]

Verwijderd schreef op vrijdag 19 februari 2010 @ 18:16:
Had ik al aan zitten denken, alleen moet het eigenlijk realtime zijn.

Dat roepen gebruikers altijd; als puntje bij paaltje komt boeit het amper. Eventueel zou je nog ergens een manier kunnen maken om de job ook handmatig aan te schoppen (een knop in je applicatie ofzo, of better yet: als de db's vanuit je applicatie worden toegevoegd/verwijderd doe het dan daarna automatisch hoef je ook geen job te draaien). Moet je voor de gein eens loggen hoe vaak ze die knop gaan gebruiken. Never.
Hoe vaak ik niet al heb meegemaakt dat "een order 'realtime' vanuit een webshop in het magazijn moet komen" om vervolgens daar 2 uur op de printer te liggen. Er zijn vast zaken die 'vlug' toegankelijk moeten zijn, maar als het toevoegen/verwijderen van DB's los staat van wat je gebruikers nodig hebben (als in: een systeembeheerder regelt dat ofzo) dan boeit het niet of de systeembeheerder het nog effe laat liggen omdat 'ie op de plee zit of dat ze even moeten wachten tot een job gedraaid heeft... Lang verhaal kort: weet je zéker dat het 'realtime' beschikbaar moet zijn? En hoe 'realtime' moet het dan zijn?

Anyhow, ik ben wel eens benieuwd naar hoe die query gaat presteren

[DamadmOO]

Je kan ook kijken naar een SP die door microsoft is meegeleverd om onder andere het administreren van databases makkelijker te maken.

sp_msforeachdb

Deze is eigenlijk vooral handig als je voor elke database dezelfde dingen uit de DMV's wilt laden. Maar kan hier ook gebruikt voor worden. Verder had ik zelf trouwens net als RobIII een job gemaakt die een aantal keer per dag de user rights ophaald.

[MBV]

waarom zet je geen triggers op de tabellen in die databases? Zodra er iets wijzigt direct de centrale database updaten. Desnoods zet je in zo'n trigger een boolean veldje ergens dat de cron-job de volgende keer weer moet draaien.

Sowieso zou het knap zijn om met MSSQL iets realtime te doen

spoiler:

hard real-time, als in: gegarandeerde deadlines enzo

[edit]
Gebruikers vinden het vaak al genoeg om te weten hoelang ze moeten wachten. Als je ervoor zorgt dat alles wat voor 16:00 is gedaan om 16:05 verwerkt is, en de gebruikers weten dat, accepteren ze het sneller dan dat ze een paar minuten naar een progress-bar zitten te kijken

[ Voor 28% gewijzigd door MBV op 19-02-2010 19:39 ]

[RobIII]

MBV schreef op vrijdag 19 februari 2010 @ 19:37:
waarom zet je geen triggers op de tabellen in die databases?

Ik neem aan (assumptions enzo ) dat die DB's van 'elders' komen. En dan is het lastig om triggers te maken naar centrale DB's die er niet zijn

MBV schreef op vrijdag 19 februari 2010 @ 19:37:
Sowieso zou het knap zijn om met MSSQL iets realtime te doen

spoiler:

hard real-time, als in: gegarandeerde deadlines enzo

Dat geldt voor elk DBMS Je hebt natuurlijk "realtime" en "realtime"

[ Voor 32% gewijzigd door RobIII op 19-02-2010 20:33 ]

[MBV]

ik neem ook aan dat ze van 'elders' komen: ze worden misschien gegenereerd door een ERP o.i.d. en zijn dus niet aan te passen op tabelniveau.
En dat real-time 'op school' iets anders is dan 'gewoon' wist ik ook al, zat gewoon even te zieken

[Compuhair]

Verwijderd schreef op vrijdag 19 februari 2010 @ 16:37:
Hallo allemaal,

50 verschillende databases
[...]
Het uitlezen van de databases duurt nu iets langer dan 60 sec.

Als je de queries parallel uitvoert dan breng je je totale executietijd terug tot onder de twee seconden schat ik. Is dat mogelijk?