97.394570112228 - Query OK, 1 row affected (43.05 sec) - the overhead of parsing and network communication
1 mysql> create table w0904procedure (wa char, wb char, wd char, wi char); 2 Query OK, 0 rows affected (0.00 sec) 3 4 mysql> INSERT INTO w0904procedure VALUES(NULL, 0, 'www0904', 'w0904d'); 5 ERROR 1406 (22001): Data too long for column 'wd' at row 1 6 7 mysql> desc w0904procedure; 8 +-------+---------+------+-----+---------+-------+ 9 | Field | Type | Null | Key | Default | Extra | 10 +-------+---------+------+-----+---------+-------+ 11 | wa | char(1) | YES | | NULL | | 12 | wb | char(1) | YES | | NULL | | 13 | wd | char(1) | YES | | NULL | | 14 | wi | char(1) | YES | | NULL | | 15 +-------+---------+------+-----+---------+-------+ 16 4 rows in set (0.00 sec) 17 18 mysql> DROP PROCEDURE IF EXISTS insert_many_rows_w0904; 19 Query OK, 0 rows affected (0.00 sec) 20 21 mysql> delimiter // 22 mysql> CREATE PROCEDURE insert_many_rows_w0904 (IN loops INT) 23 -> BEGIN 24 -> DECLARE v1 INT; 25 -> SET v1=loops; 26 -> WHILE v1 > 0 DO 27 -> INSERT INTO w0904procedure VALUES(NULL, 0, 'w', 'w'); 28 -> SET v1 = v1 - 1; 29 -> END WHILE; 30 -> END; 31 -> // 32 Query OK, 0 rows affected (0.00 sec) 33 34 mysql> delimiter ; 35 mysql> 36 mysql> SET @loops=987987; 37 Query OK, 0 rows affected (0.00 sec) 38 39 mysql> CALL insert_many_rows_w0904(@loops); 40 Query OK, 1 row affected (43.05 sec) 41 42 43 44 mysql> SELECT * FROM w0904procedure LIMIT 20; 45 +------+------+------+------+ 46 | wa | wb | wd | wi | 47 +------+------+------+------+ 48 | NULL | 0 | w | w | 49 | NULL | 0 | w | w | 50 | NULL | 0 | w | w | 51 | NULL | 0 | w | w | 52 | NULL | 0 | w | w | 53 | NULL | 0 | w | w | 54 | NULL | 0 | w | w | 55 | NULL | 0 | w | w | 56 | NULL | 0 | w | w | 57 | NULL | 0 | w | w | 58 | NULL | 0 | w | w | 59 | NULL | 0 | w | w | 60 | NULL | 0 | w | w | 61 | NULL | 0 | w | w | 62 | NULL | 0 | w | w | 63 | NULL | 0 | w | w | 64 | NULL | 0 | w | w | 65 | NULL | 0 | w | w | 66 | NULL | 0 | w | w | 67 | NULL | 0 | w | w | 68 +------+------+------+------+ 69 20 rows in set (0.00 sec) 70 71 72 mysql> SELECT COUNT(*) FROM w0904procedure; 73 +----------+ 74 | COUNT(*) | 75 +----------+ 76 | 987988 | 77 +----------+ 78 1 row in set (0.00 sec)
High Performance MySQL, Third Edition
by Baron Schwartz, Peter Zaitsev, and Vadim Tkachenko
1 <?php 2 3 $db = new mysqli('localhost', 'root', '', 'w'); 4 if($db->connect_error){ 5 die('Connect Error ( '.$db->connect_errno.' )'.$db->connect_error); 6 } 7 echo 'Connect OK.<br>'; 8 $start = microtime(TRUE); 9 if($res=$db->query('SELECT COUNT(*) FROM w0904procedure')){ 10 while($row=$res->fetch_all()){ 11 var_dump($row); 12 } 13 } 14 $end0 = microtime(TRUE); 15 if($res=$db->query('SELECT * FROM w0904procedure LIMIT 2')){ 16 while($row=$res->fetch_all()){ 17 var_dump($row); 18 } 19 } 20 $end1 = microtime(TRUE); 21 $w = 987987; 22 do{ 23 $db->query('INSERT INTO w0904procedure VALUES(NULL, 1, "w", "w")'); 24 $w--; 25 } 26 while($w>0); 27 $end2 = microtime(TRUE); 28 echo $end0 - $start, '<br>', $end1 - $end0, '<br>', $end2 - $end1;
1 Connect OK. 2 3 D:\wamp64\www\wdb.php:11: 4 array (size=1) 5 0 => 6 array (size=1) 7 0 => string '987988' (length=6) 8 9 D:\wamp64\www\wdb.php:17: 10 array (size=2) 11 0 => 12 array (size=4) 13 0 => null 14 1 => string '0' (length=1) 15 2 => string 'w' (length=1) 16 3 => string 'w' (length=1) 17 1 => 18 array (size=4) 19 0 => null 20 1 => string '0' (length=1) 21 2 => string 'w' (length=1) 22 3 => string 'w' (length=1) 23 24 0 25 0.003000020980835 26 97.394570112228
1 mysql> SELECT COUNT(*) FROM w0904procedure; 2 +----------+ 3 | COUNT(*) | 4 +----------+ 5 | 987988 | 6 +----------+ 7 1 row in set (0.00 sec) 8 9 mysql> SELECT COUNT(*) FROM w0904procedure; 10 +----------+ 11 | COUNT(*) | 12 +----------+ 13 | 1975975 | 14 +----------+ 15 1 row in set (0.00 sec) 16 17 mysql>
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 communication 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.
The stored procedure is much faster, mostly because it avoids the overhead of network communication, parsing, optimizing, and so on.
