-
Business Analytics: Performance optimation
Hi Just a generic question: Is there a need to tune the BA Database (the one containing the cubes) in order to get faster query results with BA Advanced client? I didn't dig any deeper yet, but I guess there must be a huge potential for example if creating indexes on fields that are used for selection. Any input on this…
-
sql optimization
Hello I've been looking at how navision does findasyoutype on sql. And this is very costly. I was simply searching on name On SQL Navision runs the following statement. SELECT * ,DATALENGTH("Picture") FROM "Test"."dbo"."Pharm$Customer" WHERE (("Name" LIKE '%p%')) ORDER BY "No_" OPTION (FAST 10) This takes 1700 ms and the…
-
Hanging the System while optimizing only Item Ledger Entry
Hi We migrated Navision 3.7 native database to SQL Database. After migrating to SQL Database , While I am trying to optimize only Item Ledger Entry Table with 2935329 records, it has strucked up. It won't even move to one percent. Can anybody have an idea on this Regards ajay
-
Little SQL help is needed...
Hello! One of our clients is running navision on SQL 2005 and its database is too big to reindex it by one night. So I'd like to reindex the hot tables only (G/L entry, value entry table etc). But I didn't manage to configure the T-SQL in maintenance plan setup. I followed instructions from the help, but it didn't help.…
-
Improving Navision SQL performance in terms of disk I/O
Hi there everyone, After upgrading our client from 260 SQL to 370 SQL, and changing the server to RAID 5 the performance dropped dramatically, especially in terms of disk reads. We weren't sure what the problem was and tried in vain to fix it. After a bit of screwing around we discovered that going to File, Database,…
-
SQL index changes in 5.0
Hello I was looking at the beta 5.0 and Navision has changed the sql keys. For example in Item Ledger. They have changed the Navision key Source Type,Source No.,Item No.,Variant Code,Posting Date To sql index Source No.,Item No.,Variant Code,Posting Date,Entry No. Since Navision calls sort by Source Type,Source No.,Item…
-
'NEXT' in 5.0 version
Hi, In version 5.0 with SqlServer2005, has "NEXT" any improvement? Thanks Asier
-
Never use WHILE FIND('Which')
I 've read this in a document writted by SqlPerform. Why? What's the problem? Thanks Asier
-
Webcast on SQL Optimization for Ms Dynamics NAV
Hello anybody attended the webcast? There was one question I had asked but couldn't understand what he said. The question was this. Navision has standard keys on tables. There certain fields that are not used for a given company. For example Variant Code. There are keys with this field in Item ledger. SQL has to maintain…
-
SQL tuning
Hi ... I want to start experimenting with using the SQLIndex property. But first I would like to ask if anyone knew what the results would be if for a key in Navision MaintainSQLIndex would be False but MaintainSIFTIndex would be true?
-
Convergence: C&T with Michael DaVoe: SQL2005 & NAV
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :) Mr DeVoe started off with a small explenation about the new feature in SQL2005: "Included Columns" and how it could "replace" the SIFTs we define in NAV. The "included columns" is an extension of an index, where you can define…
-
Convergence: Indexing For SQL Performance
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :) It is quite hard to summarize this kind of sessions ... But I'll give it a go. The session starts about explaining what are clustered/nonclustered indexes with some nice graphs (may be somehow they'll be put on the download…
-
Convergence: SQL Server and Win. Perf. Counters That Matters
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :) What to start looking at ... . Why should you tune your database? Loss of user productivity Cust of new hardware Harware may only temporarily resolve Understanding load being placed on your server ... Seems logical, doesn't…
-
Convergence: SQL Server: Understanding locking
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :) During this session, the speaker used a lot of diagrams and graphs, which is quite difficult to put into words ... . There are numerous lock types: X: exclusive S: shared IS: intended shared ... Some locks are compatible with…
-
Deleting Key vs. Disable Key in SQL
Performance wise, does it matter?
-
SQL2005 Hangups...
Hi all... I'm experiencing a strange hangups on client site: SQL 2005 NAV 4.0 SP3 one navision is acting normal (and that one is on the same machine as sql server) others are experiencing extreme hangs, kind of => whatever you click in NAV, you wait for a minut or a while for response. Turned-off firewalls and any…
-
New keys
Hi, I would want your opinion. I have created two extra indexes? Why? Because my customer is using NAV and Filters in the most exotic way... The question is in which order you would place the fields... . I know, the most selective, but it's hard to say which are most selective... #-o Index No. 1 on Sales Line table: - No.…
-
Need Tips On SQL DB performance issues
Hi Can anybody give the list of points to increase the performance with Navision SQL DB. 1) Do I need to click the optimize button for each table OR is there nay facility to Optimize all the tables. 2)I am already using the SQL commands like FINDSET,FINDFIRST etc., is there anything i need to optimize my code 3)I got one…
-
sql optimization webcast second part
Ok saw the part. Most of the topics have been discussed in here. So it was a good refersher. One thing that was mentioned in there was the tempbd purpose and that there isn't realy a good reason to put it on separate disk of raid1. That's because navision isn't creating any complex queries such as inner outer joines. So my…
-
Analysis View: s-l-o-w update on SQL Server 2000
One of my clients (running Navision Attain 3.60 on SQL Server 2000) reported extremely slow update of their analysis view after adding a new dimension. Their database contains only some 90,000 G/L Entries but after adding the new dimension code and confirmed deletion of existing analysis views, a renewed update took…
-
Performance Issue on posintg sales and purchase invoice
The problem we face is posting performance issue, once the problem occur all posting will very slow, one invoice with 7 lines might take more than 3 mins to post. We are now resolve the problem by restart DB server then everything back to OK but we face this situation nearly once a week. I would like to know what root…
-
Sift Tables
Hello I was reading this column on persisted columns on sql 2k5. This looks like a solution for sift tables. You could instead of sift table use persisted fields. Anybody ever thought about this? Here is an example CREATE TABLE [dbo].[Invoice_Table]( [Invoice#] [int] IDENTITY(1,1) NOT NULL, [Invoice_Amount] [money] NOT…
-
Huge Cust. Ledger Entry table on SQL
Hi there I'm running af 4.02 SQL runtime (3.60 base), where I'm experiencing a rather big delay when posting payments in a standard journal. The problem is table 21 Cust. Ledger Entry which consists of more than 9 million records. The posting routine is complete standard using the standard defined keys, but it's extremely…