Navision is Very Slow after migration from 3.7 to 5.1 sql

vsalot

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..

generic

I'm sorry to hear that.
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 ?

vsalot

We are using Server HP DL580 G5 & Using HP storage connected to the server via fiber cable.

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.

bbrown

vsalot wrote:

We are using Server HP DL580 G5 & Using HP storage connected to the server via fiber cable.

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?

bbrown

vsalot wrote:

Hi All

Navision working very slow after migaration from 3.7 native DB to 5.1 SQL 2005 DB.

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.

bbrown

vsalot wrote:

Hi All
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.

Why is good performance of this particular filter important? When would a user ever run a filter like this on G/L Entry set?

generic

your ram memory is way t0o low. And you should be on 64 bit OS and SQL server with at least 64 gig of ram.

vsalot

bbrown wrote:

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.

bbrown wrote:

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.

We did the technical upgrade & also have changed the database code as per the new functions suggested

vsalot wrote:

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?

.

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..

generic

<sarcasm>
Nav is a scalable system, and it's your "tools" that you cannot use properly.
< /sarcasm >

bbrown

vsalot wrote:

bbrown wrote:

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.

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.

vsalot

generic wrote:

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..