0

I have an old database table I used in mySql, that I would like to migrate to my new postgres DB. Has I'm using symfony, I created migration command line, that gets my data from the old DB, format it and treat it, create new entity through the symfony setters of my entity and flush the data.

To avoid to query the entire table which is big, I created a loop that gets every 100 lines of the table, format the data, until the table is entirely scanned. The fact is that it's very slow...

My old DB table have like 2000 lines, but for every line treated, I create one entity which is bind by relation to 9 other new entity created.

When I launch the custom command line to initiate the migration. It takes almost 12 minutes to scan the entire table.

How can I speed up the process and improve my symfony performances ?

Vico-gs
  • 41
  • 2
  • Unless you plan to migrate your mysql database regularly to postgresql (in which case it is not migration but syncing of two databases), I would not spend any time on optimising the code. You will spend more time on optimising the code than you would save by the optimisation. – Shadow Oct 03 '22 at 07:35
  • It is indeed a once in a life time process, but I wanted to optimize it, has I have other tables to migrate and don't want to shut down my website for to long while the process is running. – Vico-gs Oct 03 '22 at 08:33
  • Database migrations are usually not done through an ORM. ORMs ensure that you can manipulate relational tables as if they were objects in the code and are not geared towards mass data manipulations. A bespoke script that pulls all data from a mysql table (you can easily fit 2k rows into memory in one go), manipulates the data in memory (if necessary) and then pushes the data into your target database in one go (again, 2k rows are nothing for a modern computer). For the latter, look 'bulk insert' up. You need to migrate parent tables first, then child tables. – Shadow Oct 03 '22 at 09:10
  • If you want to go ahead with the symphony route, then you have to provide a lot more detail in your question as we have no clue how your database is structured and how your export - import code looks like. – Shadow Oct 03 '22 at 09:11
  • Yes, unfortunately I have to check every data, so I think symfony is one of the solution that I have. In my old DB, my table is a list of users (id, name, email, email_2, phone, phone_2, birthdate, etc ...) In the new DB I split in to a user (id, name) and contact table (phone number, emails), a OneToMany relation between user and contact. A user can have many contacts. When I read a line in my old DB, I check if the user already exists, if not I create a user with the required data, then all contacts, then I bind them, flush and clean the entityManager, And so on for every line. – Vico-gs Oct 03 '22 at 09:42
  • This approach is very time consuming as you do all database operations in the smallest possible chunk. If you want performance, you cannot do this. – Shadow Oct 03 '22 at 10:06
  • See the following answer that shows the speed difference between what you do and the suggested approach: https://stackoverflow.com/a/21739931/5389997 (your approach is the transaction per insert) – Shadow Oct 03 '22 at 10:14
  • Thanks for sharing the link, very useful. What I understand is that using an ORM will automatically be way slower than creating sql queries and using one of the suggested speedup solutions is that right ? As I absolutely need to format the data with conditions, like to check if the entity already exists, concatenate in the new DB the firstname and the lastname column from the old one, etc ... Those specific tasks are only possible through an ORM and an entity manager with symfony, Otherwise it will be pure SQL queries. Are those checks possible with SQL queries ? I'm far from an sql expert. – Vico-gs Oct 03 '22 at 12:43
  • I would be very surprised if you could not do this migration via sql queries. Obviously, it is very difficult to be entirely certain as we do not know what exactly you need to do. – Shadow Oct 03 '22 at 13:39
  • Thank you very much for the feed backs, I'll try to find out if I can indeed do what I want in classic SQL and post it in this thread. – Vico-gs Oct 03 '22 at 14:42
  • Would it work to dump CSV files, then reload them? – Rick James Oct 03 '22 at 15:17
  • You probably need a staging table first in postgresql that holds a copy of the mysql table without any transformation and then proceed from there with set of insert ... select ... queries that transform the data into the target format and can perform integrity checks as well. – Shadow Oct 03 '22 at 15:20

0 Answers0