INSERT performance

[Alan Stone]

Hi

Firstly, I'm running 4.1.7 on Win2K. 0.5Gb Memory, single 550Mhz PIII processor. Database access is via php/PEAR DB classes. Running apache 2.

I am seeing execution times increasing dramatically during insert operations on three linked tables. with only 1200 records i'm seeing 50 seconds plus to insert 100 records in each table. Is this normal?

The inserts are being done under LOCK TABLE ... for WRITE conditions to prevent other unsequenced inserts.

Code fragment follows...
$sql="INSERT INTO tblworkorder " .
"(prefix, workorderid, customerid, woidseries, returntype, symptomcode, technicianname)".
" VALUES ".
"('ACC',{$Woid},{$cid},{$Woidser},'AR',{$scode},'{$tname}'); \n";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
$sql="SELECT @@IDENTITY FROM tblworkorder;";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here

<<<<<<<<<CODE REMOVED>>>>>>>>

$popcsv = date("Y/m/d",fArrayItem($DateArray, $DateOffset));
$popdate = $popcsv . " " . fRandomTime();
$recdate = date("Y/m/d H:m:s");
$shipdate = $recdate;
$sql="INSERT INTO tblitem ".
"(workorderid,productcode,popdate,warrstatus,receivingdate,shippingdate,receiveditemcode) ".
"VALUES ".
"({$woid_inx},\"{$Accpartno}\", \"{$popdate}\", 1, \"{$recdate}\", \"{$shipdate}\",\"{$AccRIC}\"); \n";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
$sql="SELECT @@IDENTITY FROM tblItem;";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
<<<<<<<<<CODE REMOVED>>>>>>>>
$sql="INSERT INTO tblrepair".
"(itemid,repairsymptomcode,faultcode,keyrepair,partnumber,partreplaced) ".
"VALUES ".
"({$item_inx}, \"{$scode}\", \"{$AccFC}\", 1, \"{$Accpartno}\", 1)";
$rslt = fDbQuery($dbh, $sql); // Error will be thrown in handler, not here
_____________________________________________________________
End of CODE

my.ini settings are:

[client]

port=3306
[mysqld]
port=3306
basedir=E:/mysql/
datadir=E:/mysql/Data/
default-character-set=latin1
default-storage-engine=INNODB
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=8M
thread_cache=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=64M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=212K
#skip-innodb
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8

Thanks

Alan