Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 295 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Navision is Very Slow after migration from 3.7 to 5.1 sql
vsalot
Member Posts: 47
Hi All
Navision working very slow after migaration from 3.7 native DB to 5.1 SQL 2005 DB.
Is this because of VSIFT or there is a bug itself in application or might be in SQL 2005? we are using latest Service pack of SQL 2005. No index hinting no recompilation..
We are having database size more than 400GB. Because of the licence issue, we simply migrated to SQL DB. I did everything. Code optimization, Reindexing, Proper Key enabled. Still the problem is there. Because of VSIFT, calculating flowfield is extremaly slow. posting itself is taking too much time. commiting transactions is also taking time.
What is to be done in this case? Also doing filter in large tables on primary key with PIPE (|) sign is taking too much time. If I use filter 1|38569302 "for Entry No." in G/L Entry table, it is taking min. 4-5 minutes to 30 minute maximum in newly migrated Database. if I use 1..38569302 then it will come in a flash.. Whereas in Native DB it is coming in less than a second.
Is there any solution for this?.. Please help me..
Navision working very slow after migaration from 3.7 native DB to 5.1 SQL 2005 DB.
Is this because of VSIFT or there is a bug itself in application or might be in SQL 2005? we are using latest Service pack of SQL 2005. No index hinting no recompilation..
We are having database size more than 400GB. Because of the licence issue, we simply migrated to SQL DB. I did everything. Code optimization, Reindexing, Proper Key enabled. Still the problem is there. Because of VSIFT, calculating flowfield is extremaly slow. posting itself is taking too much time. commiting transactions is also taking time.
What is to be done in this case? Also doing filter in large tables on primary key with PIPE (|) sign is taking too much time. If I use filter 1|38569302 "for Entry No." in G/L Entry table, it is taking min. 4-5 minutes to 30 minute maximum in newly migrated Database. if I use 1..38569302 then it will come in a flash.. Whereas in Native DB it is coming in less than a second.
Is there any solution for this?.. Please help me..
0
Comments
What kind of hardware are you running sql on?
What kind of RAID configuration do you have?
Majority of bottle necks are on improper raid configuration and not having enough memory.
Are you running 64 bit sql ?
OS/Navision/SQL Binaries (2 disks mirrored), Log Files (4 disks -> RAID 1+0) & TempDB (2 disks mirrored).
Datafiles seperate set of 4(146 GB x 4 HDD set configured in RAID 1+0).
We are Using 32 bit SQL. Memory 8 GB.
You don't mention how many users. From my experience, a 4 drive RAID 10 is no where near adequate to support a database of this size. Besides how are you fitting a 400 GB database on that. A (146 x 4) RAID 10 would only have a useful capacity of about 270 GB formatted. Also are all these drives dedicated physical drives in the SAN?
Was this just a technical upgrade or did you also update the database code? If only a technical update then code may not be optional for 5.0 SP1. Things such as the proper use of the new FINDSET, FINDFIRST, FINDLAST commands and also VSIFT.
Why is good performance of this particular filter important? When would a user ever run a filter like this on G/L Entry set?
Because users are using this type of filter for so many tables, like G/l Entry, Barcodes, Item, & many more LS Retail Tables.. They are using almost 50reports or more than that based on this Pipe(|) filters only & believe me it is not working at all.
We did the technical upgrade & also have changed the database code as per the new functions suggested
.
Sorry.. It was a mistake. It is (300 * '8') RAID 10. Drives are in the SAN. In test servers we are using SAS Drives, with 16GB RAM. but no difference. We also tried with 64bit OS & SQL with 32GB RAM in test environment but no difference. LAN speed, some places it is 1GB & some it is 100Mbps.
Total numebr of Concurrent users are more than 150 at any point of time.
As we are having millions of records in the tables. Before it was SIFT in 3.7 native, but now because of VSIFT it is taking time like anything for reading e.g Flowfileds & also in posting because at some places we are calculating flowfilelds while posting the documents for some validations & Also in standard LS Retail , it is calculating flowfileds while posting the Sales transactions.
Also one can filter on any table having 1 mn records with PIPE(|) sign in Nav 5.0 or in Higher version with SQL 2005 & check the time. It wil not work at all. I tried in Cronus DB on SQL. Through report I inserted 1000000 records in blank empty customized table. & when i am trying to retrieve the records using Table filter, that is 1000|1001, it is taking too much time, first time approx 4-5 mins. no flowfilter nothing is there. One can pls check & let me know..
Nav is a scalable system, and it's your "tools" that you cannot use properly.
< /sarcasm >
Can you give me an example of where a user would use a filter like this on G/L Entry? Where a better key would not serve the purpose. I agree that users may use this type filter on other tables such as Item. I just don't see its pratical use on the "Entry No." field of the G/L Entry table.
We are having customization for some of the reversal entries in G/L Entry. Where at the time of reversal we are passing the entry no. of the original Document no. So user used to check the G/L Entry based on that. So they will check 123456|654321 like that. This is just an simple example. We are having Item tables with 2mn Records, Barcode tables having 2.5mn records, Trans. Sales Entry having 7mn records, G/l Entries having 50mn records & many more customized Tables (having 300 Customized tables), where this kind of filter (Pipe) is required based on the Item code or Barcode No. etc..