Thanks
James, Kilian, Karsten....
We do not keep Sales order or sales lines data. We delete it upon
invoicing. Also, the "void deleted orders" is turned off and our
table.SalesTableDelete is empty. When I run the Administration >
Reports > Size of company accounts the following list of tables are our top
ones. All of these tables has got more that 2 million records
and as you can see Stock transaction posting
has more than 11 million.
| Stock transaction posting | 11012916 |
| Ledger transactions | 9541567 |
| Sales order line - Update table | 5552715 |
| Stock transactions | 5506466 |
| Customer invoice lines | 5506458 |
| Posted VAT | 5385725 |
| Transaction log | 4393169 |
| Customer settlement | 3239164 |
| Customer transactions | 2254013 |
| Customer invoice journal | 2248285 |
| LedgerTransStats | 2230963 |
| Sales order - Update table | 2182593 |
| Sales order header - Updating table | 2182593 |
| Audit trail | 2178062 |
| CustInvoiceSalesLink | 2163242 |
| Shipping specification | 2162089 |
| Imported Shipments | 2159160 |
Like you suggest, Karsten,
we do not use any stock dimesnions. We are licensed for 7 dimensions but
we use them for Department, CostCentre, Credit Controller etc. in Accounts
Receivable but none in InventTable and InventTrans. Our InventDim
table has only one record in it but it is a dummy record with no Batch, Serial,
Location, Size, Color etc. All records in InventTrans refers to this
record through InventTrans.inventDimId.
Hope this sheds more
light.....
Regards,
Hennie
-----Original Message-----
From: James Flavell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 02, 2004 11:19
To: [EMAIL PROTECTED]
Subject: RE: [Axapta-Knowledge-Village] Axapta performanceHi Hennie,One thing I am not sure if you do is to delete the sales order lines and headers once invoiced this would at least keep your sales table (which is one of your main daily operations table) from growing too large, also the same for your purchase orders although I guess maybe you dont use them. Also I suggest you turn off the 'Void deleted orders' parameter otherwise your the table salestabledeleted will grow.Would be interested to see your top 10 to 20 tables in terms of record count, although I am not a DB or programmer it might help to focus on potentially where things have grown that could maybe be better controlled.As for archiving or purge from MBS I think we are all turning a bit blue in the face holding our breath (so far I have not had the 'pleasure' of having to deal with the volume of transactions you have mentioned)...James-----Original Message-----
From: Hennie Potgieter [mailto:[EMAIL PROTECTED]
Sent: 02 November 2004 18:57
To: [EMAIL PROTECTED]
Subject: [Axapta-Knowledge-Village] Axapta performanceHi,I want to rant about Axapta and if anyone wants to listen me out and give any advise it would be appreciated.....Some Background:We are a company in the courier and freight forwarding business and are using Axapta mainly for financials (AR, AP, GL). We do not have warehouses, BOM's, costing models etc. Our few (about 15) services that we offer has been captured as service items in the Inventory module, but other than this, we do not use inventory at all. Because Axapta is more geared towards manufacturing, we had to customize quite a few components to suit the services industry we're in.1. We have major performance problems on Axapta - especially around month end when the volumes of transactions reach a peak.2. We are creating and posting around 25000 sales order per day with an average of 5 lines per sales order. The performance of the posting is not acceptable. We also get lots of database locks from this posting process that sometimes takes minutes to resolve. We are running 3 AOS's on the application server (dual 3GHz Intel box; 4GB RAM) and a SQL Server DB on another hyper threaded 3GHz dual processor box with 4GB memory. What is your volumes like? Is ours exceptionally big or not really? Our AOS server is not straining. Our DB server sometimes hits a bottleneck on disk I/O.3. Because of our volumes, our database grows at an alarming rate. We are 8 months into Axapta now, and our database size is around 54GB, and growing at about 4 to 5 GB / month. If we need to keep like 5 years' financial data I do not want to do the calcs. How big are your databases and how do you manage the size? Has anyone developed some purging/archiving solution yet or are we holding our breath for Microsoft to come up with one in the next release?4. We are getting serious database locks on SQL Server when running big tasks simultaneously. What isolation level (allow dirty reads, read committed, serializable etc.) does Axapta use for its transaction processing and do I have any control over this? It sometimes seems as if Axapta prefers locking on a table level rather than on a row level and if these locks are exclusive, everyone waits!5. Do you allow reporting off your main database, or do you have a replicated / snapshot'ed database for reporting? Our performance problems get 10 times worse when a user kicks off a biggish report. Also Axapta was sold to us with the promise of users doing self-help reporting, which seemed nice back then. But if a user filters / sorts on a column that is not indexed on a table with 2.5 million rows it brings the DB server to it's knees. Also if the user accidentally klicks on the column heading on a form that displays a couple thousand rows and the DB tries to sort the grid, it kills the performance.That was my 2 cents.......Any tips/hints appreciated!Hennie
Sharing the knowledge on Axapta.
Sharing the knowledge on Axapta.
Sharing the knowledge on Axapta.
| Yahoo! Groups Sponsor | |
|
|
Yahoo! Groups Links
- To visit your group on the web, go to:
http://groups.yahoo.com/group/Axapta-Knowledge-Village/
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
