Long Execution Time

[jazzman1]

Guys, why the following mysql stored procedure taking much more time comparing with firebird one?

 

1. MySQL stored procedure

CREATE DEFINER=`lxc`@`::1` PROCEDURE `insertData`(IN it INT)
BEGIN
      declare i int Default 0;
      declare str char(4);
      declare p_hash int;
      myloop: loop
      set p_hash = round(rand() * 999);
      set str='book';
      insert into test.users (name,password) VALUES (concat(str,'_',i+1),PASSWORD(p_hash));
      set i = i+1;
      if (i = it) then
        leave myloop;
	end if;
    end loop myloop;
  end

Object info:

Table: users
Columns:
id	int(10) UN PK AI
name	varchar(45) 
password	char(41) 

Time:

mysql> call insertData(10000);
Query OK, 1 row affected (9 min 38.22 sec)

2. Firebird Stored Procedure

SET TERM ^ ;
CREATE PROCEDURE INSERDATA (
    IT integer ) --iteration number
AS
DECLARE VARIABLE i integer; 
BEGIN
i = 0;
while ( i < it ) do
begin
INSERT INTO PRODUCT( CODE,NAME,PASS) VALUES(:i, 'jazz_' || :i, fb_hash(round(rand() * 999)));
i = i + 1;
end
END^
SET TERM ; ^
COMMIT;

Object Info:

Table: product
columns
CODE integer,
NAME varchar(45),
PASS CHAR(41)

Time:  ( for 10 000 records it takes less than 1 sec for execution)

Executing statement...
Statement executed (elapsed time: 0.063s).
31464 fetches, 11084 marks, 0 reads, 0 writes.
10000 inserts, 0 updates, 0 deletes, 5 index, 0 seq.
Delta memory: 24264 bytes.
PRODUCT: 10000 inserts. 
0 rows affected directly.
Total execution time: 0.151s
Script execution finished.

Do you see something wrong in mysql stored procedure code?

 

jazz.

[mac_gyver]

the two sets of code are not comparable. the mysql code is doing a lot more work, with several more statements being evaluated in each iteration of the loop and of the database engine managing the auto-increment index field.

 

to make a fair comparison, you need to use the same while loop construct in both, remove the static str variable, remove the p_hash assignment, remove the i+1 operation (or add these things to the FB code), and add an auto-increment id field to the FB product table.

 

you would also need a comparable number of starting rows in each table.

 

edit: btw - you should NEVER use the msyql PASSWORD() function in your application code.

[jazzman1]

Hey mac, sorry my delay and thanks for your reply So, I am aware, that previous data structure and stored procedure script were not exactly the same, but the execution time to the mysql server is too long, isn't it? I think I'm missing something by compiling the server, howerver I will check the configure log file for more details tomorrow.

Here's the second test inserting only 2000 records.

1.MySQL Database Server

 

a) TMP_ONE: DDL

CREATE TABLE `tmp_one` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `pass` char(41) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

b) Routine DDL

CREATE DEFINER=`lxc`@`::1` PROCEDURE `insertData`(IN it INT)
BEGIN
      declare i int default 0;
      myloop: while ( i < it) do
      insert into test.tmp_one (id, name,pass) VALUES (i+1,concat('jazz_',i+1),PASSWORD(round(rand() * 999)));
      set i = i+1;
    end while myloop;
  end

c) mysql> call insertData(2000);
Query OK, 1 row affected (1 min 55.11 sec)

 

2. FB Database Server

a) TMP_ONE: DDL

CREATE TABLE TMP_ONE
(
  CODE integer NOT NULL,
  NAME varchar(45),
  PASS char(41),
  CONSTRAINT CODE PRIMARY KEY (CODE)
);

b) Routine DDL

SET TERM ^ ;
CREATE PROCEDURE INSERTDATA (
    IT integer )
AS
DECLARE VARIABLE i integer; 
BEGIN
i = 0;
while ( i < it ) do
begin
INSERT INTO TMP_ONE( CODE,NAME,PASS) VALUES(:i+1, 'jazz_' || :i, fb_hash(round(rand() * 999)));
i = i + 1;
end
END^
SET TERM ; ^

c) EXECUTE PROCEDURE INSERTDATA (2000);

 

 

Executing statement...
Statement executed (elapsed time: 0.025s).
12282 fetches, 4214 marks, 0 reads, 6 writes.
2000 inserts, 0 updates, 0 deletes, 0 index, 0 seq.
Delta memory: 8776 bytes.
TMP_ONE: 2000 inserts.
0 rows affected directly.
Total execution time: 0.111s
Script execution finished

 

Note: The two DB servers are deployed onto two different LXC ( linux containers ) but they share the same domain and host machine. The  memory of both containers is the same, 512 MB per each.

 

PS: No, I'm not using the internal mysql password function in production code!

[phpPeter]

The runtime on mySQL seems to be very long. Inserting just 10k rows should not take so long.

I have no experience with SP on mySQL in particular. However, it seems to be not very performant.

 

I assume, there are no triggers on the table.

 

Maybe you can simplify the script (just insert 10k constant rows into the db). I can't imagine, but maybe some of the functions or statements are very imperformant.

[jazzman1]

Using a standart php pdo driver taking much xecution time as well it's not a SP problem at all. There is no such a problem with performance executing the select or delete statement. 

<?php

$username = 'lxc';

$password = 'password';

$dbh = new PDO('mysql:dbname=test;host=::1;charset=utf8', $username, $password);
  
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql= "insert into test.tmp_one (id, name,pass) values(:code,:name,:pass)";

$time_start = microtime(TRUE);

$stmt = $dbh->prepare($sql);

$stmt->bindParam(':code', $code, PDO::PARAM_INT, 1);
$stmt->bindParam(':name', $name, PDO::PARAM_STR, 4);
$stmt->bindParam(':pass', $pass, PDO::PARAM_STR, 41);

$i = 1;

do {
$code = $i;
$name = 'jazz_'.$i;
$pass = sha1('password');
$stmt->execute();
$i = $i + 1;

} while ($i < 1000);

$stmt = null;

$time_end = microtime(true);

echo substr(($time_end - $time_start),0,5). " seconds"; exit;

Execution time: 57.93 seconds

<?php
ini_set('display_startup_errors',1);

ini_set('display_errors',1);

error_reporting(-1);

$dbh = new PDO("firebird:dbname=127.0.0.1:/var/firebirddata/new.fdb;charset=utf8", "SYSDBA", "masterdba");

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql= "insert into tmp_one(CODE,NAME,PASS) values(:code,:name,:pass)";

$time_start = microtime(TRUE);

$stmt = $dbh->prepare($sql);

$stmt->bindParam(':code', $code, PDO::PARAM_INT, 1);
$stmt->bindParam(':name', $name, PDO::PARAM_STR, 4);
$stmt->bindParam(':pass', $pass, PDO::PARAM_STR, 41);

$i = 1;

do {
$code = $i;
$name = 'jazz_'.$i;
$pass = sha1('password');
$stmt->execute();
$i = $i + 1;

} while ($i < 1000);

$stmt = null;

$time_end = microtime(true);

echo substr(($time_end - $time_start),0,5). " seconds"; exit;

Execution time -> 0.603 seconds

 

Almost 60 times less

[mac_gyver]

i get ~60 execution time for the mysql pdo code as well, for engine type InnoDB. for engine type myisam 0.25 seconds

[kicken]

Only takes about 2.22 seconds for me using the PDO code and an InnoDB table. Using a MyISAM table takes about 0.25 seconds with the code above.

 

Alterting the code above to add beginTransaction/commit calls drops the time on an InnoDB table to about 0.23 seconds.

$dbh->beginTransaction();
do {
...
} while ();
$dbh->commit();

You could try wrapping your loop in the stored procedure in a START TRANSACTION...COMMIT as well and see if it helps.

[jazzman1]

Thanks guys. @kick, start (begin) transaction ......commit reduced the time of execution from 57.12 sec to 0.231 using the PDO code am InnoDB engine