Number of Companies in SQL

ara3n

Hello
One of our clients has 70 companies on a sql database. My question is, if we change tables that they aren't using, aren't in their license, to company independent. So there is only one table for 70 companies. will it have any performance improvements? These tables are just in sql and are empty. Will it improve, in terms off not having 49000 empty tables but only 700? Will the server take up less memory, eventhough the tables are not used?
Thank you.

kriki

I don't think it will improve performance. This because if the tables are not used, no records will remain in the DB-cache of SQL.
The only performance improvements you can get by doing this, is when you rename, backup, restore a company. This because it also touches the empty tables and if it is a DataPerCompany=YES, these won't be touched.

kine

I agree with Kriki, no performance gain, just when renaming. Only possible problems when customer buy new area of application etc.

ara3n

What about synchronizing Security? Instead of 49000 tables you'll have 700. Will synchronization performance for 4.0 SP1 improve if you have less tables?

kine

Yes, it will be problem, if you will add roles for all companies. But if the users will have only rights into one company and only for tables they need, it will be no problem, because the process add rights to tables you want - the tables you are not using and you will exclude from all roles, will be "invisible" for this process. (problem will be sync of SUPER user... :-)

ara3n

My question is the synchronization process will it go through 49000 tables even if your license doesn't have access to them, when the SQL Role is created? The performance will be definitely improved. Does the synch process. Delete the old role and creates a new one, or does it go through every table and sets permission based on Navision security?

kine

It is based on Permission table - it means, that it set permission only for tables which are in the roles of the user.