login  Naam:   Wachtwoord: 
Registreer je!
 Forum

Optimalisatie Query

Offline finduilas - 21/01/2015 09:22 (laatste wijziging 21/01/2015 13:13)
Avatar van finduilasPHP gevorderde Hallo

Voor een applicatie die ik moet maken beschik ik over een MS SQL server met allerlei data in.

Het bevat enkele tabellen.

--> In 1 tabel worden leerlingen bijgehouden
--> In 1 tabel worden klassen bijgehouden
--> In 1 tabel worden de leerlingen gekoppeld aan een klas, per schooljaar.

Nu moet ik alle leerlingen ophalen die binnen dat schooljaar aan een leerkracht gekoppeld zijn. (Dit kan verschillende klassen zijn).

Voor deze klassen heb ik reeds een array met de Ids..
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SELECT leerlingen.id, leerlingen.stamboeknr, leerlingen.naam, leerlingen.voornaam, klassenperleerling.Klassen_id
  2. FROM leerlingen
  3. LEFT JOIN klassenperleerling ON leerlingen.id = klassenperleerling.leerlingen_id
  4. WHERE Leerlingen.school_id = 2
  5. AND klassenperleerling.Klassen_id = 45
  6. OR klassenperleerling.Klassen_id = 46
  7. OR klassenperleerling.Klassen_id = 47
  8. OR klassenperleerling.Klassen_id = 48
  9. OR klassenperleerling.Klassen_id = 49
  10. OR klassenperleerling.Klassen_id = 50
  11. OR klassenperleerling.Klassen_id = 51
  12. OR klassenperleerling.Klassen_id = 52
  13. OR klassenperleerling.Klassen_id = 53
  14. OR klassenperleerling.Klassen_id = 54
  15. OR klassenperleerling.Klassen_id = 55
  16. OR klassenperleerling.Klassen_id = 56
  17. OR klassenperleerling.Klassen_id = 57
  18. OR klassenperleerling.Klassen_id = 58
  19. OR klassenperleerling.Klassen_id = 59
  20. OR klassenperleerling.Klassen_id = 60
  21. OR klassenperleerling.Klassen_id = 61
  22. OR klassenperleerling.Klassen_id = 62
  23. OR klassenperleerling.Klassen_id = 63
  24. OR klassenperleerling.Klassen_id = 64
  25. OR klassenperleerling.Klassen_id = 65
  26. OR klassenperleerling.Klassen_id = 66
  27. OR klassenperleerling.Klassen_id = 68
  28. OR klassenperleerling.Klassen_id = 69
  29. ORDER BY naam ASC


Ik krijg 1318 waarden terug, dat klopt.. Maar hij staat toch wel een 30sec te laden..
Oorspronkelijk had ik een Query met 2 LEFT JOINS.. Omdat ik in principe ook de naam van de klas uit de tabel wil halen. Dit heb ik opgesplits door 1x alle klassen op te halen en in een array te steken (en dan zo om te zoeken.)..

Mijn vraag is nu, kan ik dat nog wat optimaliseren? Versnellen? De gegevens die ik ophaal zijn nu wel het minimum..

De bedoeling is ook dat er met Ajax dan gefilterd kan worden.. Maar dat zou normaal allemaal geen probleem mogen zijn eigenlijk. Het zijn NOG maar 1300 records...

EDIT: Er blijkt een fout te zitten. Ik mag nog maar 517 records hebben aangezien deze in school 2 zitten..

Tips zijn altijd welkom. Maar ik zal nog even verder zoeken.

EDIT 2: Haakjes rond de klas en de results zijn ok... Even zoeken hoe ik dat in codeigniter doe.

EDIT 3: Ik kan ook een limit opzetten en een paginanavigatie . Dat ik er zelf nog niet opgekomen was ;). Na wat verder opzoekwerk is query ook nog wat aangepast..
php code - Bekijk de code zonder highlighting - Klap code in 
  1. SELECT leerlingen.id, leerlingen.stamboeknr, leerlingen.naam, leerlingen.voornaam, klassenperleerling.Klassen_id
  2. FROM leerlingen
  3. LEFT JOIN klassenperleerling ON leerlingen.id = klassenperleerling.leerlingen_id
  4. WHERE Leerlingen.school_id = 2 AND
  5. klassenperleerling.Klassen_id IN (45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 68, 69)
  6. ORDER BY naam asc


mod edit: query op meerdere regels voor leesbaarheid

1 antwoord

Gesponsorde links
Offline Thomas - 21/01/2015 13:27 (laatste wijziging 21/01/2015 13:43)
Avatar van Thomas Moderator Ik weet niet precies hoe database-optimalisatie in MS SQL in elkaar steekt, maar als er kolommen zijn waar je op zoekt, bijvoorbeeld om tabellen aan elkaar te knopen, of kolommen waar je op sorteert, dan kan het een enorm verschil uitmaken of deze geïndexeerd worden.

Is de database waar je mee werkt een echte relationele database, of zijn dit allemaal losse tabellen? Bijvoorbeeld: is klassenperleerling.leerlingen_id een "echte" foreign key van leerlingen.id?

Als ik het vergelijk met MySQL. Stel je hebt twee MyISAM tabellen "leerlingen" en "klassenperleerling". In dat geval weten deze twee tabellen niet van elkaars bestaan. Kolommen die foreign keys bevatten zijn gewoon kolommen met getalletjes zonder een speciale betekenis. Als je dan een query schrijft waarbij je deze twee tabellen op een of andere manier combineert zal de query haast altijd traag zijn, het maakt eigenlijk niet eens zoveel uit hoe groot deze is. Deze query is en blijft traag totdat je relevante kolommen (waarop je zoekt of sorteert) gaat indexeren. Als je in dit voorbeeld een index zou aanbrengen op klassenperleerling.leerlingen_id dan heb je een snelle(re) lookup van leerling-ids. En als je de naam-kolom indexeert kan dit ook van positieve invloed zijn op de snelheid van de query wanneer je hier op sorteert.

EDIT: En als je InnoDB-tabellen gebruikt (waarbij je foreign keys kunt toekennen) dan kun je nog een stap verder gaan: indien je een kolom als foreign key bestempelt zorgt dat er niet alleen voor dat deze geïndexeerd wordt, maar je vertelt je database tevens iets over de structuur / samenhang van databasetabellen waarbij je beperkingen oplegt die bepalen wanneer (onder welke condities) welke data verwijderd mag worden. Dit zorgt vervolgens voor een (betere) waarborging van het kloppend houden van je data. Waar je bij MyISAM tabellen te pas en te onpas records uit je koppeltabel kunt weggooien zal een InnoDB database dit niet zonder meer toestaan, en weigeren wanneer dit zou zorgen voor inconsistentie in je data.

Primary keys worden (in MySQL, wss ook in MS SQL) automatisch geïndexeerd.

Een grotere factor voor de bepaling van de snelheid van de uitvoer van je query lijkt mij in eerste instantie de structuur van je database, en daarna (pas) de structuur van je query. Als je query efficiënt is, maar je database-opzet niet, wordt het heel moeilijk om hier iets efficiënts van te maken.

Kernwoord van dit relaas: INDEXERING - controleer dit, voeg zonodig toe en maak hier gebruik van.

EDIT2: A AND (B OR C) is iets compleet anders dan (A AND B) OR C. Het is beter om expliciet te zijn (want wat was precies de bedoeling van A AND B OR C? de eerste of de tweede variant?), de lezer van de query ziet dan ook direct wat de bedoeling van de opsteller was en kan dan sneller beoordelen of deze de goede keuze heeft gemaakt .
Gesponsorde links
Je moet ingelogd zijn om een reactie te kunnen posten.
Actieve forumberichten
© 2002-2024 Sitemasters.be - Regels - Laadtijd: 0.174s