Speed Up Multiple Saves

Afternoon.

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

Regards

Liam

Hi Liam,

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.

Cheers

Hi Luke.

Thanks for the reply and apologies for the time it has taken to reply.

I finally got round to re-write the code, and yes i found two major speed increases.

  1. I got all data from the database and stored in a array instead of constantly doing a model findbypk.

  2. i used transaction to commit the changes to the database.

It is far faster to use DAO instead of Active record if dealing with very large data sets

Once again thanks

Regards

Liam