System taking longer and longer to create invoices

[Lutin]

Has anyone experienced a gradual slow down with siwapp over time. At the moment it's taking about 60 seconds to create an invoice (by which I mean when you hit the save invoice button). This was working fine when we first deployed siwapp, but over the last few months the creation of invoices has been taking longer and longer. Admittedly, we do have a few thousand invoices in there. I suspect the issue might lie in the database query side of things. 

Any seen anything like this before. Any tips?

I should say that apart from the system being slow to create invoices, it runs fine otherwise.

[Lutin]

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!). 

The problem is in the InvoiceTable.class.php file:

  /**

   * 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.