I really don't know if it should take 2, 5 or 60 seconds. The numbers surprise me (in the bad maner). There is an incredible overhead when wrapping this query in a function. I wonder if there is an invocation of the function for every iteration.
-- Peter On Tue, Mar 3, 2015 at 2:51 PM, Federico Razzoli <[email protected]> wrote: > SELECT 1 was .42. What I find amazing is not the absolute numbers (it's an > old computer with desktop installed, etc) but the difference between the > tests. If SELECT 1 takes .42, test 2 shouldn't take more than 1 min... do > you agree? > > Federico > > > -------------------------------------------- > Mar 3/3/15, Justin Swanhart <[email protected]> ha scritto: > > Oggetto: Re: [Maria-discuss] stored programs > A: "Peter Laursen" <[email protected]> > Cc: "Federico Razzoli" <[email protected]>, "Maria Discuss" < > [email protected]> > Data: Martedì 3 marzo 2015, 14:28 > > You > probably have either a faster CPU or bigger cache on your > cpu. How long does benchmark select 1 take? You should > find it is faster on your system too, right? > --Justin > > Sent from my iPhone > On Mar 3, > 2015, at 6:18 AM, Peter Laursen <[email protected]> > wrote: > > Your 3rd test case takes > 2:02 in MariaDB 10.1 and 2:13 in MySQL 5.6 on my system > (when otherwise idle) . > -- > Peter > On Tue, > Mar 3, 2015 at 1:58 PM, Federico Razzoli <[email protected]> > wrote: > I made > some quick test to show what I mean by "performance > problem". Note that I'm not saying the the first > and the second test cases should perform equally. But there > is too much difference - see the conclusion. > > > > 1) > > > > MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT > 1)); > > +---------------------------------+ > > | BENCHMARK(50000000, (SELECT 1)) | > > +---------------------------------+ > > | 0 | > > +---------------------------------+ > > 1 row in set (0.42 sec) > > > > 2) > > > > DELIMITER || > > CREATE FUNCTION f() > > RETURNS TINYINT > > BEGIN > > RETURN 1; > > END || > > DELIMITER ; > > > > MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT > f())); > > +-----------------------------------+ > > | BENCHMARK(50000000, (SELECT f())) | > > +-----------------------------------+ > > | 0 | > > +-----------------------------------+ > > 1 row in set (2 min 5.70 sec) > > > > 3) > > > > DELIMITER || > > CREATE FUNCTION f(x TINYINT) > > RETURNS TINYINT > > BEGIN > > RETURN x; > > END || > > DELIMITER ; > > > > MariaDB [test]> SELECT BENCHMARK(50000000, (SELECT > f(1))); > > +------------------------------------+ > > | BENCHMARK(50000000, (SELECT f(1))) | > > +------------------------------------+ > > | 0 | > > +------------------------------------+ > > 1 row in set (3 min 35.20 sec) > > > > -- Conclusions: > > > > Times in seconds: > > > > 0.42 > > 125.70 > > 215.20 > > > > I don't know which ratio would be acceptable, but the > difference betweem a trivial query and a trivial function is > too high. > > Also, the difference between 2) and 3) is that the function > in 3) accepts and returns a parameter. Again, the > performance difference seems to me too high. > > > > Regards > > Federico > > > > > > > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > > Post to : [email protected] > > Unsubscribe : https://launchpad.net/~maria-discuss > > More help : https://help.launchpad.net/ListHelp > > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
