I was wondering how to speed up one of my scripts.
This script runs through around 300k lines of a database.
If performs a few functions on these lines then updates the database.
Now i have added some comparison tests so it wont save the model if the data is the same, however when the data isn’t the same it then saves and updates the database.
This in turn takes a great deal of time.
If i was using php and mysql native i would create a insert on duplicate statement with multiple values and have it insert all at once.
How can you do this in Yii. Or is there a better way that i can speed up this process.
Currently this process takes around 30mins.
I am looking into where the process is slowing down, but i do believe it to be in the insert/updates performed by the save command
The problem here is that Yii database objects are built on the back of PDO, depending on the database you can’t always perform multiple statements at once.
PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)
You might be able to get it working and use query builder, transform the data in the models to sql statements and do it that way. Either way active record really sticks to it’s one per row approach unless you are doing the work in the DB itself.