0

i got 2 tables (users and technologies) and i need to retrieve data from them both separately inside a 3rd Controller.

i can do that using loadmodel and find() then set datas to view via $this->set() but by doing that the website becomes very slow >70s and when i try retrieving only one it works fast.

So basically i need to optimise my query and speed up my DB

here's my code.

$users = $this->loadModel('Users');


    $datas=$users->find('all')->select(['id','phone', 'first_name','last_name','birthday','email','website', 'address', 'resume', 'cv_url'])->where(['id' => 1])->bufferResults(false)->ToArray();


    $technologies = $this->loadModel('Technologies');
    $technologies=$technologies->find('all')->select(['id', 'name', 'slug', 'image', 'exp', 'exp_perc'])->bufferResults(false)->ToArray();

    $this->set(compact('datas','technologies'));

Some DebugKit images enter image description here enter image description here

as you can see i only have 2 queries but in debug it shows 10 and TABLE_SCHEMA took 35322ms and its called 2 time (35322*2)

Any suggestion is welcome.

Ayoub Bousetta
  • 97
  • 5
  • 1
    I would suggest that you add more info to your question, as it stands you probably won't get much help, as it's a non-reproducable issue. Like, what DBMS are you using? What do the resulting queries look like? How many records are being retrieved? Where exactly is the time being spent (use a profiler to figure that out)? etc... – ndm Jun 27 '16 at 14:11

1 Answers1

0

Instead of loading the model and creating a select query(which makes your query time increase), you should use TableRegistry.

For example, you should use $usersTable = TableRegistry::get('Users'); instead of $users = $this->loadModel('Users'); and after that you can query your data with $query = $usersTable->find('all')->..... and iterate the rows of the query with foreach($query as $row).

It should be faster considering that it doesn't load your whole 'Users' model and only uses the UsersTable which you can easily operate with cakePHP queries.

Here is some detailed info: http://book.cakephp.org/3.0/en/orm/table-objects.html#getting-instances-of-a-table-class

I hope it helps! ;)

Kopezaur
  • 58
  • 10
  • I already tried that, the problem is that am working on a server directly. and with the debug mode on it takes a lot of time to execute the query. i have tried it on localhost and it works fine even when the debug mode is set to true. – Ayoub Bousetta Jul 09 '16 at 18:16