xTuple.com xTupleU Blog & News Customer Support

saveTax on 5.1 goes slow most of the time

We recently upgraded to 5.1 and have noticed that when we enter a voucher, this function will hang for 2~6 minutes. saveTax()

We do not use the avilara tax system And I double checked the metric so I doubt it has anything to do with that.

but This is one specific call that was ~5 minutes to complete

SELECT saveTax('VCH', 55181, '{ "date": "2020-09-03", "lines": [ { "tax": [], "line": "004", "taxable": 25.00, "taxtypeid": -1 }, { "tax": [], "line": "003", "taxable": 11.50, "taxtypeid": -1 }, { "tax": [], "line": "001", "taxable": 27.00, "taxtypeid": -1 }, { "tax": [], "line": "002", "taxable": 287.00, "taxtypeid": -1 }], "total": 0, "currid": 2, "freight": { "tax": [], "taxable": 0.0000, "taxtypeid": 1 }, "currrate": 1.00000000000000000000, "discount": 0.0}') AS tax;

Is this a simple fix of a configuration or a missing index? or is there something I should be very worried about

I have narrowed it down to the following Statement. Which indicates a missing index.

SELECT dochead_cust_id, dochead_number, dochead_ordernumber, dochead_origtype, dochead_origid, dochead_orignumber, dochead_origdate INTO _dochead
FROM taxhead
JOIN dochead ON taxhead_doc_type = dochead_type
AND taxhead_doc_id = dochead_id
WHERE taxhead_id = _taxheadid;

For anyone that is struggling with this problem, the short solution is.

addDochead2.sql (23.2 KB)
modifySaveTax.sql (8.2 KB)

execute those in order and problem solved.

The long answer is that the dochead view has a massively inefficient join in our database. I am not certain if it is the same for anyone on 5.1 but I do know that this statement

FROM vohead LEFT JOIN pohead ON vohead.vohead_pohead_id = pohead.pohead_id LEFT JOIN site() site(warehous_id, warehous_code, warehous_descrip, warehous_fob, warehous_active, warehous_counttag_prefix, warehous_counttag_number, warehous_bol_prefix, warehous_bol_number, warehous_shipping, warehous_useslips, warehous_usezones, warehous_aislesize, warehous_aislealpha, warehous_racksize, warehous_rackalpha, warehous_binsize, warehous_binalpha, warehous_locationsize, warehous_locationalpha, warehous_enforcearbl, warehous_default_accnt_id, warehous_shipping_commission, warehous_cntct_id, warehous_addr_id, warehous_transit, warehous_shipform_id, warehous_shipvia_id, warehous_shipcomments, warehous_costcat_id, warehous_sitetype_id, warehous_taxzone_id, warehous_sequence, warehous_picklist_shipform_id, warehous_created, warehous_lastupdated) ON COALESCE(vohead.vohead_pohead_id, '-1'::integer) = '-1'::integer AND (( SELECT vodist.vodist_warehous_id FROM vodist WHERE vodist.vodist_vohead_id = vohead.vohead_id ORDER BY vodist.vodist_id LIMIT 1)) = site.warehous_id LEFT JOIN addr ON site.warehous_addr_id = addr.addr_id

Takes too long to complete for something that is a daily operation.

So to fix it I created a view almost exactly like dochead but replaced the above statement with

FROM vohead LEFT JOIN pohead ON vohead.vohead_pohead_id = pohead.pohead_id

and then I modified the savetax function to use dochead2. I did this specifically to avoid other side effects that I don’t know about. it only changes the view that the save tax function uses. it does not modify the original view

Thank you for the detailed analysis and possible solution. We are always looking for performance enhancements for our product and any assistance is appreciated. We will look into your solution and consider how best to incorporate it.