I'm creating a small site for importing data from Oracle to SQL Server database, but it taking too long and using too much memory. I've tried the query by using SQL developer and SQL Server Management and both run fast and no error.
I'm using below code to retrieve the data from Oracle database :
$data = $this->oracle->query($query);
$oracle_data = $this->model_oracle->get_headers($a)->result_array();
And using below code to insert it into SQL Server :
$sqlsrv->trans_start();
$sqlsrv->truncate('tr_header');
$sqlsrv->insert_batch('tr_header',$header_oracle);
$sqlsrv->trans_complete();
There's no calculation or data processing, only get and insert. The data is about 140.000 rows, and it took more than 1 hour to complete and used more than 200MB memory (from php.ini). I believe there's no issue on my query, because if i have to compare it with how it ran in other program:
Select on SQL Developer : 1-2 mins
Insert on SQL Server : <1 min
Using SSIS : ~10 mins
So, is there any way to improve the performance?
