maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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 <federico_raz@xxxxxxxx>
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 <greenlion@xxxxxxxxx> ha scritto:
>
>  Oggetto: Re: [Maria-discuss] stored programs
>  A: "Peter Laursen" <peter_laursen@xxxxxxxxxx>
>  Cc: "Federico Razzoli" <federico_raz@xxxxxxxx>, "Maria Discuss" <
> maria-discuss@xxxxxxxxxxxxxxxxxxx>
>  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 <peter_laursen@xxxxxxxxxx>
>  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 <federico_raz@xxxxxxxx>
>  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     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>
>  Unsubscribe : https://launchpad.net/~maria-discuss
>
>  More help   : https://help.launchpad.net/ListHelp
>
>
>
>  _______________________________________________
>  Mailing list: https://launchpad.net/~maria-discuss
>  Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>  Unsubscribe : https://launchpad.net/~maria-discuss
>  More help   : https://help.launchpad.net/ListHelp
>
>