Archiving NAV Data

Kobi · 2007-10-10

Has anyone run across a solution for Archiving NAV data? Either Customized or 3rd Party?

Tbls like G/L Entry, Item Ledger Entry, Ledger Entry Dimension, Sales Shipment Line and Sales Invoice Line are getting very large.
I'm looking for a solution to archive the data out of our Production system and saved on another system but still able to look up the historical data and report on it.

Just had to start optmizing some Whse tables because of so much blocking.
5767 Warehouse Activity Line
6550 Whse. Item Tracking Line
7312 Warehouse entry

I'm running into ](*,) 's

Thanks, Kobi

NAV 3.70 4.0SP3 - SQL

cnicola · 2007-10-11

Hi Kobi,

There are standard Navision routines to compress ledger entries (Item, G/L, Customer a.s.o.). They are all under Periodic Activities -> Date Compression in each module. I don't think there is anything for posted documents though in theory you can always move some to a different table.
But can you gives us more details about the size of your db, the number of records you have in the problem tables, the spec of your server, number of users?

A lot of the locking and speed issues can be solved with just optimizing your db and server for SQL (assuming you have not done that yet) so you might not need any compression (for time being).

Kobi · 2007-10-11

Server is a DELL 2800, 50 Users
Database Size: 66,701,952
Database Used: 58,872,192

Sample of some of our largest tables by Size (KB)
Table Name Size (KB)
Value Entry 9,030,160
G/L Entry 5,912,736
Warehouse Entry 5,419,768
Ledger Entry Dimension 3,933,856
Registered Whse. Activity Line 3,252,376
Item Ledger Entry 3,215,304
Prod. Order Capacity Need 3,172,896
Sales Line Archive 2,963,416
Sales Shipment Line 2,744,464
Sales Invoice Line 2,468,352
Analysis View Entry 1,623,984
Prod. Order Routing Line 1,520,896
Prod. Order Component 1,452,808
Sales History 1,256,656
Change Log Entry 1,175,752
Posted Document Dimension 1,017,040
Prod. Order Line 708,608

Sample of some of our largest tables by No. of Records
Table Name NoOfRecords
Ledger Entry Dimension 61,825,037
G/L Entry 13,350,462
Posted Document Dimension 12,191,521
Analysis View Entry 10,589,539
Warehouse Entry 6,411,873
Change Log Entry 5,695,033
Document Dimension Archive 5,160,905
Value Entry 5,123,500
Registered Whse. Activity Line 3,879,411
Item Application Entry 2,298,652
Item Ledger Entry 2,226,615
Sales Shipment Line 2,185,643
Production Document Dimension 2,119,492
Sales Invoice Line 1,844,047
Sales Line Archive 1,779,339
Posted Whse. Shipment Line 1,548,859
Item Entry Relation 1,485,578
Value Entry Relation 1,485,168
Prod. Order Capacity Need 1,312,796
Sales History 1,190,116
Prod. Order Routing Line 682,405

bbrown · 2007-10-11

Can you provide specific details on the hardware including configuration details? Also what database maintenence is being performed on a regular basis? Plus has any application tuning been done?

This database does not seem large enough to need to consider removing data

cnicola · 2007-10-12

Wow

You guys decided to use all the possibly problematic functionalities for SQL: dimensions, WMS with Zones and Bins, Analysis Views AND Manufacturing.
And man Ledger Entry Dimension 61,825,037 entries???!!!! How many dimensions did you guys setup?
As bbrown said we need to know what the HW is. Also regular maintenance like reindexing db (once every 1 or 2 weeks), updating statistics (once a week) and backing up log (preferably every day if not several times a day).
Your db size should still be manageable. My largest db now is 160GB and has about 1.5 times your entries in ledger entry tables but I do not use that many dimensions so I only have about half the dimension entries.

Once your HW is set there are a lot of things that can be done to the db to eliminate locking and speed up the system especially on retarded things like dimensions and Sales Archives. Some creative things can be done with Warehouse Entries as well.
Also please tell me you do not have any Analysis Views with "Update On Post" set.

cnicola · 2007-10-12

And the fact you are reporting locking on 5767 Warehouse Activity Line which does not even make it in your "Top 10" list makes me think you probably did not do any SQL tuning on your Nav db.

DenSter · 2007-10-12

There's nothing wrong with large tables. If that's required due to your business that's the way it has to be.

If you are having performance issues, I'd probably say take a close look at the use of dimensions, whether analysis views are updated automatically (this is a performance killer especially with lots of dimensions), at how change log is used (another performance killer), automatic cost posting, things like that.

Of course hardware review, but also some database settings, and index tuning.

Rallnus · 2007-10-12

From time to time we export old and not often needed data in .txt-files via dataport. Another method is to delete empty lines in the table "shipment lines" for example or to disable unused keys. I wouldn't prefer deleting of entrys - and our financial auditor too.

Kobi · 2007-10-12

Wow, thanks for all the replies.

Here is more of the story

Dell 2800 Summary:
2 dual core 3ghz cpu's, 8 GB memory. 8 hard drives mirrored, OS Windows 2003 64 Bit, SQL 2005.

Current database maintenance being performed on a regular basis:
Sql backup nightly and a shrink after the backup.

Any application tuning been done?
Have had a lot of work on the tables associated with Sales Order Entry, which helped a lot. Mostly review keys and removing those not needed.
We reviewed warehouse tables with another programmer but after testing didn’t find their suggestions much of a help on performance.

Anything we aren't doing that may need to be done?

cnicola · 2007-10-12

Hi kobi,

You need to backup the database which will empty out the log but make sure you do not shrink the log. Just more work afterwards for SQL to growing it again.
You must absolutely run update statistics and rebuild or reorganize indexes on a regular basis.

And 4 pairs sound a bit low (ok a lot low

). I am using 5 pairs for data and 2 for log at a minimum at a client and looking to double that soon.

As for application there is a lot of places you can look at but it is tough to say without looking at the system.

cnicola · 2007-10-12

Oh and this is a personal preference but I do like "Always Rowlock" option so I have it on my large SQL dbs. Except it eats up a lot of RAM on the server so I usually start at 16GB min.

DenSter · 2007-10-12

'always rowlock' was put in as a bufix for a SQL Server 7 issue. It should not be necessary anymore. Does it really make a difference in performance to turn it on in your experience?

cnicola · 2007-10-12

It absolutely makes a difference in locking from what I tested. When I went to MS Navision SQL class last winter they said it is an option but that it will use a lot of RAM. I never got a straight answer as to what "a lot" is but on my clients 16GB seems OK (though more on RAM never hurt

)
BTW I mean Always Rowlock on the Navision side which sends the hint of rowlock in queries.

jlandeen · 2007-10-14

As the Data Compression routines were mentioned earlier...I have a question:

Has anyone successfully used the stock data compression routines? I know a number of clients who have tried unsuccessfully to use these routines as they've taken in excess of 3days to run.

Archiving NAV Data

Comments

Categories