Good day!
I am having a problem in slow execution of my PHP script. I am not sure if this is valid question. Please bear with me.
My task is to create a web app to convert an existing SDF file(SQL Server CE) to a DB(SQlite file). I am using PHP.
The SQlite database will be used in a mobile application.
Here is the steps of my conversion:
- The user can select single or multiple SDF file.
- I will connnect first to that SDF File. I found a very helpful script. Read SQL Compact Edition (* .sdf) with PHP
- I will create the SQLite database file and create the needed tables.
- I would select the data from the SDF, and then insert the data to their respective tables in the SQLite database.
I would have to repeat this process if the user selected multiple files.
Here is my code for your reference:
/**
* ARBalances
*/
$file_db->exec("CREATE TABLE IF NOT EXISTS ARBalances (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
CustomerCode NVARCHAR(10),
BillingDate TEXT,
Balance DECIMAL(15,2),
SalesId NVARCHAR(30),
Amount DECIMAL(18,2),
Payment DECIMAL(18,2))");
/**
* Insert Data to db file. Select ARBalances from SDF file
*/
$sdf->execute("SELECT * FROM ARBalances");
$this->data_array = [
'customer_code' => NULL,
'billing_date' => NULL,
'balance' => NULL,
'sales_id' => NULL,
'amount' => NULL,
'payment' => NULL,
];
$ar_balances = [];
while (!$sdf->eof()) {
$ar_balances[$sdf->fieldvalue('SalesId')] = $this->data_array;
$ar_balances[$sdf->fieldvalue('SalesId')]['customer_code'] = $sdf->fieldvalue('CustomerCode');
$date = str_replace('/', '-', $sdf->fieldvalue('BillingDate'));
$date = new DateTime($date);
$billing_date = strtotime($date->format('Y-m-d H:i:s'));
$billing_date = (int)$billing_date * 1000;
$ar_balances[$sdf->fieldvalue('SalesId')]['billing_date'] = $billing_date;
$ar_balances[$sdf->fieldvalue('SalesId')]['balance'] = (float)$sdf->fieldvalue('Balance');
$ar_balances[$sdf->fieldvalue('SalesId')]['sales_id'] = (string)$sdf->fieldvalue('SalesId');
$ar_balances[$sdf->fieldvalue('SalesId')]['amount'] = (float)$sdf->fieldvalue('Amount');
$ar_balances[$sdf->fieldvalue('SalesId')]['payment'] = (float)$sdf->fieldvalue('Payment');
$sdf->movenext();
}
$insert = "INSERT INTO ARBalances(CustomerCode, BillingDate, Balance, SalesId, Amount, Payment)
VALUES (:customer_code, :billing_date, :balance, :sales_id, :amount, :payment)";
$stmt = $file_db->prepare($insert);
$stmt->bindParam(':customer_code', $customer_code);
$stmt->bindParam(':billing_date', $billing_date);
$stmt->bindParam(':balance', $balance);
$stmt->bindParam(':sales_id', $sales_id);
$stmt->bindParam(':amount', $amount);
$stmt->bindParam(':payment', $payment);
foreach ($ar_balances as $value) {
$customer_code = $value['customer_code'];
$billing_date = $value['billing_date'];
$balance = $value['balance'];
$sales_id = $value['sales_id'];
$amount = $value['amount'];
$payment = $value['payment'];
$stmt->execute();
}That code is for the ARBalances table only.I have 38 tables in the database. Some tables have 200+ records. My execution time takes 5 mins ust for a single conversion! I don't know if this is normal, but I think it takes too long.
I think the while loop is what causes this problem. To be honest I am not really familiar with computer science concepts or looping optimization.
Hope you can help me how to optimize my code. Any suggestion would be much appreciated. Hope I explained myself clearly. Thanks a lot.
