The need for speed

[jim.wirt]

I've been working on a pilot project to upgrade a high usage 4.11 xtuple site to 5.00. The primary motivation is to enable integration with the Avalara tax system.

In my experience if you fail to keep your end users satisfied with the speed of the application they become very opposed to the upgrade. That is what I've been experiencing with the 5.0 upgrade. Everything works pretty smoothly with the exception of some of the CRM functions. A sample operation using the "cntct" table within 4.11 takes less than 2 seconds to display a screen. However the same operation within 5.00 takes 5.21 MINUTES to render the page. This is working with a cntct table that has more than 160,00 rows. Needless to say, my end users have become the enemies of the upgrade.

One of my observations is that there seems to have been a lot of usage of the CTE (Common Table Expression) within a number of the CRM metasql statements. I think this is contributing to the slow down. I personally find the usage of the "WITH" clause a very useful construct at times. I really like it however I think it may be a major performance killer within the Postgres 9.6 version we are using.

Does anyone else have any thoughts or observations on these slow downs?

Jim

[gmoskowitz]

Jim,

One possible problem is the introduction of the crmacctcntctass table to associate contacts with CRM accounts, giving much more flexibility in the CRM subsystem by creating a really long table. Consider creating two indexes on this table, one for the crmacctcntctass_crmacct_id column and another for crmacctcntctass_cntct_id.

Let us know what happens.

Gil

[anderson]

I thought I had already posted a patch to add those indexes in - but possibly not. In my testing those indexes made an insignificant improvement but that was on a recordset of several thousand records not 100's of thousands.

[jim.wirt]

Gil,
Thanks for the response. I will apply your suggestion and report back the results. By the way, I opened a support request with Xtuple customer support on July 2. My database has been uploaded and installed for their testing. Feel free to tinker with it if you want.
Jim

[anderson]

If you are interested here is the index code:

SELECT xt.add_index('crmacctcntctass', 'crmacctcntctass_cntct_id', 'crmacctcntctass_cntct_id_idx', 'btree', 'public'),
       xt.add_index('crmacctcntctass', 'crmacctcntctass_crmacct_id', 'crmacctcntctass_crmacct_id_idx', 'btree', 'public');

[jim.wirt]

Gil,
I created the two indexes on the crmacctcntctass table and ran my query. My postgres log reported the following:
"duration: 294294.588 ms statement: WITH chartext AS (SELECT charass_target_id, charass_char_id, . . ."

The query returns 1926 records

Jim

[anderson]

So that is unrelated to those indexes. The change to using the WITH clause for characteristics was an attempt to improve both performance of the characteristic query and to prevent the issue where if you have too many searchable characteristics the query never completed. I would need to check with the author regarding this part of the query

[rekosko]

I have 166,000 rows in the crmacctcntctass and over 137,000 rows in crmacctaddrass

I noticed that the metasql crmaccounts detail
has the

FROM crmacct()

which I change to

FROM crmacct

and performance was improved

The function crmacct() has something to do with permission checking

see if that helps you