Thread Details
  • State Not Answered
  • +1 person also asked this people also asked this
  • Replies 8 replies
  • Subscribers 354 subscribers
  • Views 1489 views
  • Users 0 members are here
Forum Actions
Related

team_sets_modules table usage?

Marko Vucak

Hi 

We have team_sets_modules table size over 5GB and is growing rapidly. How can we reduce size of that table? Are all those records necessary?

By documentation it says:

  • This table is used to manage team sets and keep track of which modules have records that are or were associated to a particular team set.

 

When i run queries on that table:

/* How many records with team_set_id are?  i get about 4000 */

SELECT * from team_sets_modules where deleted=0 and team_set_id <>"";

/* How many records without team_set_id are?  i get all other records, millions of them */

SELECT * from team_sets_modules where deleted=0 and team_set_id IS NULL;

I see that majority of records in that table only have id and module name, team_set_id is empty. Can we remove records without id?

Parents
  • 0

    Hi ,

    It is not natural for there to be records in the team_sets_modules table with a NULL team_set_id. It seems likely that there is an errant customization causing this behavior. Before doing anything with the records in this table, I recommend some investigation first.

    Check a few of the modules in question to see if they have records with NULL team_set_id. For example, you have multiple entries in this table for the opportunities module, so run the following query to see a sample of what records may have generated the NULL entries:

    SELECT * FROM opportunities WHERE team_set_id IS NULL AND deleted = '0' ORDER BY date_entered DESC LIMIT 0,10;

    This should give you an idea of how pervasive the problem is. Based on your description of the table size growing rapidly, I'm assuming this is occurring with recently created and/or modified records. Under normal conditions, all records in these tables should have a valid team_id and team_set_id. The most common scenario is that both values are identical when there is only a single team on the record. 

    Assuming that query returns results, you may want to check any customizations that are attempting to manipulate team assignments on a record. Let me know what you find.

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

  • 0 in reply to Chris Raffle

    Hi Chris

    Thank you for answer.

    I run that query on opp table and it returned 1 record, must be some old, not used, test record as it doesn't have name.

    Same query on products table returns 0 records.

    When i check how many we have in team_sets_module it returns 185762 related to opportunities.

    SELECT count(id) from team_sets_modules where deleted=0 AND module_table_name = "opportunities" and team_set_id IS NULL;

  • 0 in reply to Marko Vucak

    Hi Marko,

    If you're working in an isolated development environment, can you try creating and modifying opportunity records to see if either of those actions add records to the team_sets_modules table? Are you aware of any customizations that make changes to team visibility?

    Chris

    Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

  • 0 in reply to Chris Raffle

    Hi Chris

    Thx. There are no changes to team visibility. But project has a lot of customisations and it's using Sugar 9. I was trying to optimise it before upgrade to 12.

    I changed one field on Opportunity and it added 4 records,  then changed again and added 4 more.

    I deleted all logic hooks in Opportunities module and it happens the same. I don't even use forecast module but there are a lot of PD's created.

    I deleted all that has team_set_id NULL and it works fine so i will do that to clear data, probably update instance and see how it behaves in sugar 12.

Reply
  • 0 in reply to Chris Raffle

    Hi Chris

    Thx. There are no changes to team visibility. But project has a lot of customisations and it's using Sugar 9. I was trying to optimise it before upgrade to 12.

    I changed one field on Opportunity and it added 4 records,  then changed again and added 4 more.

    I deleted all logic hooks in Opportunities module and it happens the same. I don't even use forecast module but there are a lot of PD's created.

    I deleted all that has team_set_id NULL and it works fine so i will do that to clear data, probably update instance and see how it behaves in sugar 12.

Children
No Data