Google+ Followers

woensdag 28 december 2011

Beperk je LIMIT

LIMIT en z'n ega OFFSET worden gebruikt om een resultset te beperken tot alleen een interessant plakje van de totale uitkomst. De pest is dat de database pas kan bepalen welke records binnen de opgegeven OFFSET en LIMIT vallen als hij de hele resultset ter beschikking heeft.

Dus als je een query hebt die een beste borrel werk doet om dingen te tellen en te sorteren, dan gaat de database eerst al die berekeningen doen en de uitkomst sorteren, om vervolgens alleen de gevraagde plak terug te geven.

Om dit soort queries sneller te maken kun je de manier van werken licht veranderen door de LIMIT en OFFSET alleen toe te passen op dat deel van de brondata waar hij ook echt op wordt toegepast.

Stel je wilt een lijst van klanten met de totale waarde van hun bestellingen, hun totale aantal transacties, gemiddelde bestelwaarde etc etc, gesorteerd op hun naam, beginnend op klant 15 in de lijst. Gewoonlijk zou je dan de usertabel raadplegen en die joinen aan diverse andere tabellen en/of subqueries:



SELECT *
FROM users
LEFT JOIN (SELECT COUNT(*) FROM transactions GROUP BY userid) AS transactioncounter
ON transactioncounter.userid=users.userid
ORDER BY user.name
LIMIT 15
OFFSET 15;

Nu wordt het aantal transacties voor *alle* users geteld en daar wordt een subset van 15 uit geplukt.

Door de werkvolgorde aan te passen wordt het aantal bewerkingen veel kleiner:




SELECT *
FROM (SELECT * FROM users ORDER BY users.name OFFSET 15 LIMIT 15) AS users
LEFT JOIN (SELECT COUNT(*) FROM transactions GROUP BY userid) AS transactioncounter
ON transactioncounter.userid=users.userid;


zaterdag 24 december 2011

DBSchema 6

In mijn voortdurende zoektocht naar het ultieme programma om ERD's te ontwerpen kwam ik weer een redelijke tegen: http://www.dbschema.com/

Importeert modellen van werkende databases en kan ze ook realtime aanpassen als je het model aanpast. Dit is lang niet altijd wat je wilt, maar het betekent wel dat direct 100% zeker weet dat wat je aangepast hebt ook echt werkt op je database.

De presentatie van het schema is zonder meer mooi te noemen. Lijnen die elkaar kruisen worden voorzien van een hobbeltje om over de kruisende lijn heen te springen. Ook is er een optie om lijnen die hetzelfde aangeven samen te voegen tot  één lijn die zich splitst. Als je een usertabel hebt waarvan de PK aan veertien andere tabellen wordt gekoppeld dan vertrekt er van de usertabel toch maar één lijn, wat bijzonder prettig leest.

Zoek niet naar een 'export database' functie, die is er niet. Wat er wel is is een 'backup as SQL' functie die effectief hetzelfde doet.

De voordelen:

  • Native Linux versie (geen gedoe met wine)
  • Bijzonder fraaie ERD presentatie
  • Ondersteunt alle populaire databases
  • Herkent PostgreSQL's schema constructie.
  • Wordt actief ontwikkeld
  • Niet duur, personal license kost $127 en met 20% korting via: http://migenblog.com/dbschema-promotion-discount-coupon-code.html ben je 88 Euro kwijt.
De nadelen:
  • Bevat nog de nodige bugs in de ondersteuning van databases, zo maakt het eerst de tabellen en dan pas de sequences, wat niet werkt als je nextval() toepast.

woensdag 7 december 2011

SQL brokjes, deel 24, CAST()

CAST() kan een boolean omzetten naar een integer en integers kun je optellen.

Dat maakt het onder andere mogelijk om binnene en groep te tellen hoeveel keer een bepaalde waarde voorkwam:

SELECT
SUM(CAST(veld=5 AS INTEGER)) AS aantal_veld_is_5
, SUM(CAST(veld=13 AS INTEGER)) AS aantal_veld_is_13
, iets
FROM tabel
GROUP BY iets;

dinsdag 6 december 2011

Ontwerp tipjes, deel 14: kolomnamen.

Kolomnamen moeten beschijvend zijn, ze moeten aangeven wat voor soort data er in de kolom zit en waar die data voor gebruikt dient te worden.

Dat betekent meteen al een schop tegen mijn persoonlijke irritatie nummer één, de 'id' kolom. Een kolom bevat niet 'id', een kolom bevat een userid, of een postid, vul maar in, maar het id is altijd van een entiteit binnen je database en daar dient de naam melding van te maken.

Waarom; nou los van "omdat het logisch is"; het is gewoon onhandig als al je tabellen een kolom 'id' hebben. Koppel de user tabel aan de forumposts en select daar eens * van, dan krijg je twee resultaatkolommen genaamd 'id'. Welke is dan van de user en welke van de forumposts? Dat kun je wel afvangen met aliassen: SELECT user.id AS userid, forumposts.id AS forumposts, maar.... uhm... als je in de query een onduidelijke kolomnaam gaat verduidelijken dan ben je toch wel fout bezig.

In het verlengde hiervan; in de forumposts tabel zit uiteraard het id van de user die de post gedaan heeft. Noem die kolom dus niet userid, want er staat niet zomaar een leuk userid in, er staat in wie de post gedaan heeft.; poster_userid dus. Er is geen twijfel mogelijk over wat er in de kolom 'poster_userid' staat.

En nu ik toch bezig ben. Afkortingen. Probeer ze te voorkomen en gebruik ze alleen als het echt volledig ingeburgerd is. Waarom: ik heb veel te veel databases gezien met een uid, een gid, een pid, tid, sid, tid en wid, waarbij de progammeurs een kladblokje naast hun toetsenwordt hadden liggen met de verklarende woordenlijst.
Afkorgen maakt je query niet duidelijker en op performance heeft het al helemaal geen enkele invloed.

zaterdag 3 december 2011

Relax ProSilence-Plus

Tijden veranderen, marketing blijft een groepje apart.

Neem Bosch. Ze maken een stofzuiger die erg stil is, dus krijgt hij 'silence' in de naam verwerkt. Daar kan ik nog mee leven,het klinkt in ieder geval beter dan "superstil", en wie spreekt er nog Nederlands, niewaar?

Maar we moeten als potentiele stofzuigerkoper natuurlijk ook denken dat het professionele stofzuiger is, want "professioneel" is goed, toch? En omdat stofzuigen best hard werk is (als je het goed doet) zet je "Relax" in de naam, dan denken al die suffe kopers dat ze op hun gat kunnen zitten tijdens het stofzuigen. Om het af te maken hang je dan het nietszeggende  maar positief klinkende "plus" er achter en hop, een naam die *niets* zegt, die niemand kan onthouden, maar die wel voldoet aan alle wensen van de moderne eikel marketing bureau's.

"Relax ProSilence-Plus".

Nee ik kan er echt niet bij.