Most performant way to determine if an invoice is a credit note?

On the Spanish VAT Book report we need to determine if an invoice is a credit note or not.

For now, we compute the type of sequence used for each invoice to be able to determine if it’s a credit note or not.

We started to find performance regression when running this report, when the number of invoice lines is large (we have 300.000 lines and 2500 invoices on a single Fiscalyear).

Some sample timmings some timmings:

  • When the credit_note computation is reading all the lines the report it takes more than 5 minutes.
  • When the credit does not read all the lines (see code above), the report take just 5 seconds.

Here is the code I used to avoid reading the lines.

    def es_vat_book_type(self):                                                
        # Avoid reading the lines                          
        if self.total_amount < 0:                                              
            return 'R0'                                                        
        if not self.party_tax_identifier:                                      
            return 'F2'                                                        
        return 'F1'      

I’m not sure why the credit not computation needs to read all the lines.
So I’m wondering how we can improve such reports performance.

Because we consider a credit note, an invoice with all lines having negative amount, not just the total.

I think you need to add cache on posting like for the amount. Indeed I think it is probably better to store the book type in order to have a stable reporting (even if the behavior of _sequence_field change in the future).

We have the problem that we have two diferent types (one for vat book and other for SII) that both depend on the credit_not but have a different values.

Indeed, I tought about the possibility of adding a cache for credit_note and I’m wondering if it make sense to have this cache on standard invoice module or just in the spanish localization.

Also with the cache we will need to set a value for all the existing records, so probably it will require to have a migration query to store the existing values.

So there will be two fields if the administration can not have a common nomination.

For me it makes sense nowhere. This is not a business data.