[SQL] Dynamisch relations vinden dmv analyze

[Verwijderd]

Ik wil graag van een DB waarmee ik moet werken een EER diagram laten maken. Het probleem is dat er geen relations in gedefineerd zijn (foreign keys), en dus tools zoals case studio ook geen relations laten zien.

Wat ik dus zoek is een analyze tool die ik alle bestaande SQL queries kan voeren zodat deze de impliciete relations uit de joins die gedaan worden kan achterhalen.

Ik ben al een tijdje opzoek hierna, maar kom er eigenlijk niet uit. Via google zoeken op dingen als "query analyzer" geeft hele andere zoek resultaten dan wat ik dus wil.

Overigens is de reden dat de DB geen foreign key relations defineerd (van horen zeggen, ben zelf echt geen expert hierin) dat het de DB veels te langzaam maakt omdat onze DB (postgreSQL) dan bij elke insert gaan zitten te controleren of de relatie wel klopt.

[Verwijderd]

Volgens mij bestaat een soort tool die jij wilt helemaal niet.

Een EER diagram geeft -juist- de structurele relations weer. Met een analyzer zoals jij voorstelt krijg je ook veel relaties die toevallig zijn. Het is halfbakken oplossing (maar mischien wel de beste in jouw situatie) voor een gare situatie.

Ik zou toch eerder eens gaan kijken naar het formeel defineren van je relaties in je DB. In welke situatie werd het geheel er dan langzamer op?

[NMe]

Verwijderd schreef op 22 oktober 2004 @ 14:10:
Overigens is de reden dat de DB geen foreign key relations defineerd (van horen zeggen, ben zelf echt geen expert hierin) dat het de DB veels te langzaam maakt omdat onze DB (postgreSQL) dan bij elke insert gaan zitten te controleren of de relatie wel klopt.

Wat een onzin. Nou moet je het zelf gaan checken....en raad eens wat er sneller is? Juist. Bak die relaties dus liever gewoon in je database.
En vertel eens wat over je database. Gezien het feit dat je een tool wil hebben om je tables en joins te analyseren, ga ik ervanuit dat het heel veel tables zijn. Dat is bijna nooit goed, en dat denk ik al helemaal als dezelfde briljante geest die tegen jou zei dat referentiële integriteit langzamer is dan zelf checken ook de ontwerper is van de database.

[Verwijderd]

NMe84 schreef op 23 oktober 2004 @ 09:50:
[...]

Wat een onzin. Nou moet je het zelf gaan checken....en raad eens wat er sneller is? Juist. Bak die relaties dus liever gewoon in je database.

Die hele aanpak van een DB zonder ref. integeriteit deugt natuurlijk van geen kanten, maar ter verdeding van de TS: Hij wil die relaties alleen hebben voor een EER diagram, en die maak je mischien 1 keer per maand ofzo?

Aan de andere kant, TS stelt dat zijn (of iniedergeval de DB waar hij mee moet werken) operationeel langzamer wordt door het bestaan van foreign keys, en operationeel gebruik je een DB natuurlijk meer dan eens per maand, mischien wel aantallen keren per seconde.

Maar om wat voor soort DB gaat het? Wat voor access paterns? Wat is de frequentie van updates?

[justmental]

Kun je geen 'disabled' foreign keys aanmaken in je database?
In Oracle kan dit iig.

Overigens kan het een heel valide optie zijn om de foreign keys in een database uit te schakelen om performance redenen, met name in OLTP systemen.

[JaQ]

justmental schreef op 23 oktober 2004 @ 11:37:
Kun je geen 'disabled' foreign keys aanmaken in je database?
In Oracle kan dit iig.

en mysql kan ze niet eens enablen (dwz, niet afdwingen dat de foreign key bestaat en vervolgens een foutmelding geven)

edit:
hier stond onzin, keys niet eens gedefinieerd.. *zucht*

Anyway, het disablen van foreign keys voor performance is in een transactiesysteem een beetje "vreemd" op z'n zachts gezegd (in een data warehouse wordt het anders) Echter, hoe kan jij in godsnaam snel joinen op sleutels als er geen indexen op liggen? (foreign key is een implecitie index) Als je database te "traag" wordt door je foreign keys, is het ontwerp misschien wat te ver genormaliseerd (of gewoon fout), maar ik ga er vanuit dat je daar niet zomaar in mag gaan zitten schuiven)

[ Voor 72% gewijzigd door JaQ op 23-10-2004 13:24 ]

[P_de_B]

DrFrankenstoner schreef op 23 oktober 2004 @ 13:19:
[...]
Anyway, het disablen van foreign keys voor performance is in een transactiesysteem een beetje "vreemd" op z'n zachts gezegd (in een data warehouse wordt het anders) Echter, hoe kan jij in godsnaam snel joinen op sleutels als er geen indexen op liggen? (foreign key is een implecitie index) Als je database te "traag" wordt door je foreign keys, is het ontwerp misschien wat te ver genormaliseerd (of gewoon fout), maar ik ga er vanuit dat je daar niet zomaar in mag gaan zitten schuiven)

Als je te maken hebt met een database die enorm veel insert/update bewerkingen moet doen, zal het sneller gaan als je geen relaties in je database hebt. Dat de selects dan eventueel trager gaan is niet relevant als het zwaartepunt van de database insert/update is.

[EfBe]

NMe84 schreef op 23 oktober 2004 @ 09:50:
En vertel eens wat over je database. Gezien het feit dat je een tool wil hebben om je tables en joins te analyseren, ga ik ervanuit dat het heel veel tables zijn. Dat is bijna nooit goed, en dat denk ik al helemaal als dezelfde briljante geest die tegen jou zei dat referentiële integriteit langzamer is dan zelf checken ook de ontwerper is van de database.

Veel tabellen nooit goed? Sinds wanneer? Niet iedereen werkt met mickey mouse databases met 10-30 tabelletjes.

[justmental]

DrFrankenstoner schreef op 23 oktober 2004 @ 13:19:
Anyway, het disablen van foreign keys voor performance is in een transactiesysteem een beetje "vreemd" op z'n zachts gezegd (in een data warehouse wordt het anders) Echter, hoe kan jij in godsnaam snel joinen op sleutels als er geen indexen op liggen? (foreign key is een implecitie index) Als je database te "traag" wordt door je foreign keys, is het ontwerp misschien wat te ver genormaliseerd (of gewoon fout), maar ik ga er vanuit dat je daar niet zomaar in mag gaan zitten schuiven)

Een foreign key disablen is wat anders dan een index weggooien.
Bij elke insert in een childtabel met een foreign key naar een master wordt er een lookup gedaan in die master of de bovenliggende key wel bestaat.
Door de foreign key te disablen voorkom je dit.

[Verwijderd]

justmental schreef op 24 oktober 2004 @ 10:44:

Een foreign key disablen is wat anders dan een index weggooien.
Bij elke insert in een childtabel met een foreign key naar een master wordt er een lookup gedaan in die master of de bovenliggende key wel bestaat.
Door de foreign key te disablen voorkom je dit.

Inderdaad, en bij een database waar inserts/updates de boventoon voeren is het dus toch niet zo'n gek idee.

We blijven dan wel met het probleem zitten van automatisch een EER diagram genereren. Er zijn databases waarin je wel een foreign key relatie kunt specificeren, maar de ref. integeriteits check achterwege kunt laten. TS zou dus even kunnen opzoeken of zijn DB die mogelijkheid heeft.

Het analyseren van alle joins die in alle queries gedaan worden blijft een halfbakken oplossing mijns inziens, en is net zoiets als in weak-typed languages door het -gebruik- van de variablen de types proberen te achterhalen. Het -kan- wel (meestal), maar is niet echt solide.

[Verwijderd]

Bedankt voor alle replies tot zover! Ik ben nu alleen eigenlijk nog niet zo heel veel verder. De ene helft zegt dat geen foreign keys hebben uberhaupt niet zo slim is, terwijl de andere helft zegt dat het inderdaad beter is voor de performance afhankelijk van de soort DB.

De DB waar ik mee werk is niet heel klein, het zijn 66 tabellen en is inderdaad voornamelijk insert gebasseerd.

Een zekere insert gebeurt met een frequentie van ongeveer 4/sec terwijl een andere insert minder vaak voorkomt (eens per half uur), maar de foreign key zou opgezocht moeten worden in een tabel van 6.6 miljoen rijen, waardoor dit onacceptabel langzaam wordt. Deze laatste zou wel geindexeerd kunnen worden, maar dan wordt de hoge frequentie insert weer veels te langzaam.

In het verleden zijn de tabellen wel geindexeerd geweest en was elke relatie ook netjes met een foreign key gespecificeerd, maar deze zijn juist allemaal weggehaald omdat het gewoon veels te langzaam werd.

Zelf werk ik overigens alleen met deze DB en kan (en mag) er dus niks aan gaan veranderen.

[Verwijderd]

Ik vind dit toch wel een interesant probleem. Het lijkt allemaal zo logisch;

• Ref. int. checks uitgeschakelen want deze zijn te langzaam
• Geen EER diagram kunnen maken want er zijn geen foreign keys

Dit zijn eigenlijk twee heel verschillende dingen. Klaarblijkelijk heeft nog nooit iemand hierover nagedacht, want een oplossing voor dit probleem bestaat niet.

[JaQ]

P_de_B schreef op 24 oktober 2004 @ 09:14:
[...]
Als je te maken hebt met een database die enorm veel insert/update bewerkingen moet doen, zal het sneller gaan als je geen relaties in je database hebt. Dat de selects dan eventueel trager gaan is niet relevant als het zwaartepunt van de database insert/update is.

Helemaal mee eens, sneller zal het zeker gaan. Echter wel meer risico op verlies van integriteit. Maar die discussie is hier redelijk offtopic en is in vele andere topics omtrent business logic in database of 2e tier/GUI al meerdere malen behandleld

EfBe schreef op 24 oktober 2004 @ 10:16:
[...]

Veel tabellen nooit goed? Sinds wanneer? Niet iedereen werkt met mickey mouse databases met 10-30 tabelletjes.

Klopt, maar waar haal jij de aanname vandaan dat ik dat wel of niet zou doen? (of voel ik me onterecht aangesproken?)

justmental schreef op 24 oktober 2004 @ 10:44:
[...]

Een foreign key disablen is wat anders dan een index weggooien.
Bij elke insert in een childtabel met een foreign key naar een master wordt er een lookup gedaan in die master of de bovenliggende key wel bestaat.
Door de foreign key te disablen voorkom je dit.

Ik wil wel een tegen argument verzinnen, maar kan niets geldigs bedenken.


Maar om terug op de vraag te komen van de topic starter... Kan je iets met de naamgeving van de kolommen en tabellen? Misschien dat daar iets omheen te schripten valt?

[EfBe]

DrFrankenstoner schreef op 26 oktober 2004 @ 00:02:
Klopt, maar waar haal jij de aanname vandaan dat ik dat wel of niet zou doen? (of voel ik me onterecht aangesproken?)

Geen idee, ik reageerde op iemand anders dat veel tabellen nooit goed is. Ik heb klanten met databases met meer dan 2000 tabellen, en ik denk niet dat dat minder kunnen worden ook. Het argument als zou een zeker aantal tabellen goed/slecht zijn is IMHO onzin, want het aantal tabellen is niet relevant, de correctheid van je relationele model wel.

Maar om terug op de vraag te komen van de topic starter... Kan je iets met de naamgeving van de kolommen en tabellen? Misschien dat daar iets omheen te schripten valt?

Stel dat de FK velden een prefix hebben van 'FK_', ik weet niet of hij dan blij moet zijn of niet .

Wat ik eerlijk gezegd niet begrijp is waarom de TS niet het schema exporteert en als een ander schema opnieuw aanmaakt en daarin (maar zonder data) de FK's aanmaakt en DAN het E/R model genereert. Aanmaken van die FK's lijkt me minder werk dan het schrijven van een analyseprogramma zonder bugs dat alle mogelijke SQL queries kan parsen.

[Verwijderd]

EfBe schreef op 26 oktober 2004 @ 09:48:
Stel dat de FK velden een prefix hebben van 'FK_', ik weet niet of hij dan blij moet zijn of niet .

Nee, aan de column namen is wel het een en ander of te lijden, maar er zijn geen FK_ prefixen of iets dergelijks.

Wat ik eerlijk gezegd niet begrijp is waarom de TS niet het schema exporteert en als een ander schema opnieuw aanmaakt en daarin (maar zonder data) de FK's aanmaakt en DAN het E/R model genereert.

Dat is natuurlijk ook een idee, maar hoe hou je dat in sync met veranderingen in het oorspronkelijke schema?

Stel ik doe dit, dan heb ik dus de originele DB zonder FK's en een los schema met FK's.

Als nu de originele DB structuur veranderd (nieuwe tabel, nieuwe velden etc) is het dan mogelijk om het ge-exporteerde schema hiervan op een of andere manier te diffen met het schema wat de FK's heeft zodat alleen weergegeven wordt wat de verschillen in structuur zijn?

Aanmaken van die FK's lijkt me minder werk dan het schrijven van een analyseprogramma zonder bugs dat alle mogelijke SQL queries kan parsen.

Tjsa, ik had gewoon nog niet deze mogelijkheid bedacht. Ik ben (zoals vermeld in eerste post) geen super expert op DB gebied.

[jochemd]

Verwijderd schreef op 25 oktober 2004 @ 10:45:

Een zekere insert gebeurt met een frequentie van ongeveer 4/sec terwijl een andere insert minder vaak voorkomt (eens per half uur), maar de foreign key zou opgezocht moeten worden in een tabel van 6.6 miljoen rijen, waardoor dit onacceptabel langzaam wordt. Deze laatste zou wel geindexeerd kunnen worden, maar dan wordt de hoge frequentie insert weer veels te langzaam.

4 inserts per seconde is niet veel en mag geen enkel probleem zijn. Als ik dit lees neig ik te zeggen dat je een heel ander probleem hebt met de database (server). FWIW, PK lookups in een 8 miljoen records tabel op consumentenhardware moet makkelijk een paar honderd maal per seconde kunnen.

[Gerco]

Verwijderd schreef op 25 oktober 2004 @ 10:45:
Een zekere insert gebeurt met een frequentie van ongeveer 4/sec terwijl een andere insert minder vaak voorkomt (eens per half uur), maar de foreign key zou opgezocht moeten worden in een tabel van 6.6 miljoen rijen, waardoor dit onacceptabel langzaam wordt. Deze laatste zou wel geindexeerd kunnen worden, maar dan wordt de hoge frequentie insert weer veels te langzaam.

Het zou wel performanceproblemen kunnen opleveren als die zoekactie op die 6.6 miljoen rows traag is, maar dan zou ik toch echt eens naar je index gaan kijken. Een database hier @ work doet lookups in een table met 300+ miljoen rows (op MSSQL) nog binnen een paar milliseconden (niet gemeten, maar meerdere per seconde is geen probleem) en die heeft zelfs nog een varchar field als PK.

Als dat zo snel kan, moet een klein beetje data als 6 miljoen rows geen probleem zijn, pgsql is een goede database server, dus ik zou eens naar je indexen gaan kijken.

[Verwijderd]

Gerco schreef op 27 oktober 2004 @ 13:22:
[...]

Als dat zo snel kan, moet een klein beetje data als 6 miljoen rows geen probleem zijn, pgsql is een goede database server, dus ik zou eens naar je indexen gaan kijken.

Inderdaad, maar de kolom van de tabel waar de FK heenwijst mag absoluut niet geindexed worden omdat anders de inserts veel te langzaam worden. Deze tabel is dus als gezegd +- 6 miljoen rows. Als je daar in gaat inserten als er een index op zit werd het ongeveer 20x ~ 30x langzamer! Het is een tijd geleden dat het getest werd, en de exacte getallen zijn niet meer bekend, maar die factor 20~30 weet men nog wel hier.

Mischien nog even een duidelijke situatie schets.

We hebben:

Table A
6.6 miljoen rows
Insert freq. +- 4/sec
Bevat de key X die in Table B als FK geldt
Geen index
Met index: insert duurt +- 8 sec (kan dus niet)

Table B
+-100.000 rows
Insert freq. +- 1/30 minuten
Bevat de eigenlijke FK die naar key X in Table A wijst
FK relatie niet gedefineerd, anders duurt de lookup in Table A zo'n 20 a 30 seconden (kan dus niet)

Je zit dus met het probleem dat met een FK de lookup te langzaam wordt en als je dat dan opvangt met een index wordt de insert weer te langzaam.

[jochemd]

Verwijderd schreef op 27 oktober 2004 @ 14:38:

Table A
6.6 miljoen rows
Insert freq. +- 4/sec
Bevat de key X die in Table B als FK geldt
Geen index
Met index: insert duurt +- 8 sec (kan dus niet)

8 seconden voor een insert in een insert-only tabel als gevolg van het toevoegen van één index is onzin, daar is iets grondig mis als dat het geval is. Wat is de definitie van die tabel, wat is de definitie van die index, hoe vaak vacuum je, hoevaal analyze je, hoevaak reindex je en wat is je I/O load? Welke PostgreSQL versie is dit?

[Jaspertje]

Ik kan je niet aan zo'n tool helpen, maar misschien dat je de database kan nabouwen in Access en dan daar, zonder data, de hele db namaken en dan alle keys en dergelijke wel ingeven. Dan kan je zelf daarbinnen al een diagram maken

[Verwijderd]

jochemd schreef op 27 oktober 2004 @ 16:06:
[...]
8 seconden voor een insert in een insert-only tabel als gevolg van het toevoegen van één index is onzin, daar is iets grondig mis als dat het geval is. Wat is de definitie van die tabel, wat is de definitie van die index, hoe vaak vacuum je, hoevaal analyze je, hoevaak reindex je en wat is je I/O load? Welke PostgreSQL versie is dit?

8 seconden klopt inderdaad niet, ik heb het net wat dieper nagevraagt en het was toen de tijd 1 seconde (wat nog steeds veels te langzaam was).

We gebruiken nu PostgreSQL 7.4 op een dual p4 xeon, maar destijds was het PostgreSQL 7.2.

Een interne snelle test op een development server (Athlon 700, 1GB mem), PostgreSQL 7.4, met en zonder index op dezelfde tabel met iets minder rows (2 miljoen ipv 6 miljoen) geeft geen enkel verschil aan in execution time. (dat kan toch ook niet?)

Een ruwe directe insert doet er afwisseled rond de 50ms over of rond de 150ms, gemeten door de insert query te runnen met Sqlexplorer (Eclipse plugin).

Men wil echter niet verder gaan testen op dit moment omdat dit niet op de todo lijst van iedereen stond en ze pas beter willen gaan testen als we een goede maand gedraait hebben en/of het niet hebben van indexen echt problemen gaat geven.

Na het droppen van de index heb ik nog wel een vacuum gedaan. Omdat de insert tijden met en zonder index exact hetzelfde waren vermoedde ik dat na het droppen van de index deze nog even bleef staan, maar ook na een vacuum zag ik dezelfde tijden.

[Verwijderd]

Jaspertje schreef op 27 oktober 2004 @ 16:14:
Ik kan je niet aan zo'n tool helpen, maar misschien dat je de database kan nabouwen in Access en dan daar, zonder data, de hele db namaken en dan alle keys en dergelijke wel ingeven. Dan kan je zelf daarbinnen al een diagram maken

Dat is precies wat tot nu toe gedaan werd. Probleem hierbij is dat de nagemaakte DB en de echte DB snel uit elkaar gaan lopen na verloop van tijd. Je zou het heel strak kunnen opleggen dat elke verandering aan de DB ook meteen in de nagemaakte DB gedaan moet worden, maar in de praktijk werkt dat helaas niet.

[Verwijderd]

Verwijderd schreef op 27 oktober 2004 @ 17:18:
[...]

8 seconden klopt inderdaad niet, ik heb het net wat dieper nagevraagt en het was toen de tijd 1 seconde (wat nog steeds veels te langzaam was).

[...]

Men wil echter niet verder gaan testen op dit moment omdat dit niet op de todo lijst van iedereen stond en ze pas beter willen gaan testen als we een goede maand gedraait hebben en/of het niet hebben van indexen echt problemen gaat geven.

Proberen ze je niet gewoon af te schepen? Het zou best kunnen dat ze helemaal geen veranderingen willen maken aan de DB en daarom maar zeggen dat het de boel trager maakt?

Mischien duurt het helemaal niet zo lang, maar is er gewoon nog code in gebruik die op de DB werkt en (soms) duplicate keys insert en wil men daar niet 1,2,3 voor uitkomen.

Overigens is het volgens mij wel zo dat een index een insert -altijd- vertraagd. Weet je zeker dat de eclipse plug-in wel de goede tijden doorgeeft?

[jochemd]

Verwijderd schreef op 27 oktober 2004 @ 17:18:

Een interne snelle test op een development server (Athlon 700, 1GB mem), PostgreSQL 7.4, met en zonder index op dezelfde tabel met iets minder rows (2 miljoen ipv 6 miljoen) geeft geen enkel verschil aan in execution time. (dat kan toch ook niet?)

Als je geen lock contention hebt is het snelheidsverschil van één extra index waarschijnlijk bij benadering te verwaarlozen

[jochemd]

Verwijderd schreef op 27 oktober 2004 @ 20:07:

Proberen ze je niet gewoon af te schepen? Het zou best kunnen dat ze helemaal geen veranderingen willen maken aan de DB en daarom maar zeggen dat het de boel trager maakt?

Waarschijnlijk niet, PostgreSQL 7.2 had Foreign Key Lock Problemen die in 7.4 gefixed zijn maar bij mijn weten nooit zijn gebackpatched.

[Verwijderd]

Verwijderd schreef op 27 oktober 2004 @ 20:07:
Mischien duurt het helemaal niet zo lang, maar is er gewoon nog code in gebruik die op de DB werkt en (soms) duplicate keys insert en wil men daar niet 1,2,3 voor uitkomen.

Nee, dat is zeker niet het geval. De code is zeker niet de mooiste, maar als dat de issue was zou dat absoluut zeker gewoon gezegd worden.

Ik zit nu even te twijfelen wat te doen. Toch de relaties toevoegen omdat het blijkbaar helemaal niet trager wordt met het huidige systeem doe ik natuurlijk niet zomaar zonder toestemming.

Het idee van efbe lijkt me nog het beste tot nu toe. Ik ben nu aan het zoeken naar een soort diff tool voor de DB structuur zodat ik de originele DB en het ge-exporteerde schema synchroon kan houden.

[Verwijderd]

Een kleine schop voor dit oude topic.

Ik zit nu met een zelfde soort probleem, alleen gaat het er hier om dat ik automatisch 1:m relations in mijn diagram wil krijgen. Nu kun je, voor zover ik weet, de 1:M niet defineren in SQL zelf. Weet iemand hoe je dit toch voor elkaar krijgt zonder telkens zelf ze in een ERD te gaan tekenen?