Precalculation and MRP performace in Beas

SYSTEM FUNCTIONALITY: CAUCULATION AND MRP 

When you run the batch calculation or MRP in Beas, especially in big size databases (for example in DB size more than 100GB), or the BoM structure is more complicated, occasionally you may find that the system is very slow.

During operation, databases of SAP Business One become fragmented due to insertions, updates, and deletions of data. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade performance. 

We recommend running the RSP task on a monthly basis to perform a database re-index on your customer's database. Please refer to RSP Task 1469218 for the task details.   

DECLARE @tableName as sysname  

DECLARE @strExec as varchar(1000) 

-- Cursor declaration  

DECLARE tableNameCursor CURSOR READ_ONLY FAST_FORWARD FOR 

 -- Take all user table 

 SELECT [name] FROM sysobjects WHERE xtype = 'U'  

OPEN tableNameCursor  

FETCH NEXT FROM tableNameCursor INTO @tableName 

WHILE @@FETCH_STATUS = 0  

BEGIN 

-- Create the statement  

SET @strExec = 'dbcc dbreindex (''' + @tableName + ''','''',0 )' 

 -- Execute the procedure 

 exec (@strExec) 

FETCH NEXT FROM tableNameCursor INTO @tableName  

END 

CLOSE tableNameCursor  

DEALLOCATE tableNameCursor 

The re-index procedure takes a while; on a large database it can be more than 2 hours, and during this operation the database will not be available for use. Refer to Microsoft Developer Network for more details on this topic.