[SQL] query optimaliseren

dinsdag 3 juli 2007 12:08

Topicstarter

Momenteel heb ik een database met 3 tabellen:

Devices

Phonenumber

UserID

Devicename

Users

UserID

Username

...

Location

Phonenumber

Devicename

GMTDate

...

Nu is het de bedoeling om de laatste x aantal locaties van een bepaalde user op te vragen. Dus 1 user kan meerdere devices hebben! Ik gebruik hiervoor de volgende query

SQL:

SELECT loc.*, Users.Username 
FROM Locations loc, (

   SELECT COUNT(*) as rownumber, L1.Phonenumber,L1.GMTDate 
   FROM Locations L1, Locations L2 
   WHERE L1.Phonenumber = L2.Phonenumber 
   AND L1.GMTDate <= L2.GMTDate 
   GROUP BY L1.Phonenumber,L1.GMTDate
)rownum, Users, Devices 
WHERE loc.Phonenumber = rownum.Phonenumber 
AND loc.GMTDate = rownum.GMTDate 
AND loc.Phonenumber = Devices.Phonenumber 
AND Users.UserID = Devices.UserID AND Users.Username = '$user' 
AND rownum.rownumber <= $fetchnumber

Deze query duurt bij een location table van 8000 zon beetje 12s, niet te doen dus. Heeft iemand misschien een idee om deze query te optimaliseren.

Acties:

Verwijderd

Ooit gehoord van indexen?

dinsdag 3 juli 2007 12:13

Acties:

Verwijderd

Ik heb jou query niet doorgelezen maar wat ik van je verhaaltje begrijp wil je iets in de trant van:

code:

1	SELECT u.Username, l.* FROM Devices d, Users u, Location l WHERE u.Username = 'x' AND u.UserID = d.UserID AND d.Devicename = l.DeviceName LIMIT x

dinsdag 3 juli 2007 12:24

Acties:

dinsdag 3 juli 2007 16:26

De query van ProFox, maar dan met ON/USING clauses voor de join condities, en misschien nog een indexje op Devices.UserID.

Lees je in voor wat betreft het joinen van tabellen, en probeer je query zo eenvoudig mogelijk te houden. Subqueries/derivered tables en group by zijn voor helemaal niet nodig voor wat je wil bereiken.

{signature}

Acties:

dinsdag 3 juli 2007 16:38

Topicstarter

Punt is dat ik van alle devices van een bepaalde user de laatste x aantal locations wil hebben. Ik heb nu het volgende:

SQL:

 
SELECT l . *
FROM Devices d, Users u, Locations l
WHERE u.Username = 'x'
AND u.UserID = d.UserID
AND d.Phonenumber = l.Phonenumber
ORDER BY GMTDATE DESC
LIMIT 5

maar nu worden dus gekeken naar alle punten en daar de laatste 5 van genomen. Ik moet dus van elke device afzonderlijk. Verder is locations.devicename niet uniek.

Acties:

XangadiX

trepanatie is zóó kinderachtig

>>> PRG

Stoer; Marduq

dinsdag 3 juli 2007 16:42

Acties:

dinsdag 3 juli 2007 16:43

schopje in de goede richting: normaliseren.

[ Voor 53% gewijzigd door Mischa_NL op 03-07-2007 16:43 ]

Acties:

gorgi_19

Kruimeltjes zijn weer op :9

Mischa_NL schreef op dinsdag 03 juli 2007 @ 16:42:
schopje in de goede richting: normaliseren.

Wat heeft normaliseren te maken met query optimalisatie?

Digitaal onderwijsmateriaal, leermateriaal voor hbo

dinsdag 3 juli 2007 16:46

Acties:

whoami

Een goed (genormaliseerd) datamodel kan een serieuze invloed hebben op query-performance. (Omdat je dan geen rare truken moet uithalen bv).
Een gedenormaliseerd datamodel kan natuurlijk ook de performance positief beïnvloeden.

Maar in dit geval is 'normalisatie' een loze term, aangezien de 'database' al genormaliseerd is.

[ Voor 17% gewijzigd door whoami op 03-07-2007 16:46 ]

https://fgheysels.github.io/

dinsdag 3 juli 2007 16:48

Acties:

gorgi_19

Kruimeltjes zijn weer op :9

whoami schreef op dinsdag 03 juli 2007 @ 16:46:
Een goed (genormaliseerd) datamodel kan een serieuze invloed hebben op query-performance. (Omdat je dan geen rare truken moet uithalen bv).
Een gedenormaliseerd datamodel kan natuurlijk ook de performance positief beïnvloeden.

Maar in dit geval is 'normalisatie' een loze term, aangezien de 'database' al genormaliseerd is.

Kortom, wat maakt dat dan uit?

edit:

Ga eens niet snel je post editen

[ Voor 5% gewijzigd door gorgi_19 op 03-07-2007 16:48 ]

Digitaal onderwijsmateriaal, leermateriaal voor hbo

dinsdag 3 juli 2007 16:52

Acties:

dinsdag 3 juli 2007 16:58

whoami schreef op dinsdag 03 juli 2007 @ 16:46:
Een goed (genormaliseerd) datamodel kan een serieuze invloed hebben op query-performance. (Omdat je dan geen rare truken moet uithalen bv).
Een gedenormaliseerd datamodel kan natuurlijk ook de performance positief beïnvloeden.

Maar in dit geval is 'normalisatie' een loze term, aangezien de 'database' al genormaliseerd is.

Het feit dat ik in location devicename zie staan en in devices ook roept bij mij vragen op.

Of devicename moet zijn PK zijn maar dat lijkt me nogal stug

.

[ Voor 6% gewijzigd door Mischa_NL op 03-07-2007 16:54 ]

Acties:

dinsdag 3 juli 2007 17:06

Topicstarter

Mischa_NL schreef op dinsdag 03 juli 2007 @ 16:52:
[...]
Het feit dat ik in location devicename zie staan en in devices ook roept bij mij vragen op.

Wanneer een location wordt toegevoegd wordt eerst gekeken naar Devices.devicename. Vervolgens wordt die waarde in Locations.devicename gezet. Devices.devicename kan echter ten alle tijden veranderen.

Acties:

dinsdag 3 juli 2007 19:26

In dat geval snap ik je relaties tussen je tabellen helaas niet.

Acties:

woensdag 4 juli 2007 15:05

Topicstarter

Mischa_NL schreef op dinsdag 03 juli 2007 @ 17:06:
In dat geval snap ik je relaties tussen je tabellen helaas niet.

Devices.Phonenumber en Locations.Phonenumber zijn wel aan elkaar gelijk.

Acties:

Niemand_Anders

Dat was ik niet..

Cheazers, kun je misschien de output van explain {selectie query} een posten. Dan kunnen we zien of er correct gebruik wordt gemaakt van indexen. Daarnaast, hoelang duurt de group by (subquery) los?

@Mischa. Locations is een soort history tabel. Hoewel de naam van het toestel kan wijzigen moet in deze tabel de oude blijven staan. Devicename wordt ook niet gebruikt in de join query.

If it isn't broken, fix it until it is..

woensdag 4 juli 2007 15:31

Acties:

vrijdag 13 juli 2007 14:18

nevermind.

[ Voor 87% gewijzigd door Mischa_NL op 04-07-2007 15:32 ]

Acties:

maandag 16 juli 2007 23:06

Topicstarter

Niemand_Anders schreef op woensdag 04 juli 2007 @ 15:05:
Cheazers, kun je misschien de output van explain {selectie query} een posten. Dan kunnen we zien of er correct gebruik wordt gemaakt van indexen. Daarnaast, hoelang duurt de group by (subquery) los?

De subquery duurt zon 3sec korter dan de hele query. Die duur inmiddels 13sec. Hier onder de explaination.

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	<i>NULL</i>	<i>NULL</i>	<i>NULL</i>	<i>NULL</i>	9228	Using where
1	PRIMARY	Devices	ref	PRIMARY	PRIMARY	47	rownum.Phonenumber	1
1	PRIMARY	loc	ref	PRIMARY	PRIMARY	55	rownum.Phonenumber,rownum.GMTDate	1
1	PRIMARY	Users	eq_ref	PRIMARY	PRIMARY	8	robortt.Devices.UserID	1	Using where
2	DERIVED	L1	index	PRIMARY	PRIMARY	55		9228	Using index
2	DERIVED	L2	ref	PRIMARY	PRIMARY	47	robortt.L1.Phonenumber	79	Using where; Using index

Acties:

dinsdag 17 juli 2007 08:08

Topicstarter

Niemand_Anders schreef op woensdag 04 juli 2007 @ 15:05:
Cheazers, kun je misschien de output van explain {selectie query} een posten. Dan kunnen we zien of er correct gebruik wordt gemaakt van indexen. Daarnaast, hoelang duurt de group by (subquery) los?

schopje

Acties:

dinsdag 17 juli 2007 09:28

Werk de joins netjes uit (expliciet join type opschrijven en ON/USING clauses ipv ',' en WHERE). Kijk naar indexes voor de subquery, kijk desnoods naar een temp table of redundante samenvattings tabel ipv de subquery. Probeer de subquery te veranderen zodat daar alleen de rijen met devices van $user bekeken moeten worden. Die hele group by in de subquery is niet nodig, want je doet helemaal niets nuttigs met de aggregate functions: Die rownumber clause kan bij de korte, schone query gewoon als LIMIT. Etc. Etc.
Bijna alles wat je probeert kan die query verbeteren.

{signature}

Acties:

Niemand_Anders

Dat was ik niet..

Die hele where kan inderdaad in de ON worden opgenomen. Door de where clausible op de juiste plaatsen in de join op te nemen beperkt je de resultaten welke moeten worden samengevoegd.

Als die subquery beduidend langer duurt, moet je die dus zien te optimaliseren.

Hoe lang duurt deze query bij jouw:

code:

1
2
3

SELECT COUNT(L1.Phonenumber) as rownumber, L1.Phonenumber, L1.GMTDate 
   FROM Locations L1 inner join Locations L2 on  L1.Phonenumber = L2.Phonenumber 
   AND L1.GMTDate <= L2.GMTDate  GROUP BY L1.Phonenumber,L1.GMTDate having rownumber <= $rownumber

Mocht je een foutmelding krijgen op having gebruik dan having count(L1.Phonenumber) <= $rownumber.

In deze query zitten twee optimalisaties. De eerste geeft explicit aan op veld veld je de count wilt uitvoeren ipv op alle velden (*). De tweede optimalisaties is de (inner) join constructie.

Je volledige query zou dan worden: (Nog een optimalisatie is alle velden van loc explicit vermelden zodat mysql niet eerst een lookup hoeft te doen in de systeemtabellen)

code:

SELECT loc.* FROM Locations loc inner join 
(
SELECT COUNT(L1.Phonenumber) as rownumber, L1.Phonenumber,L1.GMTDate 
   FROM Locations L1 inner join Locations L2 on  L1.Phonenumber = L2.Phonenumber 
   AND L1.GMTDate <= L2.GMTDate  GROUP BY L1.Phonenumber,L1.GMTDate 
   having rownumber <= $rownumber)
rownum on loc.Phonenumber = rownum.Phonenumber AND loc.GMTDate = rownum.GMTDate 
inner join Devices on loc.Phonenumber = Devices.Phonenumber 
and Devices.UserID = (select UserID from Users where Users.Username = '$user' )

Ik heb Users.Username uit de select weggelaten omdat je deze in de where explicit zetten (Users.Username='$user'). Die weet je dus al bij het uitvoeren van de query en hoef je dus niet nog een keer op te vragen. Als je UserID al weet, laat dan ook de subquery welke userid ophaalt hier achterwege en gebruik dan direct Devices.UserID=$userid.

If it isn't broken, fix it until it is..

dinsdag 17 juli 2007 16:00

Acties:

dinsdag 17 juli 2007 17:07

Topicstarter

Niemand_Anders schreef op dinsdag 17 juli 2007 @ 09:28:
Hoe lang duurt deze query bij jouw:

code:

1
2
3

SELECT COUNT(L1.Phonenumber) as rownumber, L1.Phonenumber, L1.GMTDate 
   FROM Locations L1 inner join Locations L2 on  L1.Phonenumber = L2.Phonenumber 
   AND L1.GMTDate <= L2.GMTDate  GROUP BY L1.Phonenumber,L1.GMTDate having rownumber <= $rownumber

Het zit hem wel in dit gedeelte, ruim 12 seconden duurt deze query.

Acties:

Niemand_Anders

Dat was ik niet..

Dan zullen de indexen op Locations niet goed staan. Probeer anders eens een clustered index op Phonenumber en GMTDate aan te maken.

Hoelang duurt de query als je de count en de group by weg laat?

If it isn't broken, fix it until it is..

dinsdag 17 juli 2007 20:23

Acties:

woensdag 18 juli 2007 09:02

Topicstarter

Niemand_Anders schreef op dinsdag 17 juli 2007 @ 17:07:
Dan zullen de indexen op Locations niet goed staan. Probeer anders eens een clustered index op Phonenumber en GMTDate aan te maken.

Hoelang duurt de query als je de count en de group by weg laat?

Beide zijn van het primary type. query gaat aanzienelijk sneller zonder count/group

Acties:

D4Skunk

Kind of Blue

probeer dit eens (ik ga er van uit dat je SQL server gebruikt):

SQL:

SELECT l.*, u.Username 
FROM Locations l, 
     Users u, 
     Devices d
WHERE l.Phonenumber = d.Phonenumber 
AND l.DeviceName=d.DeviceName
AND u.UserID = d.UserID 
AND u.Username = '$user' 
AND l.GMTDate in 
    (select TOP 5 l2.GMTDate
     from Locations l2
     where l2.devicename=l.devicename
     and l2.phonenumber = l.phonenumber
     order by l2.GMTDate desc)

De subselect in oracle

SQL:

    (select l2.GMTDate
     from Locations l2
     where l2.devicename=l.devicename
     and l2.phonenumber = l.phonenumber
     and rownum <= 5
     order by l2.GMTDate desc)

Overigens zou ik je wel aanraden om overal surrogate primary keys gebruiken; dat maakt de bovenstaande query heel wat duidelijker zonder domeinkennis. Als je surrogate primary keys zou gebruiken, zou je kunnen zeggen :

SQL:

AND l.id in 
    (select TOP 5 l2.id
     from Locations l2
     where l2.device_id=l.device_id
     order by l2.GMTDate desc)

Als je geen surrogates gebruikt zou ik aanraden een index aan te maken op locations[devicename,phonenumber,gmtdate]

Even nog een kleine verduidelijking :
Bij jouw query ga je een tijdelijke resultset gaan opbouwen die dus niet geindexeerd is. Om deze uit te voeren moet de database een carthesian join uitvoeren (maw alle rijen van tabel a met alle rijen van tabel b vergelijken), wat dus een enorme impact heeft op performantie. Wat ik hierboven in de query uitvoer, is de brongegevens mijn query zo klein mogelijkhouden, en er voor zorgen dat er in elke stap van het execution plan indexen gebruikt kunnen worden; dat zou de performantie drastisch moeten verhogen.

[ Voor 52% gewijzigd door D4Skunk op 18-07-2007 09:14 ]

woensdag 18 juli 2007 09:30

Acties:

woensdag 18 juli 2007 09:47

cheazers schreef op dinsdag 17 juli 2007 @ 20:23:
[...]
Beide zijn van het primary type. query gaat aanzienelijk sneller zonder count/group

Tja, dat had ik dus al gezegd, Voutloos in "[SQL] query optimaliseren" , maar om duistere redenen worden mijn tips genegeerd.

{signature}

Acties:

D4Skunk

Kind of Blue

Voutloos schreef op woensdag 18 juli 2007 @ 09:30:
[...]
Tja, dat had ik dus al gezegd, Voutloos in "[SQL] query optimaliseren" , maar om duistere redenen worden mijn tips genegeerd.

offtopic:
Zonder arrogant te willen zijn, en als dit zo overkomt bied ik alvast m'n excuses aan :
Je bedoelt inderdaad hetzelfde, maar misschien had je beter de query even uitgeschreven, want ik denk dat je post nogal moeilijk te verstaan is voor iemand die niet zo thuis is in databases.

woensdag 18 juli 2007 09:54

Acties:

woensdag 18 juli 2007 16:14

offtopic:
Nee, ik geef express alleen hints en niet de volledige oplossing. Zonder arrogant te willen zijn, is het schrijven van een snellere query voor mij minder moeite dan het schrijven van vorige post. Maar ik ga graag uit van enig initiatief om de daadwerkelijke implementatie zelf uit te zoeken en geef daarom dus geen kant en klare oplossingen.

{signature}

Acties: