The situation:
1 Progess DB server running on Unix 1 MySQL DB server running on Ubuntu
The Progress database has to be synced every night with the MySQL DB. We figured this could be done via a PHP sync script. The script connects to Progress via an ODBC driver, fetches the table and the php scripts runs through every row and formats the insert for the MySQL DB.
While this works its VERY slow, the total synchronisation takes abouts 6 hours for a database which estimates around 1GB. After some extensive debugging we found out that the result is retrieved fast from the ODBC but inserts are very slow on the MySQL server.
So the next question was how to optimize this;
Tried to write everything to a file, and then import to MySQL with the LOAD DATA Tried to disable the keys on the tables Tried an insert with multiple values
All in all it didn't matter that much, we did win a couple minutes but that aint the groundbreaking result I'm looking for. Is it really the MySQL server limit that cant handle insert faster?
The redundant work in the script is also clear to me, but we have to build the insert queries and loop through every row.
In short the procedure:
- connect to odbc
- connect to mysql
- fetch via odbc (whole table) = fast
- loop every row from odbc = fast
- create insert for mysql = fast if send directly, via file its still slow
- send query to mysql = slow
How can we optimize this, I searched for a different solution also, which resembles an API in C. That isnt really my workfield do.