Multiple inserts using mysql were very slow so I read
http://ee.php.net/manual/en/function.mysql-query.php for the function below.
It works well, reducing insert/query times dramatically.
However, I don't understand the difference below between <1 second and 5
seconds taken for the code below. The first set of inserts sends the
queries with no space between them and the second follows the function by
putting ;;\n separator to distinguish the queries - which takes longer.
Both seem to work better than not doing the function! I am not sure the
first one should work without a separator, or what the problems might be
when mysql parses the query and/or how to avoid these. Obviously I would
prefer to stay with the substantially quicker method.
***the function***************
function multiple_query($q,$link)
{
$tok = strtok($q, ";;\n");
while ($tok)
{
$results=mysql_query("$tok",$link);
$tok = strtok(";;\n");
}
return $results;
}
***Using no space between the queries:**************
mktime() on previous step: 1124306204
inserted multiple records SSL DELETE FROM Cal WHERE flag='D'INSERT INTO Cal
VALUES (NULL, '2005/8/1', '1', 'N', '1124306204')INSERT INTO Cal VALUES
(NULL, '2005/8/2', '1', 'N', '1124306204')INSERT INTO Cal VALUES (NULL,
'2005/8/3', '1', 'N', '1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/4',
'1', 'N', '1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/5', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/6', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/7', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/8', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/9', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/10', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/11', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/12', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/13', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/14', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/15', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/16', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/17', '1', 'N',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/18', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/19', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/20', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/21', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/22', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/23', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/24', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/25', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/26', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/27', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/28', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/29', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/30', '1', 'Y',
'1124306204')INSERT INTO Cal VALUES (NULL, '2005/8/31', '1', 'Y',
'1124306204') 1124306204
mktime() on this step: 1124306204
<1 seconds
***Using separator between the queries:*****************
mktime() on previous step: 1124306437
inserted multiple records SSL DELETE FROM Cal WHERE flag='D'INSERT INTO Cal
VALUES (NULL, '2005/8/1', '1', 'N', '1124306437');; INSERT INTO Cal VALUES
(NULL, '2005/8/2', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/3', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/4', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/5', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/6', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/7', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/8', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/9', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/10', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/11', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/12', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/13', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/14', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/15', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/16', '1', 'Y', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/17', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/18', '1', 'Y', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/19', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/20', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/21', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/22', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/23', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/24', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/25', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/26', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/27', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/28', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/29', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/30', '1', 'N', '1124306437');; INSERT INTO Cal VALUES (NULL,
'2005/8/31', '1', 'N', '1124306437');; 1124306442
mktime() on this step: 1124306442
5 seconds
*************************************************
Incidentally, the script is on a secure server which access a remote host.
John
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
