Hi, Just to avoid ambiguity: the stored function is executed as byte code in each call, and the native function is called by the byte code for each call.
--Justin On Tue, Mar 3, 2015 at 7:04 AM, Justin Swanhart <[email protected]> wrote: > Hi, > > The server parses the function into a form of bytecode which is then > cached until the routine changes. In the past any routine change would > invalidate cache for all routines and that sometimes can still happen but > it is rare. The server then interprets the bytecode for every call to the > function and yes, the function is executed every time unless you memoize > the function ( if(function_input != @last_input) then @last_input = > function_input; return @last_result := function(function_input); else > return @last_result; > > On Tue, Mar 3, 2015 at 6:59 AM, Peter Laursen <[email protected]> > wrote: > >> 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
