extraClause in CRMAcctCluser

[rekosko]

Hi All

I am using the latest xTuple 5.0

I have a screen that displays the CRM Account Cluster for a custom role.

So I use the extraClause in my script

ins_typecombo.extraClause(" crmacct_id in (select crmacctrole_crmacct_id from public.crmacctrole where crmacctrole_crmrole_id = (select crmrole_id from public.crmrole where crmrole_name = 'Insurance'))" );

This should return only our custom roles Insurance

But when I click the list menu of the cluster, all the crm accounts are returned.

So after reviewing the _listAndSearchQueryString in the crmacctCluser.cpp, I changed the extra clause to

ins_typecombo.extraClause(" id in (select crmacctrole_crmacct_id from public.crmacctrole where crmacctrole_crmrole_id = (select crmrole_id from public.crmrole where crmrole_name = 'Insurance'))" );

to try to match the metasql query.

This did not work either.

SELECT *, getcontactphone(cntct.cntct_id, 'Office') AS contact_phone, formataddr(addr.addr_id) AS street
FROM
(
SELECT crmacct_id AS id, crmacct_number AS number,
crmacct_name AS name, cntct_id,
crmacct_active AS active, cntct_addr_id AS addr_id
FROM crmacct
LEFT OUTER JOIN crmacctcntctass ON (crmacct_id=crmacctcntctass_crmacct_id
AND crmacctcntctass_crmrole_id=getcrmroleid('Primary'))
LEFT OUTER JOIN cntct ON (crmacctcntctass_cntct_id=cntct_id)
) crminfo
LEFT OUTER JOIN cntct ON (crminfo.cntct_id=cntct.cntct_id)
LEFT OUTER JOIN addr ON (crminfo.addr_id=addr.addr_id)
where id in (select crmacctrole_crmacct_id from public.crmacctrole where crmacctrole_crmrole_id = (select crmrole_id from public.crmrole where crmrole_name = 'Insurance'))

returns 505 rows in the Query Analyzer.

I just dont know if the extra clause is inside or outside the crminfo group.

So is my extra clause wrong or is it not sent to the List() function?

Lastly, the _listAndSearchQueryString should be in the metasql.

the FROM crmacct() function takes over 7 seconds to return the rows and only 145 ms when I remove the ()

I know this function uses security, but we would prefer the faster query

Thank you

Bob

[anderson]

Hi Bob,

Your extraClause should look something like:
_crm.setExtraClause("crmacct_id IN (SELECT crmacctrole_crmacct_id FROM crmacctrole JOIN crmrole ON crmacctrole_crmrole_id=crmrole_id AND crmrole_name = 'ROLENAME')");

however I am seeing the same thing in that the extra clause does not appear to be sent to the list query. I have raised issue #34895 to address this problem.

Regarding the security function performance, can you please give me an indication of how many CRM accounts you have in your system?

SELECT count(*) FROM crmacct

Obviously we want to retain the privileges to view the correct accounts, but also have that return the list as quickly as possible.

[rekosko]

thank you Anderson,

We have around 150,000 crm accounts broken down into custom roles such as Employer ( 30,000) and Insurance (500), The rest are Doctor Practices and Patients.

I The query that took seven seconds was only for the 500 Insurance companies

When I query select * from crmacct() , it takes 37 seconds.

[anderson]

Thank you for the information. The crmacct() function has to do some complex privilege checks before returning. This was made more comprehensive with the changes in version 5.0 which explains why this function is performing worse. It will still be worth investigating whether we can modify anything to make the function and the underlying functions perform quicker on large datasets,

One thing I found when testing is the database cache is very important. The first time I ran this function it warmed the cache, and subsequent calls to the function were 80-90% quicker.

[rekosko]

I am going to modify the function crmacct() to just return the rows and disable the privilege checks. Since we are still in development, I will have a chance to witness any complication of this decision.

But I really need the ExtraClause to work.

[gmoskowitz]

Bob,

The extraClause problem is recorded as bug 34895.

I see some things to try in the crmacct() function but I don't know how much they'll help.

Gil

[rekosko]

Thank you Gil

I can modify the crmacct() function and remove the security checks for now.
That will keep management happy; speedwise.
I am not too sure we need that level of security.
We can always add it back in

[rekosko]

This is to let you guys know that the extra clause still doe snot work in the crmacct cluster in 5.0 RC
This is a major show stopper for us because we have over 100,000 crmacct broken down by roles

I will try to debug further and see what I can find.

Bob

[rekosko]

my mistake, the extra clause is working, I was using the wrong function (extraClause)