MartinH <mgheal (AT) gmail (DOT) com> wrote:
I don't think this is real. In fact I have strong reason to believe
that a non-cursor version will be faster (but not significantly).
To avoid measuring only cache warming effects, you should execute
each version a few times. And you should (for each version) start
from the same position: (re)start the MySQL server before running
the test.

Of course the comparison makes only sense if you run the same query.
Else you will just see the effect of different execution plans.


XL

On 2010-12-20 10:29, Axel Schwenke wrote:
Axel, can you elaborate on why you expect the non-cursor version to be
faster, is it due to context switching or some sort of similar effect?

I don't fancy cursors myself but I would expect the cursor variant to do
a lot less work than my non cursor based version:

select reporting_day into res
from (
select x.reporting_day,
(select count(y.reporting_day)
from reporting_days as y
where day_type = ''
and y.reporting_day < x.reporting_day
and y.reporting_day >= Start_Date
) as cnt
from reporting_days as x
where day_type = ''
and reporting_day >= Start_Date
) as z
where cnt = floor(Units_to_Add);

Given a table with say 1000 rows and start_date somewhere in the middle
I'ld expect the query to *indirectly* touch 500+499+...+1 rows.
Indirectly because we will utilize the index, but we will still have to
do a leaf page scan of the index.

For the cursor:

SELECT reporting_day FROM reporting_days
WHERE reporting_day >= Start_Date
AND Day_Type = ''
ORDER BY reporting_day;

I would expect that start_date is found through the index, and then read
a number of leaf pages in the index.


/Lennart

Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
I think we compare apples and pears here. The two versions below use
pretty different queries, so any difference in performance is *not*
a matter of using a cursor or not.

It seems you use the cursor to just read the first FLOOR(Units_to_Add)
matching rows. But then MySQL already has a syntax feature for that:
a LIMIT clause. And of course MySQL can use LIMIT to optimize execution
of a query (i.e. read rows in specified order and stop reading when the
LIMIT is reached)

http://dev.mysql.com/doc/refman/5.1/...imization.html


A non-cursor version of that query could look like so:

SELECT MAX(inner.day)
FROM ( SELECT reporting_day AS day
FROM reporting_days
WHERE reporting_day >= Start_Date
AND day_type = ''
ORDER BY reporting_day
LIMIT FLOOR(Units_to_Add)
) AS inner;

Due to syntax limitations one had to use PREPARE and EXECUTE to put
the `Units_to_Add` value into that query (aka "dynamic SQL").

In order to execute the inner query fast, an index on
(reporting_day, day_type) is needed. The `reporting_day` column allows
to use an index scan to read in sort order and including the `day_type`
column avoids diving to the row to compare day_type = ''


XL

On 2010-12-28 19:32, Axel Schwenke wrote:
See below

Can you provide a working example inside a function? I keep getting:

ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or
trigger

Example:

delimiter //
CREATE FUNCTION Add_Working_Days3(Units_to_Add Decimal(10, 2),
Start_Date Date)
RETURNS date
BEGIN
declare res date;

set @qry = 'SELECT MAX(inner.day) into res
FROM ( SELECT reporting_day AS day
FROM reporting_days
WHERE reporting_day >= Start_Date
AND day_type = ''''
ORDER BY reporting_day
LIMIT FLOOR(' || Units_to_Add || ')
) as INNER)';

PREPARE stmt from @qry;
EXECUTE stmt;

END//
delimiter ;

I introduced the @qry variable since there seems to be limitations in
what one can do with the query string inside the PREPARE stmt

The reason I did not use LIMIT in my declarative approach was that LIMIT
is not supported other than as a constant, but I'm unable to PREPARE a
query as well.


/Lennart

On 2010-12-28 22:06, Lennart Jonsson wrote:
[...]
not sure how the last ')' ended up in my post, it should be:

set @qry = 'SELECT MAX(inner.day) into res
FROM ( SELECT reporting_day AS day
FROM reporting_days
WHERE reporting_day >= Start_Date
AND day_type = ''''
ORDER BY reporting_day
LIMIT FLOOR(' || Units_to_Add || ')
) as INNER';

Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
No. Dynamic SQL is not allowed in a stored function. But it can be used
in a stored procedure. And of course it had to be written along

$qry = "... LIMIT " || FLOOR(Units_to_Add) || ...


XL