I finally got to the bottom of this. It seems there might be a underlying issue in siwapp or the doctrine layer.
Basically, I have about 10,000 invoices - lately it was taking around 60 seconds to save an invoice (as a result the request would often time out!).
/**
* Returns the next number for the series
*
* @return integer
* @author Enrique
**/
public function getNextNumber($series_id)
{
$found = $this->createQuery()
->where('Draft = ?', 0)
->andWhere('series_id = ?', $series_id)
->execute()
->count();
if ($found > 0)
{
$rs = $this->createQuery()
->select('MAX(number) AS max_number')
->where('Draft = ?', 0)
->andWhere('series_id = ?', $series_id)
->fetchOne();
}
else
{
return Doctrine::getTable('Series')->find($series_id)->getFirstNumber();
}
}
What that first query looks like it's doing is getting a simple count of the rows. The problem is, it's retrieving all the rows FIRST and then doing a count. When you've got 10,000 invoices, it turns out that takes a helluva long time.
I am a complete novice when it comes to doctrine (and php come to think of it), so I am sure there is a more elegant way to solve the issue, but this is what I did:v
/**
* Returns the next number for the series
*
* @return integer
* @author Enrique
**/
public function getNextNumber($series_id)
{
$rsRecordCount = $this->createQuery()
->select('COUNT(number) AS record_count')
->where('Draft = ?', 0)
->andWhere('series_id = ?', $series_id)
->fetchOne();
if (intval($rsRecordCount->getRecordCount()) > 0)
{
$rs = $this->createQuery()
->select('MAX(number) AS max_number')
->where('Draft = ?', 0)
->andWhere('series_id = ?', $series_id)
->fetchOne();
return intval($rs->getMaxNumber()) + 1;
}
else
{
return Doctrine::getTable('Series')->find($series_id)->getFirstNumber();
}
}
What this does is execute a proper sql count like this:
SELECT COUNT(c.number) AS c__0 FROM common c WHERE (Draft = ? AND c.series_id = ? AND (c.type = 'Invoice')) - (0, 1)
This query takes a split second to execute.
Anyway, I hope that helps someone. If you are finding the generation of invoices slow, this is probably why.