MySQL performance comparison between Stored Procedure and Prepared Statements

Question

Here is MySQL prepared statement

SELECT 
    ag.`attendance_type`,
    ag.`description`,
    COUNT(a.`attendance`) attendance_count 
FROM
    `ems_attendance` a 
    RIGHT JOIN `ems_att_group` ag 
        ON ag.`id` = a.`attendance` 
        AND a.`added_date` BETWEEN '2011-06-01' 
        AND '2011-06-17' 
        AND a.`users_id` = '9' 
GROUP BY a.`attendance` 
ORDER BY ag.`id`;

and equivalent Store Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `users_attendance_report` $$

CREATE PROCEDURE `users_attendance_report` (
    IN users_id INT,
    IN start_date DATE,
    IN end_date DATE
) 
BEGIN
    SELECT 
        ag.`attendance_type`,
        ag.`description`,
        COUNT(a.`attendance`) attendance_count 
    FROM
        `ems_attendance` a 
        RIGHT JOIN `ems_att_group` ag 
            ON ag.`id` = a.`attendance` 
            AND a.`added_date` BETWEEN start_date 
            AND end_date 
            AND a.`users_id` = users_id 
    GROUP BY a.`attendance` 
    ORDER BY ag.`id` ;
END $$

DELIMITER;

After I run the query both outputs the same results.

Array
(
    [0] => stdClass Object
        (
            [attendance_type] => present
            [description] => Present
            [attendance_count] => 10
        )

    [1] => stdClass Object
        (
            [attendance_type] => absent
            [description] => Absent
            [attendance_count] => 2
        )

    [2] => stdClass Object
        (
            [attendance_type] => other
            [description] => Other
            [attendance_count] => 0
        )

    [3] => stdClass Object
        (
            [attendance_type] => dayoff
            [description] => Day Off
            [attendance_count] => 2
        )

)

I closely look into the execution time, both are same. When and where one is better and faster than another?

score 4 · Accepted Answer · edited May 23 '17 at 11:45

4

"Faster" and "better" are not necessarily aligned. Please see this recent similar SO question, and consider these attributes of a solution:

maintainable (readable, skills requirements - who can work on this code)
testable
releasable
flexible
portable

Generally speaking, stored procedures are faster, but fail on every other metric.

edited May 23 '17 at 11:45

Community

1
1

answered Jun 17 '11 at 06:32

Bohemian

412,405
93
575
722

5

Yes, but also consider integrity and security. Stored procedures win on those metrics. A stored procedure can have access rights differing from the table. Which means you can for example deny access to a table, except via the well-defined way the stored procedure implements. Also, you can rule out most cases of accidential or sloppy invalid/inconsistent data. Lastly, it is much harder to successfully exploit a script that calls a stored proc rather than sending raw SQL to the DB server, too (not saying that it's impossible, but it's definitively much harder). – Damon Jun 17 '11 at 07:30
1

Sure - everything you say is true, but PL/SQL is too hard to maintain and debug etc. Most databases defer control of user access to the OS/app; rarely do they do it themselves (at an app-user level). Also, PL/SQL is a rarer skill than app programming, and frankly less disciplined IMHO. In the medium/long run, you'll be thankful you didn't go there, especially for any "business" logic. – Bohemian Jun 17 '11 at 12:45

score 2 · Answer 2 · answered Jun 17 '11 at 07:53

2

I think in your case it doesn't matter if you run the query standalone or as part of stored procedure. A procedure is nice in situations where you've got a query batch to perform. Therefore, for your query it's best to run standalone.

answered Jun 17 '11 at 07:53

Abhay

6,545
2
22
17

MySQL performance comparison between Stored Procedure and Prepared Statements

2 Answers2