Bij een website met een community zoals AB Concerts, Vorst Nationaal, Capitole Gent en Tagger.fm loopt het aantal queries heel snel op.
Naast het intensievere werk voor de MySQL-server kan dit ook een vertraging op de site opleveren. De data moet namelijk opgehaald worden, verwerkt worden en getoond worden aan de bezoeker.
Bij wijze van voorbeeld: Stel je voor dat je een array hebt met daarin de ID's van de vrienden van de huidige ingelogde gebruiker. Wat de meeste developers nu doen is een loopje starten en in deze loop de waardes ophalen per friend-ID.
// loop friends
foreach($friendIds as $id)
{
// get friend data
$friendData = $this->db->getRecord('SELECT p.nick, p.avatar, ...
FROM profiles AS p
WHERE p.id = '. $id .';');
// code to generate output
...
}
?>
In het bovenstaande voorbeeld hebben we dus een groot aantal queries (lees n+1), namelijk:
- 1 om de ID's van de vrienden op te halen
- 1 per vriend om zijn specifieke data op te halen
Met behulp van de MySQL-functie IN() kunnen we dit terug brengen naar 2 queries.
// get all profiledata at once
$profileData = $this->db->retrieve('SELECT p.nick, p.avatar, ...
FROM profiles AS p
WHERE p.id IN ('. implode(',', $friendIds) .');');
// loop profiles
foreach($profileData as $friend)
{
// code to generate output
...
}
?>
Zoals je nu kan zien hebben we 2 queries:
- 1 om de ID's van de vrienden op te halen
- 1 om de data voor al deze vrienden in één keer op te halen
Opmerking: Ik wil er wel de nadruk op leggen dat dit een fictief voorbeeld is. De code is dus ook niet terug te vinden in de vernoemde websites. In dit voorbeeld zou het nog nuttiger zijn om niet alleen de ID's op te halen maar meteen de data, maar dit terzijde.
Door Anon 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Thank you, Captain Obvious!
Door Wouter Cuypers 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Als je dat niet wist hoor je wel niet thuis in web development.
Je kan daar toch ook 1 query van maken door de SELECT van je vrienden in de IN() te plaatsen?
Door Tijs 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
@wouter: en toch zie ik dat veel (beginnende) developers in de loop queries uitvoeren.
En je kan inderdaad met een sub-query werken, of een join.
Dit is zeker en vast niet de heilige graal, eerder een voorbeeld om aan te tonen dat je queries in loops makkelijk kan omzeilen.
Door Matthias 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Relevante blogpost, maar lijkt me dat ie nog een conclusie mist: wees kritisch voor je eigen code. Kan het immers efficiënter? Vaak wel. Maar de beste constructs in een taal krijg je pas onder de knie door ervaring.
Een beginnende developer hoort eigenlijk een mentor met een aantal jaar ervaring te hebben. Bv. zet een oudere werknemer samen met een beginner. Doe aan peer review met alle developers.
Hetzelfde met stagairs. Beschouw die niet als goedkope werkkrachten. Zet ze in op een relevant project waar ze effectief iets uit kunnen leren. Lijkt evident, maar dat is het blijkbaar niet als ik Tijs hoor. Misschien moet je later met diezelfde mensen immers aan de slag!
Tijdverlies? Kijk maar even naar de verhalen op thedailywtf.com. Duurzame code is echt wel een pre.
Door Geert 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Ik neem aan dat
IN(1, 2, 3)ook sneller is dan een constructie als:id = 1 OR id = 2 OR id = 3.Door mlitn 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Geert: Neen, de OR zal even snel werken (al kies je om esthetische redenen natuurlijk best voor de IN omdat deze duidelijker is). MySQL zal deze intern allebei op dezelfde manier verwerken.
Door Bramus! 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
@Anon, @Wouter: Als lector webtechnieken zijnde kan ik bevestigen dat dit voor sommigen niet "so obvious" is.
Denk hierbij aan vers afgestudeerden (niet elke school doceert de studiegebieden even grondig!) of aan mensen die iets in zelfstudie volgen: Veelal leren ze de twee vakken apart en bij het samenvloeien doen ze dan maar wat naargelang eigen intuďtie.
Het overlappingsgebied van deze twee studiegebieden vergt zeker extra aandacht en kennis: er is nog steeds een verschil tussen een query schrijven en dé query schrijven (theorie vs. praktijk). Sterker nog: Soms voer je bij het ontwerpen extra (de)normalisaties door of trek je zaken uit elkaar (of hou je net data dubbel bij!) omdat het in de praktijk makkelijker/beter werken is. Het "bluts met de buil" principe is hier zeker van toepassing ... en dat gebackt door je eigen ervaring, iets wat bij een vers afgestudeerde nog slechts een fractie van wat het worden zal is.
Maak u echter geen zorgen hoor, alle studenten die ik sinds dit jaar onder m'n hoede heb krijgen dit er in gelepeld (dat komt ervan van iemand uit de industrie te plukken en als lector voor een groep te stellen - de uitgebreide cursussen worden gefinetuned met extra real life situations) en tegen het volgende academiejaar wordt een volledige OA (beter bekend als "een vak") onder handen genomen om meer praktijkgericht te zijn (viz. invoeren van usecases uit het bedrijfsleven gegrepen).
As you were soldier; Move along.
Door Thijs 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
En toch moet ik Anon en Wouter wel volgen, het blijft vrij obvious ;-)
In Bramus! voordeel dan weer, vorig jaar heb ik de volledige persistence layer van een klasgenoot moeten herschrijven door precies deze fout. Dit in combinatie met een Access database was nefast voor performance.
Ik verkies echter vaak de OR oplossing, maar dat ligt eerder aan mijn db abstraction.
Door Tijs 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Heren, of het nu obvious is of niet. Er worden wel meer dingen niet correct gedaan terwijl het vrij obvious is. bijvoorbeeld http://www.example.com werkt maar http://example.com niet, geen comments schrijven, begrijpend lezen, ... Maar dit doet er helemaal niet toe.
Deze blogpost is niet voor mensen die al 20 jaar ervaring hebben, het is voor mensen die dit niet weten - kennis delen, weet je wel.
Het lijkt me dus ook beter de nutteloze discussie of het nu obvious is of niet te stoppen en de kennis die jullie ongetwijfeld hebben te delen met mensen die ze niet hebben.
Just my humble opinion.
Caption Obvious
Door Mr. Jean 16/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Volledig eens met Tijs!
In mijn 3 jaar ervaring met PHP en MySQL is dit iets wat ik nog niet wist maar nu dus zeer goed zal onthouden!
Door Tijs 17/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Tim De Baere schreef op http://www.facebook.com//note.php?note_id=340257705726&comments onderstaande, ik zet het ook even hier zodat iedereen kan bijleren:
"Arrays zijn vies!
De IN van SQL mag dan wel het aantal db fetch-opdrachten beperken... maar het lijkt me nog steeds niet de goede oplossing voor dit probleem. IN negeert nl. indexen op database niveau.
Een INNERJOIN van de user met de profile tabel zou slecht 1 db-fetch als gevolg hebben. De resulterende recordset bevat dan alle data van alle vrienden van de user... die zonder array of (extra db-fetches) te loop-en vallen.
Of vergis ik me?"
Door Tijs 17/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
@Tim: In de opmerking staat dan ook dat het nuttiger zou zijn om direct alle data op te halen. Het is slechts een voorbeeld.
Ik vind nergens iets terug in de documentatie van MySQL (tenzij ik erover lees) dat IN de indexes zou negeren.
Door Frederick 18/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Ik weet niet goed hoe dat zit bij MySQL, maar ik kan alvast zeggen dat bij MSSQL IN niet persé trager zal zijn dan EXISTS of JOIN.
De voorwaarde is wel dat men een subquery op een geindexeerde tabel gebruikt in de IN-lijst en geen opgebouwde lijst uit een array. Die array is helemaal niet geindexeerd en daar zit hem juit het probleem. Dat haalt de performantie helemaal onderuit!
Door mlitn 18/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
IN() gaat wel indexes gebruiken bij volgend gebruik: SELECT * FROM table WHERE column IN (1, 2, 3)
Dit is voor MySql een range, en hij gaat de indexes gebruiken.
Werk je echter met subqueries, dan zal je primary tabel de indexen inderdaad niet gebruiken.
Door Anon 18/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Hey Netlash, ik heb gehoord dat je tegenwoordig CSS kan gebruiken i.p.v. frames en tables. Klopt dit gerucht? Schrijven jullie hier even een post over?
Door Tijs 18/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
@Anon: prachtige toegevoegde waarde. Misschien kan je me nog iets bijleren, ik stel voor dat je eens binnenspringt op kantoor en dan bespreken we dat eens bij een tas koffie.
Door Tim De Baere 18/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
In SQL Server is de prestatie van JOIN tov IN wel sterk voelbaar imo. Probeer maar eens op een tabel met miljoenen rec's...
Geen verstand van mySql...
wat ik wel weet is dat
sommige SQL Engines volgend statement slecht optimaliseren :
SELECT IN (1,2,3, ... , 255)
FROM
WHERE
zal vertaald worden naar:
SELECT = 1 OR
= 2 OR
= 3 OR
= 4 OR
= 5 OR
FROM
WHERE
...
Dus 255 individuele OR-opdrachten.
De data engine zal dan een index seek doen (met index) of een tablescan (zonder index op voor elke OR.
De runtime is proportioneel aan het aantal elementen in de IN-list. Hoe langer de IN range, hoe langer de runtime.
Ik zweer bij JOIN's.
Door mlitn 25/11/09 (2 jaren geleden)
re: Queries uitsparen met behulp van IN()
Uiteraard gaat
SELECT * FROM tabel1 WHERE id IN (SELECT fk FROM tabel2);
of
SELECT fk FROM tabel2;
SELECT * FROM tabel1 WHERE id IN (<waardes van hierboven>);
altijd stukken trager zijn dan:
SELECT * FROM tabel1 INNER JOIN tabel2 ON tabel1.id = tabel2.fk;
En inderdaad, in het bovenstaande voorbeeld was een join de aangewezen keuze, zoals in de opmerking wordt aangegeven. Het punt was echter om te tonen dat een loop van queries vaak kan vermeden worden. De data waarop je wenst te zoeken is niet altijd beschikbaar in je database of wordt net opgeslagen om een ingewikkelde query niet al te vaak te hoeven uit te voeren. Denk maar aan gegevens van derden of een 'selectie' in mysql die nogal zwaar is en in 7 queries voorkomt (dan splits je deze best even). In dit geval gaat een IN altijd stukken liever zijn voor je database, dan een hele loop aan queries.
En uiteraard, elke ietwat zichzelf respecterende programmeur is zich bewust van het bestaan van IN en gebruikt dit natuurlijk ook wanneer nodig. Maar net zoals alles wat obvious, blijkt dit niet altijd even vanzelfsprekend, denk maar aan pakweg user input sanitation.
Door pregnancy travel insurance 11/10/10 (1 jaar geleden)
re: Queries uitsparen met behulp van IN()
Misschien moet iemand een soort 'Immo API' of web service verzinnen waarmee immokantoren makkelijk hun nieuwe panden op mandro.be (en andere sites) kunnen publiceren? En dan makers van immosoftware overtuigen om de API te integreren in hun software of zo (ik zeg maar wat; heb geen verstand van hoe de immomarkt werkt).
Door jogos de corrida 22/10/10 (1 jaar geleden)
re: Queries uitsparen met behulp van IN()
Ik heb gister de IN() code geprobeerd en kreeg paar foutmeldingen.
Door mlitn 22/10/10 (1 jaar geleden)
re: Queries uitsparen met behulp van IN()
@jogos:
Wat waren de fouten?
Door jogos de corrida 24/10/10 (1 jaar geleden)
re: Queries uitsparen met behulp van IN()
@mlitn Ik heb het al gefixt! Thanks
Er zijn nog geen tweets over dit artikel.