Databases Reference
In-Depth Information
We usually prefer to keep stored routines small and simple. We like to perform complex
logic outside the database in a procedural language, which is more expressive and
versatile. It can also give you access to more computational resources and potentially
to different forms of caching.
However, stored procedures can be much faster for certain types of operations—
especially when a single stored procedure call with a loop inside it can replace many
small queries. If a query is small enough, the overhead of parsing and network com-
munication becomes a significant fraction of the overall work required to execute it.
To illustrate this, we created a simple stored procedure that inserts a specified number
of rows into a table. Here's the procedure's code:
1 DROP PROCEDURE IF EXISTS insert_many_rows;
2
3 delimiter //
4
5 CREATE PROCEDURE insert_many_rows (IN loops INT)
6 BEGIN
7 DECLARE v1 INT;
8 SET v1=loops;
9 WHILE v1 > 0 DO
10 INSERT INTO test_table values(NULL,0,
11 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt',
12 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt');
13 SET v1 = v1 - 1;
14 END WHILE;
15 END;
16 //
17
18 delimiter ;
We then benchmarked how quickly this stored procedure could insert a million rows
into a table, as compared to inserting one row at a time via a client application. The
table structure and hardware we used doesn't really matter—what is important is the
relative speed of the different approaches. Just for fun, we also measured how long
the same queries took to execute when we connected through a MySQL Proxy. To keep
things simple, we ran the entire benchmark on a single server, including the client
application and the MySQL Proxy instance.
Table 7-1
shows the results.
Table 7-1. Total time to insert one million rows one at a time
Method
Total time
Stored procedure
101 sec
Client application
279 sec
Client application with MySQL Proxy
307 sec
The stored procedure is much faster, mostly because it avoids the overhead of network
communication, parsing, optimizing, and so on.
We show a typical stored procedure for maintenance jobs later in this chapter.
