13. juni 2006 - 15:21Der er
10 kommentarer og 1 løsning
Optimering af forespørgsel
Jeg er ved at lave avanceret statistik på en simpel database indeholdende ordrelinier. I store dele af statistikken bliver der kun brugt en tabel, som er listet nedenfor i forkortet udgave:
id (int) produkt (varchar 200) kundenummer (int) pakning (int)
Statistikken er koblet sammen med en grafisk brugergrænseflade, hvor brugeren har mulighed for selv at stykke en forespørgsel sammen, som jeg generer dynamisk.
Alt fungerer fint med undtagelse af en enkelt sum, som bliver vist ved siden af det resultSet, som den dynamiske forespørgsel giver anledning til.
Den problematiske sum skal "blot" summere antallet af forskellige kundenumre i den dynamiske forespørgsel og kan f.eks. se således ud:
SELECT COUNT(DISTINCT inner_nr) FROM ( SELECT Count(*) AS total, produkt, pakning, kundenummer AS inner_nr FROM ordrelinier GROUP BY produkt, pakning, inner_nr ) AS S1 JOIN ( SELECT Count(*) AS total, produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING total >= 10 AND total < 21 ) AS S2 ON S1.produkt = S2.produkt AND S1.pakning = S2.pakning
Den dynamisk genererede forespørgsel svarer til S2 i ovenstående, hvor denne i S1 bliver manipuleret til altid at medtage kundenummer (altså inner_nr) men tilgengæld undlade HAVING-betingelsen. Disse to forespørgsler bliver efterfølgende joinet for at kunne tælle antallet af forskellige kundenumre.
Så vidt jeg kan finde ud af, virker forespørgslen, men afhængig af det valgte kan den være ekstrem langsom (adskillige minutter). Test har vist, at afvikling af henholdsvis S1 og S2 max. tager nogle hundrede millisekunder - så performance lider i forbindelse med join.
Hvordan får jeg forespørgsel til at blive afviklet hurtigere? Skal der en anden forespørgsel til - eller skal der bruges indexes (og hvis ja, hvilke)??
Manuelle og semi-automatiske strategier for identitetsstyring virker - lige indtil nogen beder om dokumentation. For at undgå denne fare har DKTV taget kontrol over sin identitets- og adgangsstrategi.
Måske er det bare mig der ikke kan se det, men ville du ikke få det samme resultat (godt nok med et par felter mere returneret, men det betyder vel ikke noget) ved simpelthen at sige:
SELECT COUNT(DISTINCT kundenummer) AS kunder, produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING COUNT(*) BETWEEN 10 AND 20
Desværre er det ikke helt godt nok, da der for det første kommer flere linier med bl.a. count af kundenummer. Og disse linier kan jeg ikke blot ligge sammen, da der kan være gengangere imellem dem.
Der er muligvis flere problemer forbundet med søgningen...
Jeg har ganske enkelt brug for at kunne tælle, hvor mange FORSKELLIGE kundenumre, som har bidraget til salget af de ordrelinier, (hvor der er 10-20 af dem grupperet efter produkt og pakning).
Og problemet er, at jeg ikke blot kan lægge første kolonne sammen (altså kunder), da det giver 46, hvilket er forkert, da der er dubletter imellem... Det rigtige resultat er således 44
Okay, jeg har vist simplificeret problemstillingen lidt rigeligt. :-) Hvad så med denne her...?
SELECT COUNT(DISTINCT kundenummer) AS kunder, produkt, pakning FROM ordrelinier o1 INNER JOIN ( (SELECT produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING COUNT(*) BETWEEN 10 AND 20 ) AS o2 ON o1.produkt = o2.produkt AND o1.pakning = o2.pakning
SELECT COUNT(DISTINCT kundenummer) AS kunder, produkt, pakning FROM ordrelinier o1 INNER JOIN (SELECT produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING COUNT(*) BETWEEN 10 AND 20 ) AS o2 ON o1.produkt = o2.produkt AND o1.pakning = o2.pakning
SELECT COUNT(DISTINCT kundenummer) AS kunder, produkt, pakning FROM ordrelinier o1 INNER JOIN (SELECT produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING COUNT(*) BETWEEN 10 AND 20 ) AS o2 ON o1.produkt = o2.produkt AND o1.pakning = o2.pakning GROUP BY produkt, pakning
Hrmpf! Jeg glemmer åbenbart altid et par detaljer...
SELECT COUNT(DISTINCT o1.kundenummer) AS kunder, o1.produkt, o1.pakning FROM ordrelinier o1 INNER JOIN (SELECT produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING COUNT(*) BETWEEN 10 AND 20 ) AS o2 ON o1.produkt = o2.produkt AND o1.pakning = o2.pakning GROUP BY o1.produkt, o1.pakning
Ikke bare glemmer jeg detaljer, jeg er blind og døv!!!! For at det skal virke, kan man jo ikke bare gruppere to gange. Det bliver resultatet da ikke bedre af. Men så må vi jo droppe de to felter, og nøjes med at returnere summen. Det var jo også det du bad om i dit oprindelige spørgsmål.
SELECT COUNT(DISTINCT kundenummer) AS kunder FROM ordrelinier o1 INNER JOIN (SELECT produkt, pakning FROM ordrelinier GROUP BY produkt, pakning HAVING COUNT(*) BETWEEN 10 AND 20 ) AS o2 ON o1.produkt = o2.produkt AND o1.pakning = o2.pakning
Det virker jo helt fantastisk, når man kombinerer din forespørgsel med de indexes, som mysql ikke ville bruge ved mit forslag... Den har åbenbart nemmere ved at gennemskue dit forslag, som jo også er noget kortere og simplere :)
Det, som før tog minutter, tager nu kun få sekunder i værste fald.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
