MySQL Forums
Forum List  »  Quality Assurance

Function slowing down
Posted by: Freddy Hernandez
Date: June 28, 2011 03:39PM

A create a function to calculate the diff in workdays.
When I test the function:
SELECT benchmark(5000, WorkDays('2007-5-19','2007-5-13'))
It took 0.422 sec, but if I test again, I get: 4.968, 8.828, 12.000, 15.360, and every time it take more to complete.
I don't know why It get slowing every time it run.
I need to make a select with 4 calls to this function, for some thousands registers, so I need it to work for a lot of call, no only one.
Code:
CREATE DEFINER=`root`@`localhost` FUNCTION `WorkDaysOrg`(start_date DATETIME, end_date DATETIME) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE diff INT;
DECLARE dwStart INT;
DECLARE dwEnd INT;
IF (start_date > end_date) THEN
BEGIN
DECLARE temp_date DATE;
SET temp_date = start_date, start_date = end_date, end_date = temp_date;
END;
END IF;
SET diff = DATEDIFF(end_date, start_date), dwStart = WEEKDAY(start_date), dwEnd = WEEKDAY(end_date);
RETURN (CASE WHEN dwStart<>5 AND dwStart<>6 AND dwEnd = 5 THEN diff
WHEN dwStart<>5 AND dwStart<>6 AND dwEnd = 6 THEN (diff - 2)
WHEN dwStart = 5 AND dwEnd = 6 THEN (diff - 1)
WHEN dwStart = 5 AND dwEnd = 5 THEN (diff + 1)
WHEN dwStart = 6 AND (dwEnd = 5 OR dwEnd = 6) THEN (diff + 1)
WHEN dwStart = 5 AND dwEnd <> 5 AND dwEnd <> 6 THEN (diff -1)
WHEN dwStart = 6 AND dwEnd <> 5 AND dwEnd <> 6 THEN (diff + 1)
WHEN dwStart<>5 AND dwStart<>6 AND dwEnd <> 5 AND dwEnd <> 6 AND dwStart > dwEnd THEN (diff - 2)
ELSE diff END)
- (FLOOR(diff / 7) * 2)
- (CASE WHEN dwStart = 6 THEN 1 ELSE 0 END)
- (CASE WHEN dwEnd = 5 THEN 1 ELSE 0 END);
END
What can I do for this to at least have a deterministic performance?

Options: ReplyQuote


Subject
Views
Written By
Posted
Function slowing down
1778
June 28, 2011 03:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.