[MySQL] Sortering en UDF vertraagt query extreem

[Verwijderd]

Hallo,

tsja, mijn probleem is een beetje moeilijk uit te leggen in de topictitel. Maar het is als volgt.
Ik heb een stored procedure, daarin selecteer ik een artikel:

code:

SELECT `artikel`.`code`, artikel_prijs(_id, `artikel`.`code`, 'vk') FROM `artikelen`

Query is veel uitgebreider ivm prijsgroepen, meerdere valutas en nog meer informatie maar dat is nu niet relevant.
Zoals je kan zien gebruik ik een eigen functie om de prijs te berekenen aangezien deze afhankelijk is van meerdere factoren en ik deze gebruik in meerdere stored procedures. Op het moment heb ik die berekening dus los in elke procedure staan en wil ik deze in een functie gaan zetten zodat aanpassingen daaraan op een centrale plek kunnen gebeuren.
Mijn functie gebruik 3 variabelen, het id van de ingelogde user, de artikelcode, en het type prijs (verkoopprijs of adviesprijs). Het probleem zit hem nu dus in het aanroepen van mijn functie, daarin heb ik dus de artikelcode nodig die ik in de zelfde query selecteer. Op het moment lijkt het dat hij alle artikelcodes invult ipv de artikelcode voor het huidige artikel. Daardoor word de prijs niet juist berekend en duurt de query ipv 1.2 seconde nu 45 seconde.
Kortom, hoe kan ik een waarde die ik selecteer, in dezelfde query weer gebruiken voor het aanroepen van een functie?

[ Voor 0% gewijzigd door Verwijderd op 11-12-2006 11:59 . Reden: typo ]

[Robbemans]

Als je functie goed is en je gebruikt MSSQL, dan zou het gewoon moeten werken.

Tipje mijnerzijds: vermijd functies zo veel mogelijk. De impact op je performance is oneglooflijk groot, zeker als je ze in een where-clausule gaat gebruiken.

Wat ik met wel afvraag is waarom je de tabel- en veldnamen tussen qutes plaatst...

[ Voor 15% gewijzigd door Robbemans op 11-12-2006 11:50 ]

[Verwijderd]

SQL:

DECLARE @ARTIKELCODE VARCHAR(20)

SELECT TOP 1 @ARTIKELCODE = artikel.code, artikel_prijs(_id, artikel.code, vk) FROM artikelen

PRINT @ARTIKELCODE

denk dat je naar zo'n constructie moet kijken. dit is iig voor MS SQL

[ Voor 4% gewijzigd door Verwijderd op 11-12-2006 11:51 ]

[Verwijderd]

Vergeten, ik werk met MySql 5.0
En de bedoeling is dus dat mijn query meerdere artikelen met bijbehorende prijs oplevert!

[ Voor 56% gewijzigd door Verwijderd op 11-12-2006 12:13 ]

[Verwijderd]

Ok, dit is dus de echte query die mijn stored procedure uitvoert, ik weet dat er paar vieze dingen inzitten maar daar gaat t nu niet om haha:

SQL:

SELECT 
`artikel`.`Voorraad`,
`artikel`.`artikelcode`,
`artikel`.`Merk`,
`artikel`.`Type`,
grossier_prijs(1,`artikel`.`artikelcode`,'kb') AS KBprijs,
grossier_prijs(1,`artikel`.`artikelcode`,'vk') AS VKprijs,
ROUND((100 - ((100 / `prijs`.`KBprijs`) * `prijs`.`VKprijs`)),1) as Korting,
`artikel`.`EAN code`,
`assortiment`.`Art code`,
`grossier`.`Valuta` as Code
FROM `artikel`
INNER JOIN `prijs` USING (`artikelcode`)
INNER JOIN `grossier` USING (`Prijsgroep Id`)
LEFT OUTER JOIN `assortiment` ON ( `artikel`.`artikelcode` = `assortiment`.`artikelcode` AND `grossier`.`Id` = `assortiment`.`GrossierId` )
WHERE
`grossier`.`Id` = 1 AND
(`artikel`.`klant artikel`='y' OR `artikel`.`artikelcode` LIKE '1|%') AND
(`grossier`.`Nulvoorraad`='j' OR `artikel`.`Voorraad`>0) AND
((`VKprijs` > 0) OR (`KBprijs`) > 0 )  AND
`artikel`.`klant artikel` = 'y'
ORDER BY `Merk` asc LIMIT 0,50

als ik dus op de plaats van de artikelcode in de grossier_prijs handmatig een artikelcode zet is mijn query wel snel....

[ Voor 12% gewijzigd door Verwijderd op 11-12-2006 13:21 . Reden: query duidelijker geplakt :) ]

[Verwijderd]

Ok, ik ben een stuk verder. Ik heb het probleem gevonden.
Mijn query levert zonder "LIMIT" 5101 artikelen op, het sorteren gebeurt voor de limit. Dus hij gaat voor alle 5101 artikelen de prijs berekenen, dan sorteren, en dan de alleen de eerste 50 resultaten terug geven. Eigenlijk moet ik dus eerst alleen selecteren waarop ik sorteer, en daarna de rest van de informatie eraan toevoegen. Dat zou het snelst zijn (ik had verwacht dat mysql dit zelf wel deed maarja).

MET sortering MET limit: fetchen (0.0077s) uitvoer (7,7276s) (50 rijen)
MET sortering ZONDER limit: fetchen (0.5692s) uitvoer (5.5607s) (5101 rijen)
ZONDER sortering ZONDER limit: fetchen (4.0490s) uitvoer (0.0471s) (5101 rijen)
ZONDER sortering MET limit: fetchen (0.0075s) uitvoer (0.0297s) (50 rijen)

Tijden volgens MySQL Query Browser

[ Voor 27% gewijzigd door Verwijderd op 12-12-2006 12:13 ]

[Robbemans]

Hoe ziet je FUNCTIE er uit?

[Verwijderd]

SQL:

DELIMITER $$

DROP FUNCTION IF EXISTS `grossier_prijs` $$
CREATE FUNCTION `grossier_prijs`(
_prijs DECIMAL(9,2),
_valuta DECIMAL(9,2)
) RETURNS decimal(9,2)
BEGIN
DECLARE _result DECIMAL(9,2);
set _result = ROUND(_prijs * _valuta , 2);
RETURN _result;
END $$

DELIMITER ;

Meer niet op het moment Dit heb ik zo gedaan, om prijsberekening op 1 centrale plek te kunnen onderhouden

Dit is overigens alweer na aanpassing, deze werkt dus niet met bovenstaande query. Toen zocht de functie zelf de prijs op voor het artikel+ingelogde user. Maar omdat de functie dus word uitgevoerd voor de limit, duurde de prijsberekening zodanig lang dat dit een snellere oplossing is.

[ Voor 26% gewijzigd door Verwijderd op 12-12-2006 13:18 ]

[Crazy D]

Maar het lijkt me redelijk logisch dat ie eerst alle rows bepaald, en dan pas de limit kan doen, aangezien je in je where clause de resultaten gebruikt van je functie (en dus kan ie niet simpelweg de 1e 50 records ophalen volgens je sortering.... al zou je dan misschien nog mogen verwachten dat ie stopt zodra er 50 resultaten zijn).

Overigens lijkt je functie mij op dit moment nogal zinloos. Juist met zaken als klantspecifieke prijzen en zo erbij halen wordt het functie-waardig, nu doe je enkel de 2 params vermenigvuldigen en afronden.

[Verwijderd]

Crazy D schreef op dinsdag 12 december 2006 @ 13:22:
Maar het lijkt me redelijk logisch dat ie eerst alle rows bepaald, en dan pas de limit kan doen, aangezien je in je where clause de resultaten gebruikt van je functie (en dus kan ie niet simpelweg de 1e 50 records ophalen volgens je sortering.... al zou je dan misschien nog mogen verwachten dat ie stopt zodra er 50 resultaten zijn).

Overigens lijkt je functie mij op dit moment nogal zinloos. Juist met zaken als klantspecifieke prijzen en zo erbij halen wordt het functie-waardig, nu doe je enkel de 2 params vermenigvuldigen en afronden.

Ja de bedoeling was dus ook hierin klantspecifieke prijzen te gaan berekenen. Maarja, toen duurde me query dus 2 minuten ipv 2 seconde...
Ook als ik het resultaat van mijn functie niet in het where path gebruik is de query zo traag

Zo is mijn functie incl groepspecifieke prijzen, komt nog klantspecifieke bij in, zodra de query die deze aanroept weer op normale snelheid werkt:

SQL:

DELIMITER $$

DROP FUNCTION IF EXISTS `grossier_prijs` $$
CREATE FUNCTION `grossier_prijs`(
_id INT,
_code VARCHAR(20),
_type ENUM('vk','kb')
) RETURNS decimal(9,2)
BEGIN
DECLARE _result DECIMAL(9,2);
SELECT CAST(ROUND(IF(_type='vk',`prijs`.`VKprijs`,`prijs`.`KBprijs`)*`valuta`.`Koers`,2) AS DECIMAL(9,2)) INTO _result FROM `prijs`
INNER JOIN `grossier` ON (`prijs`.`Prijsgroep Id` = `grossier`.`Prijsgroep Id`)
INNER JOIN `valuta` ON (`grossier`.`Valuta` = `valuta`.`Code`)
 WHERE `grossier`.`Id` = _id AND `prijs`.`artikelcode` = _code;
RETURN _result;
END $$

DELIMITER ;

[ Voor 35% gewijzigd door Verwijderd op 12-12-2006 13:36 ]

[ACM]

Als je in mysql 5.0 eindelijk limits in subqueries mag stoppen kan je zoiets doen:

SQL:

select a.*, funct(a.bla)
from
( select ... from tabellen ... order by ... limit 0, 50) as a

[Verwijderd]

ACM schreef op dinsdag 12 december 2006 @ 13:40:
Als je in mysql 5.0 eindelijk limits in subqueries mag stoppen kan je zoiets doen:

SQL:

select a.*, funct(a.bla)
from
( select ... from tabellen ... order by ... limit 0, 50) as a

Ik zie niet helemaal wat je hiermee kan bereiken, hoezo een subquery in je from statement?

edit:

Ik snap waar je hiermee heen wilt, maar in deze constructie is sorteren op prijs niet meer mogelijk
Ik snap niet, waarom prijsberekening in de query zelf zonder functie wel snel gaat (ook met sorteren) en in een functie niet... of eigenlijk deels ook wel ... hmm vage situatie

[ Voor 26% gewijzigd door Verwijderd op 12-12-2006 14:14 ]

[Verwijderd]

Ok, conclusie ...
hij is dus langzamer, omdat hij per record nu 2x de prijs berekent, en dus 2x een extra select doet waarbij 3 tabellen (2 Inner join's) nodig zijn.
Als ik de prijsberekening in elke stored procedure zou plaatsten is het immers wel snel.
Dus, mijn (vieze) oplossing op het moment is, dat de functie een string teruggeeft met daarin de sql voor de prijsberekening. En dat de stored procedure deze dus concat met de rest van de query en dan uitvoert. Dit is mooi te doen, tot er dus in de toekomst een extra tabel voor de prijsberekening nodig is, dan moet alsnog alle stored procedures aangepast worden. Maar het is niet anders op het moment, ik vind 2 minuten voor 5000 records gewoon te lang

[igmar]

en wat zegt een explain op een werkende query ? > 10 seconde wijst op het gebruik van tmp tables, en die komen vaak van je order by.