Complex CDbCriteria speed issues

Hey,

public function getCriteria($pid){

 return new CDbCriteria;

      $criteria->alias='a';

      $criteria->select="*,subid as a";

      $criteria->condition="(select count(*) from documents as b where b.revision = a.revision and b.revid > a.revid ) <=0 pid={$pid}";

      $criteria->order="dateModified";

      $criteria->limit = 10;

}

$criteria = Documents::getCriteria($pid);

$documents = new CActiveDataProvider('Documents', array('criteria' => $criteria));

That criteria returns all the records associated with $pid, and is working as expected. It will also only return the latest version of a single record.

My issues lie within speed of that query, its taking 5 seconds to run and load the page, is there a way to speed that up? Or reduce the SQL query todo the same thing?

It selects all the documents ordered by the date they were changed, groups them by a revision stream (revision) and only returns the highest version from that group. max(revid).

Is there a simpler way todo this in MySQL or yii?

With 5 - 10 records in the table, load time is nothing, its perfect. When there is 1009 records in the table, the load and query time is increased tenfold.

Thanks for your time

Install one of the extensions

• dbprofiler

• phpquickprofiler

and take a look at the sql-statements and the consumed time.

Test the performance of the generated sql directly in phpMyAdmin. Maybe it’s an issue of the statement.

For complex requests and better performance I would prefer the CSqlDataProvider (returning the records as array and not createing a model for each fetched row).

Thanks Joblo, I will have a look at that some time today see if I can’t fix the query, it is using pagination, and should only be loading 10 results at a time. Unfortunately I need these results as an active record.

Okay, here are the two queries that take 2.3 seconds each to run.

SELECT COUNT(*) FROM `documents` `a` WHERE (select count(*) from documents as b where b.revision = a.revision and b.revid > a.revid ) <=0 AND status in ('Approved', 'Pending Approval') AND cid='442' AND pid='112'

SELECT *,subid as a FROM `documents` `a` WHERE (select count(*) from documents as b where b.revision = a.revision and b.revid > a.revid ) <=0 AND status in ('Approved', 'Pending Approval') AND cid='442' AND pid='112' ORDER BY dateModified LIMIT 10

If any one can see a way to fix those queries, +10 internets for you

Maybe it’s faster if you move the status/cid/pid to first position:

WHERE 'a.status in (‘Approved’, ‘Pending Approval’) AND a.cid=‘442’ AND a.pid=‘112’

AND (select count(*) from documents …)

… and use a.status, a.cid …

… and check the indexes.

Or something with:

Select … max(revid)

Group by …

Having …

Order by date

But can’t test without data

If I were to give you a sample dump, do you think you would be able to have a squiz for me?

Here is a dump of the data. There are 7 rows in it

What needs to happen is select * from documents, ordered by dateAccepted.

It should show all the rows, minus did in (2068 and 2067) because they have a higher revid (3)

I think I worked it out, adding more rows to the table to test it now, but what I have is this.

select * from documents d1 WHERE revid=(select max(revid) from documents d2 where d1.revision=d2.revision);

Thanks for your help Joblo, I did not realise I could have sub-queries in a where clause.

Unfortunately I seem to be mistaken. 3000 records in the table, and it is taking 90 seconds with either query.