Wednesday, July 28, 2010

Overview performance improvements in Exact Globe in the last years.

Photo credit Alancleaver_2000
Over the years a lot new release have become commercial available. A lot of new functionality has come available. With the information we received from customers who have used the Exact System Information tool we have seen that they are not aware of available functionality. The Exact System Information tool is made for customers to help them in improving the performance of their Exact solution. A lot of customers uses Exact Globe already for years. In their administration they have a lot of historical data. Some clean up applications has been made to clean up the database. This will result in a smaller backup and can help to improve the overall performance.

This blog post will give an overview of all performance improvements and cleanup functions.
  • Clean up log files. Release 360. Functionality is made to clean up ‘Application log’ or ‘Masterdata log’
  • Clean up of historical journal records. Release 360. Historical data in Globe is stored in multiple tables in the database. In the previous release of Globe application, there is no process in place to perform the clearing of old records. As the database size grows from period to period and from year to year, the system performance will be deteriorated
  • Database structure optimized. Release 370. Exact Globe 2003 database used data type CHAR instead of VARCHAR to store data with length more than 10 bytes. CHAR always uses fixed length to store data whereas VARCHAR uses dynamic length which depends on the actual size of the data. Therefore from a database structure standpoint, the database was not optimized. As a result, the following drawbacks were introduced. Record was unnecessarily bigger which caused slower queries because less record can be retrieved in one disk I/O. Indexes were bigger which again reduced query performance
  • Clean Up XML Import Logs. Product update 395. Whenever you import XML files to Exact Globe, the XML import results which are the log of the relevant events will be recorded. An option to remove the XML logs are added to the functionality for cleaning up logs.
  • Cleanup tool to delete obsolete logistic records and MRP planning records. Product update 395. Logistic processes in Exact Globe generate MRP planning records. Examples of logistic processes are sales orders, blanket sales orders, return to merchant authorization (RMA) orders, purchase orders, blanket purchase orders, return to vendor (RTV) orders, interbranch transfers, and quotations. Over the years, as customers’ databases become larger with logistic transactions, the database actions on these records become slower. Historical records such as completed sales or purchase orders may be considered obsolete after the completion of the logistic processes over a certain period of time. In order to enhance the performance of the administration, these obsolete logistics and planning records are best deleted.
  • Retrieving Balance Totals. Product update 395. Due to the database structure of Exact Globe, the retrieval of transaction totals requires the system to totalize all transactions. Over time, as your database becomes bigger and filled with transactions, the retrieval of balance totals becomes slower. Faster retrievals are therefore necessary to help you work more efficiently with the system. Enhancements have been made to improve the performance of retrieving balance totals
  • Database Performance by Optimizing Index. Product update 397. In every Exact Globe product update, system performance is improved by optimizing the database indices in Exact Globe. In this product update, a tool is implemented to optimize your database instead of the standard indices in Exact Globe. With this method, the indices will be deployed only when it is required by your SQL server. This method also reduces deployment risk because you can easily add or remove the indices if you are not satisfied with the performance result. There will also be performance improvement for functions that use the new index.
  • Cleanup tool to delete obsolete logistics records extended to Production Orders and Stock Allocations.Product update 398. The scope of the cleaning up tool is extended to completed production orders and all the respective allocation entries. The allocation entries refer to stock allocation records generated from back-to-back orders and sales order enrichments. Consequently, the absence of production orders and stock allocation is no longer the prerequisite for executing the tool.

Tuesday, July 27, 2010

Performance improvement Globe (398): Logistic MRP Cleanup Tool Extended to Production Orders and stock allocations.


Photo Credit: Donger
As of Exact Globe product update 398 is is possible to delete the obsolete logistics and planning records for production orders and allocation entries. This is a good extension to the MRP cleanup tool which is released as of product update 395.

In Exact Globe product update 395, the logistics MRP cleanup tool for cleaning up obsolete logistic records and budget entries without affecting the related processes was introduced. The records included completed sales orders, blanket sales orders, RMA orders, purchase orders, blanket purchase orders, RTV orders, interbranch transfer requests, and quotations. For more information, see Product Update 395: Improved Performance with Obsolete Logistic Records and MRP Planning Records Cleanup Tool .


However as of product update 398, in order to enhance the performance of the administration, system administrators also need to delete the obsolete logistics and planning records for production orders and allocation entries. As such, in this implementation, the scope of the cleaning up tool is extended to completed production orders and all the respective allocation entries. The allocation entries refer to stock allocation records generated from back-to-back orders and sales order enrichments. Consequently, the absence of production orders and stock allocation is no longer the prerequisite for executing the tool. This enhancement will definitely assist administrators in boosting the performance of the administration with ease. For more information about the updated prerequisites, how to use the cleanup tool, and the latest list of functionalities that are affected by the cleaning up process, see How-to: Cleaning Up Obsolete Logistic Records and Budget Entries.

To get an estimate how much records can be cleaned up, please run the Exact System Information tool and request an improvement report. More information about the improvement reports of the Exact System Information tool can be found here.

Enjoy the update to Globe product update 398.

Exact Globe supports SQL Server 2008 R2 as of product update 398




 In July 2010 the Exact Globe product update 398 is released. As of this product update 398, Microsoft SQL Server 2008 R2 is supported. Beside this Office 2010 (32 bits) is supported. More information about this product update can be found here.

Be aware that a database which is upgraded to SQL 2008 R2 can't be used on a previous version of SQL Server.

Enjoy your upgrade to SQL 2008 R2.

Sunday, July 25, 2010

Back from holiday, new blog posts will come soon.

The last 3 weeks I have been on holiday. It was fantastic, good food, wine, relaxed a lot. We did a lot of nice activities with the children like swimming, playing tennis and golf, drive karts, climbing in trees and one of my favorites during holidays: fishing on carps.



Now the holiday is over, I will continue to post new articles about SQL Server. See you soon.

Tuesday, July 20, 2010

How to retrieve numbers of records in a table with high performance without index or tablescans?


Photo Credit: dodge challenger1

The most common way to retrieve the number of records in table is done with a query like:
select count(*) from tableX where column1 = 'value'

Using this kind of queries will result in a (clustered) index scan. On tables with millions of records this can still take a while to execute and can result in unnecessary overhead on the SQL server. SQL server stores a lot of statistics of your tables. These statistics are used by the optimizer to generate the most efficient queryplan. You can use these statistics to retrieve the number of records in your database. This is always faster then to query the table itself.
SQL server uses his own mechanisme to update the statistics, so it can happen that there is a small mismatch between the number of records in a table and the number of records stored in the statistics. For big tables this is not always so important. For instance to display the growth of your tables.

You can use next query to retrieve the number of records per tables via the statistics:

SELECT sysobjects.name,rowcnt
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id=sysindexes.id
WHERE xtype='U' AND indid IN (0,1)
AND sysobjects.name = 'Your table'

To check for the number of records for a column with a specific value use next query. This query wil return the number of records per column value. Available columns are columns which exists as the first column in an index. Example you have a table: X with the column: Type. The column type is used as the first column in an index. Next query will return all existing values for the column type with the number of records.

DECLARE @TableName Nvarchar(200)
SET @TableName = 'Your Table'

DECLARE ColumnStatistics CURSOR FOR
SELECT Tbl.name AS TableName, SSID.name as IndexName ,
         INDEX_COL( tbl.[name], idk.indid, 1 ) AS IndexColumn
FROM SYSINDEXkeys idk
INNER JOIN SYSOBJECTS tbl ON idk.[id] = tbl.[id]
INNER JOIN SYSINDEXES SSID on SSID.[ID] = TBL.[id] and ssid.indid = IDK.indid
WHERE tbl.ID = (SELECT ID FROM SYSOBJECTS WHERE name = @Tablename)
           AND keyno = 1

OPEN ColumnStatistics

DECLARE @IndexName Nvarchar(200)
DECLARE @IndexColumn Nvarchar(200)

FETCH NEXT FROM ColumnStatistics INTO @TableName,@IndexName, @IndexColumn
WHILE @@FETCH_STATUS = 0
BEGIN

CREATE TABLE #TempTable (ColumnValue nvarchar(200),INT1 INT,EQ_ROWS INT,
    INT3 INT,INT4 INT,TableName nvarchar(200))
INSERT INTO #TempTable (ColumnValue, int1,EQ_ROWS,int3,int4)
EXEC ('dbcc show_statistics(' +@TableName+','+@IndexName+') with histogram')

SELECT @TableName AS Tablename,@IndexColumn AS IndexColumn,ColumnValue, EQ_ROWS AS Records

FROM #Temptable
DROP TABLE #TempTable

FETCH NEXT FROM ColumnStatistics INTO @TableName,@IndexName, @IndexColumn
END
CLOSE ColumnStatistics
DEALLOCATE ColumnStatistics

How to check if and when the statistics are updated?

execute next query:
EXEC sp_autostats 'Your table'



Check for the column AUTOSTATS. The value should be set to ON.
Check for the column Last Updated to see when the statistics for the index is updated for the last time.


Enjoy using the statistics.