-
FindSet
Hello In Sp1 4.0, they released findset, and this lowers the sql to nav client communication a lot. this works great if you are filtering on a set of records. However, for Get function Navision communicates with sql every time unless it's the same record. For example. ItemLedger.setrange("Location Code",'123'); if…
-
Sql server 2000 Sp3
The question is this. I have a good hardware system with 70 users using Navision 4SP1 DB running on a SQL Server 2000 SP3 Enterprise edition. Beeing obvious that we must client upgrade from 4sp1 to 4sp3, i would like to know if there's some performance issue regarding the sql server 2000 sp4 upgrading. I read the…
-
Order By performance
In the SQL database, I have written in the code that finds the last item charge date. The code goes like this: SETCURRENTKEY("Item Charge No.","Inventory Posting Group","Item No."); SETRANGE("Item No.",Item."No."); IF FINDLAST THEN BEGIN IF "Item Charge No." <> '' THEN NewValDate := "Posting Date" ELSE EXIT(FALSE); The SQL…
-
isolation level
is there a way to change isolation level from within C\AL code to something less strict than SERIALIZABLE ? Am i right to state that LOCKTABLE defaults to SERIALIZABLE ? Even if i do a simple INSERT or MODIFY it defaults to locking on SERIALIZABLE level ? Is there a way to change it all to READ_COMMITED ?
-
Do reports use FINDSET automagically?
Just out of curiosity: when you run a report, does it get the records with FIND('-') - NEXT, or FINDSET(TRUE,FALSE) - NEXT or how does it work? BTW I'd love to explore these things myself but the point is when I run the client monitor, the SQL statements are cut to something like, I don't remember, 100 or 200 chars. And…
-
Table index affects INSERT?
I know there are a lot of aspects with SQL tuning. This question is more specific though... Does the number of indexes or using the wrong index affect the performance on INSERT statements on SQL Server?
-
Changing Company Name is very slow
A customer of us wants to change the company name in Navision. The change of a company name inside a native Navision database has a duration less then a minute. When our customer is doing this with his SQL database the duration is more then 45 minutes. This is very long. Does anyone know if this is normal and if this can…
-
installation
Hello everybody, We’ve got a customer who is upgrading from nav 2.60 to 5.00. They do have a lot of external program’s who communicate with nav true dataports. Now the customer wants to implement new external tools which they write themselves. They want to use the SQL-server version of Navision. It’s a small company, only…
-
Setting DEADLOCK_PRIORITY with sp_$ndo$loginproc
Hi all! Well, in a perfect world we would never encounter deadlocks with NAV, but unfortunately things are not that perfect ... so we will always have deadlocks, sometimes more, sometimes less. It is quite annoying, when a DL occurs and some unattended process, e.g. a NAS or NAV Job Scheduler, is killed, because these…
-
Keys
Hi, I have two questions about keys and SQL (Working on SQL): 1) Supose you have a key defined consisting of Field1, Field2 and Field3. Now you want to use this key to 'filter' on Field1 and Field3. I know it is not best to use that key for this reason, but in some cases (with more keyfields) it can be useful to use the…
-
Restore Backup time - in Nav 4.0 SQL
The version is 4.0 sp3 - running on new hp server with win sql 2005 A total of 89 companies in the database - No performance trouble in day to day ops. I did a backup of a company from my development net - the company has almost no data - Then started restore to my production server over night. Total restore time 16 hours…
-
nav application slow down problem
Hi wondering has anyone experienced the navision application slow down all of a sudden from some users within the same network trying to read/calculate from large data pool and the the whole thing just hang for everyone else in the network, and rebooting server seems like the solution? this problem came up for us when some…
-
clustered index on Ledger tables
Hello I was reading a sql optimation book, and in the book it talked about clustered index on sequentially increasing tables. Ledger tables in navision. Here is the quote. So does this mean Clustered index shouldn't be the entry no. but for example Item No., Entry No. and have 99 % fill rate so that new entries get filled…
-
Performance Probs [Log inside]
Hi, we have huge performance Problems within our Navision Databases. E.g. opening an item form may take up to 20 seconds!! Having a look into the performance monitor I made the following screenshot. At this moment the requisition worksheet was running over all items to made proposes for purchase. Does the harddisk queue…
-
Nav SQL Table Locking, performance Issue
Hi Experts, Hereunder is the brief Client Database Structure : Currently Operating 100 stores with a target of 300 stores within 1-2 years. Store : Each store has its own Navision native data base in which Primarily retails invoice are being generate. Statements are being created in day end and replicated to HO database…
-
Auto update statistics
Hi, one question. Has you activate this option or have you create an job, that every morning, like 4o clock, with sp_updatestats the statistics updates. Whats you meaning and properties on DB? Regards
-
Sift index and Sql index
I know that it's possible not to mantain SIFT indexing on SQL or not to mantain a sql index at all. The question is: is it possible (and does make sense) to mantain sift index and not sql index. For example, consider Item Ledger Entry. I'm interested to mantain the Entry Type, Item No. sum, but not the corresponding key on…
-
1 x RAID1 RPM10000 + 2 x RAID1 RPM15000:SQL
15k disks support more io/sec thus they can read/write more data/sec. Depending how you plan to split your database I'd probably leave transaction logs or backups on the 10k disks and put data on the 15k disks It also depends how you plan to use the arrays - files in the same filegroup , or seperate filegroups.
-
When is SQL Performance Tuning Needed?
Assuming the database is native 5.0 with Lanham E-Ship/EDI addon implemented. If there are no custom modifications to the Navision table keys, will we ever need to tune the database? If so, why doesn't Microsoft release a "pre-tuned" database?
-
Some weird locking
Hi guys, We are using EDI from Lanham and at times we may be creating 1000-2000 Sales Orders during 1 push of a button. We do get people locked up on the sales side (entering other orders or changing some) but let's say I can understand that. However it is locking pretty much everyone else (including say people on the…
-
Clustered indexes
Some links to clustered indexes. http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html http://www.sqlskills.com/blogs/kimberly/2007/02/02/TheClusteredIndexDebateContinues.aspx http://www.sqlskills.com/blogs/kimberly/2005/06/06/EverincreasingClusteringKeyTheClusteredIndexDebateagain.aspx…
-
table partition & filegroup partition
Hello All: I know Navision has one primary data file and one secondary data file. Is it possible if we can split to more secondary data file? Another question: I have a large table containt few million of records, now if I partition this table to be multiple tables (horizontal partittion), then will Navision be able to…
-
SQL2005 Performance Dashboard:how to reset counters
I am using this tool to tune the performance. And it is really helpfull. Especially the missing indexes part and the statements that request a lot of resources. So sometimes I decide to create a SIFT to avoid heavy "SELECT SUM(*" queries. And indeed, after doing that, the heavy "SELECT SUM(*" isn't executed anymore, but…
-
Few SQL questions
Few things I am curious about: 1. Always rowlock: I see people say if you turn that on it will use A LOT of memory. What is A LOT? I have 16GB on the server with 14GB allocated to the server (Navision 4.00SP3 with a DB of 10-20Gb for now but will surely go soon to 100GB + ). 2. Sift levels. Let's say I have 2 keys…
-
Bug in ApplyItemLedgEntry due to SQL optimizer
If in Item Journal Line you have several lines the lines after first may be not applied when they shoudl. This happens because theCASE EntryFindMethod OF '-': IF ItemLedgEntry2.NEXT = 0 THEN EXIT; '+': IF ItemLedgEntry2.NEXT(-1) = 0 THEN EXIT; END; in the ApplyItemLedgEntry function (codeunit 22) skips some open records…
-
FINDSET(TRUE, FALSE) with COMMIT inside the loop
I want to ask if the following code is okIF FINDSET(TRUE, FALSE) THENREPEAT...COMMITUNTIL NEXT = 0; and if it still uses the SQL optimization advantage over standardIF FIND('-') THEN and in general - do the FINDSET command's parameters stay valid after COMMIT ?
-
SIFT bucket
please excuse the question but I'm trying to get a handle on Navision. I have a SIFT table with 56.5 million rows and three bucket values - the tables's clustered index is almost the entire table. So I read some comments about removing SIFT tables and placing columns as includes within an index to gain performance. As the…
-
qty on hand using sift tables from sql
Hello. A lot companies use outside systems to grab qty on hand for items. Most of the use item ledger. This store procedure uses the sift tables just like navision Just change Company_Name to your company name and run the store procedure. CREATE PROC GetQuantity @ItemNo varchar(20)='%',@Variant varchar(10)='%', @Location…
-
SQL Errors Question
Hello all, I have the following error messages (I changed my company name reference to Company): Error: The Gen. Journal Line table has inconsistent SumIndexField values: Key fields: Journal Template Name,Journal Batch Name,Line No. SumIndexField table: Company.dbo.Live Company$81$0 Contact your system manager. Error: The…
-
Performance Question
Hi, we work with navision 40SP1 Since we updated to 40SP1 we have some performanceissues. The serverhardware is really fine and no leaks there. First i will do is to set the indexes to clustered, i hope it will boost the performance a bit. I reorganize data and index pages with a maintance plan daily. (fillfactor 30% )…