Find orphan note records in a Dynamics GP company database

About the Dynamics GP Notes table

Notes are held within the SY03900 company table in Dynamics GP. Every note in that notes table has a NOTEINDX, which is used by other tables in GP to reference that note. This makes the notes system extensible, when new modules/addins are created for GP,  those modules can simply piggy back off the existing notes table for their notes too by inserting notes into the table and referring to them by index. However this does have the disadvantage that looking at a random note in the notes table, you have no idea as to what records may be referencing that note. 

Hence, if you were to delete records from tables that reference note records, it orphans any note record for that deleted record. The note record can still exist, but the record that refers to it has been removed. An example of where this could happen is, if someone were to delete a sales order line using SQL DELETE, without also deleting the note record in SY03900 that is referenced by that sales order line record. 

You see, the note table does not have a "source" field to identify the "owner" table, so there is nothing in the note record to indicate from what table the note record originated. Thus you must check all the NOTEINDX fields in all the tables over the entire company database in order to find its owner.

Lucky for us, using dynamic SQL we can do this. We can query SQL server for the table definitions of all the tables in the database containing the field named NOTEINDX. We can then copy all the values that exist from each of those tables found into one big list of NOTEINDX values. Finally we can compare the notes table with that extracted big list of all valid values and find where we have a value in the notes table that does not exist in any of the tables in the GP company database. These will (most likely) be orphan records. Note you must exclude the SY03900 table itself when preparing this consolidated list!

Dynamic SQL to find all references to note records

The following script does this. It looks for any table that have a field named NOTEINDX and inserts all the note index numbers from those tables and fields into a temp table, from where you may join it back to the notes table to find the notes that no longer have reference in the database (orphaned). 

If you are then going to use the results from this to delete notes, beware as if you have a third party product that uses notes but does not name its reference to the note as NOTEINDX, so I'm saying use this with care, especially if you start removing notes based on it, check what they are first and gain confidence they are genuine orphans.

 

IF EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE NAME = '##NotesConsolidated'
)
DROP TABLE dbo.##NotesConsolidated

SET NOCOUNT ON

CREATE TABLE ##NotesConsolidated (
NOTEINDX numeric(19,5)
,TableName VARCHAR(1000)
)
GO
DECLARE @SqlStatement VARCHAR(500)
DECLARE @DatabaseName AS VARCHAR(5)
SET @DatabaseName = cast(db_name() AS VARCHAR(5))

DECLARE This_cursor CURSOR
FOR
SELECT 'INSERT INTO ##NotesConsolidated (NOTEINDX, TableName) select NOTEINDX, ''[' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'' from [' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.NAME = 'NOTEINDX' AND NOT (t.name='SY03900' AND SCHEMA_NAME(schema_id)='dbo');
OPEN This_cursor

FETCH NEXT
FROM This_cursor
INTO @SqlStatement

WHILE (@@fetch_status <> - 1)
BEGIN
EXEC (@SqlStatement)

FETCH NEXT
FROM This_cursor
INTO @SqlStatement
END
DEALLOCATE This_cursor

SELECT * FROM SY03900
where NOTEINDX not in (
SELECT NOTEINDX FROM ##NotesConsolidated )

 

Results 

Examples of the four hundred and seven (yours will differ) tables containing references to notes in the notes table:

Solution to Acrobat reader slow to attach email, Get a link to your file, How do you want to share your file

Our Dynamics GP uses reporting services for generating sales documents, these documents often need to be emailed to customers and suppliers. It was recently brought to my attention that pressing the share icon on Acrobat DC to send a PDF by email launches a new share window. This window seems to often stall or take 30 seconds or longer to open sometimes with a spinning icon. I assume this is an attempt by Acrobat to draw people in the cloud document management solutions. For users transaction processing day in day out, this mounts up over a day and is annoying and inefficient.

Although I've not checked, I think this is because it will be contacting the Adobe servers in the background and our corporate firewall blocks a lot of traffic. Admin users don't seem to suffer this problem, which may be evidence of this being the case. 

Solution to slow to send email in acrobat:

To enable the envelope icon on the tools menu, to directly jump to attaching an email in outlook, avoiding this slow overlay share window,  then a registry change is required. The change is documented in the following acrobat resource, How to use the email icon to send a PDF directly as email attachment 

Basically the following registry change is required for DC users. Having applied this change outlook opens in sub-seconds  with the PDF attached. 

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Adobe\Acrobat Reader\DC\FeatureLockDown]
"bSendMailShareRedirection"=dword:00000000

Rebuild QTYONORDER of IV00102 Dynamics GP

I had to rebuild the quantity on purchase order of the inventory site qtys table today (On Order in Item Enquiry).

Wrote this SQL script that works for our install with our settings, but you should verify your install does not have other modules or modifications that may make this break.

 

Comment out the UPDATEs and uncomment the selects to see what is going to happen first, running SQL against your production environment is dangerous, test, test, test first. This is on the HIGH side of risky as a script to run. 

 

-- Rebuilds the qty on order in inventory from purchase order table
-- Don't know how manufacturing module would play with this
BEGIN TRANSACTION
-- Part 1 Correct the site/location qty on order from receipts and purchase orders
;
WITH CTE_ShippedQtys
AS (
SELECT PONUMBER
,POLNENUM
,SUM(QTYSHPPD*UMQTYINB )TotQTYSHIPPED
,SUM(QTYREPLACED*UMQTYINB ) TotQTYREPLACED
FROM POP10500
GROUP BY PONUMBER
,POLNENUM
)
,CTE_OnOrderQtys
AS (
SELECT SUM(CASE WHEN POTYPE IN (1,3) THEN ((QTYORDER - QTYCANCE)*POP10110.UMQTYINB) - ISNULL(TotQTYSHIPPED, 0)+ISNULL(TotQTYREPLACED,0) ELSE 0 END) AS QTYORDER
,ITEMNMBR
,LOCNCODE
FROM POP10110
LEFT JOIN CTE_ShippedQtys ON POP10110.PONUMBER = CTE_ShippedQtys.PONUMBER
AND pop10110.ORD = CTE_ShippedQtys.POLNENUM
WHERE POLNESTA IN (
2
,3
)
GROUP BY ITEMNMBR
,LOCNCODE
)
UPDATE IV00102
SET QTYONORD = ISNULL(QTYORDER,0)
--SELECT QTYONORD,QTYORDER, *
FROM IV00102
LEFT JOIN CTE_OnOrderQtys ON IV00102.ITEMNMBR = CTE_OnOrderQtys.ITEMNMBR
AND IV00102.LOCNCODE = CTE_OnOrderQtys.LOCNCODE
AND IV00102.LOCNCODE !=''
WHERE
IV00102.LOCNCODE!=''AND
(QTYONORD != QTYORDER
OR (QTYONORD!=0 AND QTYORDER IS NULL))
ROLLBACK TRANSACTION

--Summ up the location on order Qtys for the summary record
BEGIN TRANSACTION;
--Part 2 Correct the summary location
WITH CTE_SumLocations
AS (
SELECT SUM(QTYONORD) totqty
,ITEMNMBR
FROM IV00102
WHERE LOCNCODE != ''
GROUP BY ITEMNMBR
)
UPDATE IV00102
SET QTYONORD = totqty
--SELECT *
FROM IV00102
JOIN CTE_SumLocations ON CTE_SumLocations.ITEMNMBR = IV00102.ITEMNMBR
AND IV00102.LOCNCODE = ''
AND IV00102.RCRDTYPE = 1
AND QTYONORD != totqty

ROLLBACK TRANSACTION

Yes you can - SQL Server Table Compression and Dynamics GP


2019-03-01_23-12-19

Today I attended SqlBits in Manchester UK, where there was a session “Performance tuning SQL server on crappy hardware” by Monica Rathbun.

Monica has a the fast and punchy presentation style I enjoyed. Although I had already experienced or knew most of what was covered it was still a good presentation. There was one take away I noted in my notebook to comeback to later. Now back at the hotel I’m having a look.

Row/Page compression - More Data in MEMORY

Monica was promoting the use of COMPRESSION – not just backup compression but ROW/PAGE database compression in the database engine itself.

By compressing the data in the database, the theory goes that you reduce I/O required to move the data around and allow much more relevant data to be held in SQL server’s caches and perhaps the underlying storage system’s caches too. Having more data in memory leads to a more performant system.

For some reason the existence of compression in the database as was something that had slipped under my radar, perhaps because it used to be an Enterprise feature but now its available to me in our SQL2016 Standard Edition.


This is particularly interesting to Dynamics GP users as our database is full of padded CHAR data types, has very wide tables full of only partially used data (depending on modules used) or repeating data in the case of settings flags. Dynamics GP also has many tables full of decimal columns that are all zero, again due to configuration or options in how GP is set up or what modules are active. So from the outset it feels like Dynamics GP would benefit.

“Enabling compression only changes the physical storage format of the data that is associated with a data type but not its syntax or semantics”. This means the compression occurs inside the SQL engine but is transparent to the application interacting with SQL server. There are two levels of compression of interest and available to us. ROW compression takes each data row in the table,

  • It uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money).

  • It stores fixed character strings by using variable-length format by not storing the blank characters.

So imagine how much room can be saved when you consider the fields in Dynamics GP are fixed length!

What is more there is another option, PAGE compression that looks at repeating data within the pages of data stored on the filesystem and compresses that data. As this is over an entire page its more heavy on CPU resources but is great where there is a lot of repeated data down the rows of a table. Wait, repeating data down rows of a column? – That is what we get lots of due to status flags and little used fields in the GP tables that vary little from top to bottom of the table.

Just look at something like Item Master table IV00101 or one of the pricing tables etc. There are distributions and settings that are the same, repeated for all items and are ripe for compression as this leads to repeated content in the pages.

Data repeated down from table IV00101 Item Master


So both the nature of the data in the tables and the use of compressible data types by Dynamics GP sure makes it look good for compression.


Compression does cause more CPU load, but unless you are pulling millions of rows then it seems insignificant, see more here:

https://sqlperformance.com/2017/01/sql-performance/compression-effect-on-performance where it is proved it has little effect.


We can run EXEC sp_estimate_data_compression_savings 'dbo', 'IV00101', NULL, NULL, 'PAGE' ;

This will show us, by sampling a subset of the table, much as the statistics does, how much space should be saved by compressing the table, without having to actually do it. Let try with Item Master in Dynamics GP.

SELECT COUNT(*) from IV00101


EXEC sp_estimate_data_compression_savings 'dbo', 'IV00101', NULL, NULL, 'PAGE' ;

Item Master compression test

So we can see the item master table goes from 81,944KB to 16,304KB that is only 20% of what it was!

No trying it with IV00108  that has SELECT COUNT(*) FROM IV00108 = 6,107,169 rows and we get 779,816 going down to 139,440, that is only 19% of what it was before.

Compression testing with IV00108

So you can see how much saving can be achieved this way, imagine the reduced I/O from having 20% of what used to be read.

Even going down to ROW compression gives you only slightly less compression but less overhead too:

2019-03-02_00-31-51

Only 46% of what it was with row level compression.


Downside

There are not many downsides. The first is technical, compressing the data take up CPU, most SQL servers are not CPU bound in terms of resources, so this should not be an issue. Typically 10-30% increase, so check your current CPU load. As this is a table by table selection, you could tackle only the main most sizable tables in GP to get the majority of the benefit without having to apply compression to every table and index. When the data is written you take a hit on compressing it to reap the rewards later. So tables and indexes with great numbers of inserts per second may cause issues (have to be big loads).

The article below has some good scripts to see what will work and what will not…

https://thomaslarock.com/2018/01/when-to-use-row-or-page-compression-in-sql-server/


Upside

Much smaller data means less I/O more in cache. And more data in memory to make for more efficient queries.


Summary

I am going to gradually add tables to compression and see what happens to CPU usage. The benefits should be substantial in terms of reads so it seems well worth pursuing.


Support

This article would indicate its supported for Dynamics GP, although the tool referenced for choosing tables to compress is no longer available, however it is possible to manually work with the database to turn on compression.

https://blogs.msdn.microsoft.com/nav/2011/07/21/sql-server-data-compression-and-microsoft-dynamics/


This is the reason going to conference is so worth while, this is only one of many things I leant or got reinforced today in the various sessions I attended.