-
Query Optimization
hi all, When I run the below query in the query analyzer and show the execution plan it is as follows: select <- sort <- clus. index Seek. SELECT * FROM "testdb$Prod_ Order Routing Line" WITH (READUNCOMMITTED) WHERE (("Status"=3)) and "Ending Date"<'2010-06-01 00:00:00' ORDER BY "Ending Date" DESC,"Ending Time"…
-
run adjust cost item entries are very slow on NAV2009
Does anyone have a good way to speed up adjust cost item entries process? I have upgrade from NAV5Sp1 native to SQL NAV2009 and it take 5 times double from the old version.
-
"Searching" dialogue box gone after Migration to SQL
Hi, I just migrated my client's database from Navision 5.01 Native to Navision 5.01 SQL. We are using SQL 2005 server. With binary code page 437 collation. Whenever we try to apply filter that is not in sync with the key set on the form, system shows "Not Responding" for some time and then it shows the data. Actually in…
-
SQL tuning - keys
Hi all, We have just migrated from 5.01 Native to 5.01 SQL, and probably have a lot of SQL tuning to do. I have a question about low/high selectivity and SQLIndex property. E. g on the Sales Header table we have a key: Document Type,Status,Sell-to Customer No.,Settlement Type,Week No.,Month No. that in my understanding has…
-
Frequent blocking in Nav on SQL Server
Our client has started experiencing lots of blocking, which is slowing down their system. They only noticed this on Tuesday, it wasn’t an issue before that. The blocking happens several times a minute, for just a few seconds each time. It is experienced by many different users, in different areas of Navision, with no…
-
Flowfield slow for count type on NAV5SP1 SQL Server 2008
I have a customer database running under NAV 5 SP1 and SQL Server 2008. When I ran a table to analyze data, it was extremely slow to come up - about 15 minutes, then a 10 second wait to move to a new line. The table has a number of flowfields including several that use flowfilters. By process of elimination using a list…
-
SQL Server Cofiguration
Hello Experts, I am running Navision 5.0. Over the past few months, it has been running very slow. I am going to make a new configuration of the db Server in order to resolve make it running quickly. Data Base Info: Database Used (KB): 38,810,624 (47%) Database Size (KB): 81,960,960 With licence of 30 users. Server Specs:…
-
SQL 2005 64bit- Nav 5sp1 SLOW
Hi, I am implementing Nav 5ps1 with Tectura Life Science add on. Golive is in April 2009. I have been having slow performnce on a beefy SQL server 2005 box where, when I refresh a Production Order, it was taking 5 minutes to run! ouch. The same db on the Native Nav db took 10sec on a laptop! The consultant cannot provide…
-
Slow delete from Reservation Entry Table
We have a client that receives and ships using Lot numbers. Over the years, the Reservation Entry has become quite large. Now it takes forever to run the Requisition Worksheet. When using the client monitor in addition to the SQL Server Profiler, this statement seems to be taking a long time. DELETE FROM…
-
Performance degrade of a report after upgarde frm 3.7 to 5.1
Hi Experts, I had a DB which was in 3.7 (SQL 2000), which was recently upgarded to 5.1(SQL 2005) Now there is this customized report which was running without any issues in the 3.7 version, which now when imported into the 5.1 version without making any code or property changes has a severe performance issue. It is taking…
-
Technical Upgrade NAV5.0/Native -> NAV5.1/SQL
We have a customer that currently runs NAV 5.0 with native db. Now they want to switch to SQL2005. Wouldn't it be worth the effort to do a technical upgrade to NAV 5.0 SP1 at the same time to take advantage of the new enhancements in 5.0 SP1 regarding to SQL performance (e.g bulk inserts, indexed views etc.)? The customer…
-
SETCURRENTKEY, SETFILTER
I'm upgrading to Client v5.0. Is it still recommended to SETCURRENTKEY & SETFILTER for all key fields for optimal SQL performance or is Nav/SQL now smart enough to figure that stuff out? Thanks, Mega
-
Multiple NAV SQL dbs on Production server setup
We are a large international company and use multiple production Navision SQL instances (NAV 4, 5 and 2009). We have about 500 users evenly deployed across mainland Europe and the Americas. The usage time is during normal business hours locally. We have 12 instances ranging from 2GB to 100GB. We are migrating to a new SAN…
-
List form selecting every field causing slow performance?
We have a database that is on version 3.7B but the executables are upgraded to NAV2009 SP1. Everywhere in the system, the performance has increased except for the item card and the item list. The performance on the item card and list has severly decreased in performance, it takes roughly 3-4 seconds when I click on each…
-
Dynamic cursor behaviour
I just wondered about something: We know that NAV (the latest NAV version and we have some exceptions anyway. But I am not going into this now.) uses dynamic cursors for getting records. And we know that SQL in this case looks more at the ORDER BY clause to decide which index to use. And we also know that SQL looks more at…
-
LOCKING at INSERT with VSIFT
Hello out there. I like the RECORDLEVELLOCKING of the NAV-SQL. I also like the new SQL-views VSIFTS inside the SQL with NAV-SQL 5.1 and up. But: Can you explain to me WHY the INSERT of a single RECORD LOCKS almost the whole table if there is a SIFT on a KEY? I thought that next to the actual RECORD and the update of the…
-
Developers Toolkit Performance Tuning
At a customer they run the DevTookit DB on a SQL2005 Server. The ToolkitDB runs since several years in different versions. Currently the Toolkit is V3.01.0410 and the DB was updated to NAV2009 (no SP) coming from 4.03. I checked all objects in the DB and they do match the objects from the FOB MS packages with the current…
-
Specific Tables on Specific Drives
Please bare with me as I am not a SQL or hardware expert by any stretch. Hoping to learn as we go through this, though. We are currently on SQL2000 and Navision 3.7 but will be moving to SQL2008 and NAV2009SP1 very shortly. We have 125+ users and a 100GB+ database. Currently the data all resides on a single disk. We have…
-
Strange Navision reactions on SQL2000
Hello, We are sometimes facing a problem with performance of retrieving open Purch.Rcp.Lines in Form 51 Purchase Invoice. The most of the time it works fine, performance is good, but in some cases (could not identify any logic) Navision begins to read thru all Receipt Lines witouth using the right key set in setcurrentkey.…
-
Nav 2009 vs 5.0Sp1. performance
Hi, I have simple question about performance in NAV 2009 with New client (RTC i think). Is it working faster than standard client in NAV 5.o SP1? I mean, If some report generate 30 minute can I expect it will be faster?
-
Poor performance of SQL view
I've created a view which joins Item, Item Variant and Stock keeping unit tables. When running it from SQL 2005, the performance seems ok but from the object designer you can almost see each record being fetched. Has anyone else experienced the same issue? (System is based on NAV 5 sp1) Thanks Simon
-
SQL2008 – Results from Activity Monitor
I have only one SQL customer using SQL2008, so I tried the new Activity Monitor which shows several interesting figures about the server. I especially noted the Resource Waits area, which lists statistics from 10 different wait categories. I’m currently optimizing a heavily customized NAV solution where a few central…
-
Slow response time through TSE
Hi, We have the following installation : 4 companies, 2 databases (2x2 companies) Each SQL DB is hosted on a different SQL Server. SQL1 has 16 gb RAM SQL2 has 4 gb RAM Both SQL Servers runs : Windows Server 2003 R2 SP2 Microsoft SQL Enterprise Manager V8.0 NAV 3.70 The Network cards are the same, with the same driver. The…
-
SQL Uprade from 2000 to 2005 on NAV 4.0 SP3 YES OR NO
Hello All, in the past few weeks i went all over this forum to search any topics related to upgrade/don’t upgrade the SQL server that my organizations has, we use now NAV 4.0 SP3 with sql 2000 and everything works pretty much fine...we plan now a hardware upgrade and i was thinking that its a good opportunity to upgrade…
-
Deleting Keys
Does any one know how I can delete keys from tables other than designing the table in object designer and deleting it from there? I know there is a Key virtual table where you can see all the keys of all the tables. But I can't delete keys from there, I am getting permission errors. At the same time I can run Field virtual…
-
SQL Server Profiler - sp_cursorexecute - High read values
Hello, We have a little preformance issue with a navision db. The Navision has a own written querry that shows all historical data of a customer purchases. When we open this querry it can last up to more than a minute before the screen starts to build up. When we look with SQL Profiler to the made reads/writes we see that…
-
Dynamic Cursor trouble (WAS: How-to re-create NAV SQL?)
NAV5SP1 introduces prepared SQL statements to prevent parameter sniffing, but how do I re-create it in a SSMS Query Window? I have used the T-SQL statement from http://blogs.msdn.com/microsoft_dynamic ... story.aspx and found several statements with “room for improvements”. One of the queries seems quite simple: (@P1…
-
SQL 2005 & 2008, same data & SELECT -> Diff. Execution Plan
I’m currently optimizing a solution which is used by different customers on both Native and SQL2005 and SQL2008. My focus so far has only been to ensure that Native and SQL both performed fine, but now I just encountered a difference between SQL2005 and SQL2008. I have two databases with the same data and same statistics…
-
NAV 2009, Indexed Views and SQL Server 2008 Standard
The Edition Comparison chart at Microsoft says that SQL Server 2008 Standard does not support Indexed Views. http://www.microsoft.com/sqlserver/2008 ... mpare.aspx I am running NAV2009 on SQL Server 2008 standard and wondering if this is impacting my performance. The same comparison chart for SQL Server 2005…
-
Why would NAVIGATE cause a block/deadlock
Hi Everyone, Like many of us, we're starting to have record blocking issues, particularly during posting, e.g. "The G/L Entry table is locked by another user...". However, my users are now getting this same message when they click "Navigate" on a posted item. e.g. go to particular vendor ledger entry and click "Navigate".…