What are these tables: [Company Europe$75832$1]

Aziona
Aziona Member Posts: 7
edited 2013-01-02 in Navision Attain
We use Nav3.6 since 2003 and use it for the administration of 7 companies. From 2003 to 2009 the size of the SQL Server 2000 database grew from 0 GB to 55GB. Since 2009 it grew to 160 GB.

It seems that the number of tables with unclear names [Company Europe$75832$1] has grown significant the last few years. There are now approximately 200 of these tables per company, so times 7. Apart from the normal tables such as [x$G_L Entry].
What is the purpose of these tables and how can I can know which of these tables are necessary and which are not?

Comments

  • DenSter
    DenSter Member Posts: 8,307
    They are SIFT tables. Each one of those table is used to keep SumIndex totals. Determining which ones are needed is a job for a specialist, I would leave them alone if I were you. Are you just asking about the use of those tables, or are you experiencing performance issues? If you don't have performance issues, I would not take any action.

    One thing you can do is to optimize the tables in each company. Open a company, go to File, Database, Information, and click the 'Tables' button. From there you can filter on a particular company, select tables, and click the 'Optimize' button. This will rebuild those SIFT tables, and get rid of records that are no longer needed.

    You do need to have proper maintenance in place. Take a look at this video for some inspiration: http://youtu.be/0KbZkKdyZps Sorry about the audio.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • MarkHamblin
    MarkHamblin Member Posts: 118
    Another option (if it's realistic) is to look at doing a technical upgrade of the database to run with a NAV5+ client. The newer versions do a much better job managing SIFT and SQL in general than the older versions.
  • DenSter
    DenSter Member Posts: 8,307
    You mean 5.0 SP1 or higher. You could only do that if your license can handle that.

    To elaborate on that - starting 5.0 SP1, they implemented SIFT using indexed views instead of actual tables. No more issues with empty SIFT records, and no more need to optimize.

    You still need database maintenance though.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • MarkHamblin
    MarkHamblin Member Posts: 118
    DenSter wrote:
    You still need database maintenance though.
    Agreed - far too many NAV databases out there with no maintenance plans or even manual maintenance being done.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    DenSter wrote:
    You mean 5.0 SP1 or higher. You could only do that if your license can handle that.

    To elaborate on that - starting 5.0 SP1, they implemented SIFT using indexed views instead of actual tables. No more issues with empty SIFT records, and no more need to optimize.

    You still need database maintenance though.

    Actually they retrograded 5.00 to use VSIFT, so if you apply hot fixes to a 5.00 executable it "upgrades" to 5.00 SP1 VSIFT. One of those things I found out the hard way.
    David Singleton
  • Aziona
    Aziona Member Posts: 7
    Hi, thank you for all the answers. We are actually going to do an "EXE" upgrade in a few weeks. I optimize some tables in the test environment to see whether that has any effect.
    Can any of you tell whether it is normal to have 200 of these tables for each company? Is it possible that the number has grown in the past few years? What might have caused this?
  • David_Singleton
    David_Singleton Member Posts: 5,479
    Aziona wrote:
    Hi, thank you for all the answers. We are actually going to do an "EXE" upgrade in a few weeks. I optimize some tables in the test environment to see whether that has any effect.
    Can any of you tell whether it is normal to have 200 of these tables for each company? Is it possible that the number has grown in the past few years? What might have caused this?

    75832 is a custom table. Adding 10 sifts to 20 tables would give you the additional 200 that you are seeing. Talk to the developer and find out why they did it.
    David Singleton

Categories