Saving to associated table taking around 20 seconds

Need Help
1

Hi Friends,

I have below table in my project.

  1. users
  2. menus
  3. menus_users

For each user we can have around 50 menus mapped in menus_users table.

I have written a cron job for testing purpose which create users with menu permissions.
so for 15000 users we will have around 750000 records in menus_users table.

below is the logic to create user

//create new user.
$user = $this->Users->newEntity();
//find all menu items.
$menus = $this->Users->Menus->find(‘all’);
//set the menus array so assoicated data will be mapped in menus_users.
$user->menus = $menus->toArray();

//now save the data
$this->Users->save($user)

but problem is when the data is increasing in menus_users table the saving function of Users->save($user) is becoming slow. after 15000 users it is talking around 20 seconds.

Is there anyone who can suggest me a better way to save it?

Regards,
Ashish

2

I have an application that inserts 1000’s of records into a table, which ran a bit slow too.

First thing I do is write a bunch of tests to ensure things are working with the cake ORM, then I create a new method that uses raw SQL bulk inserts with a prepared statement. Wrapping a bunch of inserts in a single transaction can help too.

My tests mean I’m confident my new raw SQL - very fast - method works as expected. Performance gains mean what took 20 minutes using the cake ORM now runs in less that minute.

This approach will of course mean it can be hard to move the application to a new database vendor in the future - but that wasn’t a concern for me.