Nav.-Performance / How Nav. works together with SQL-Server

Comments

• Options

  kriki Member, Moderator Posts: 9,090

  2005-08-27

  - In the Exterprise Manager: You can see all the tables there, that are also in Navision. But what are the tables that consist only of number e.g. Mandatory$50021$0 for ???

  SQL has no SIFT, so something had to be invented to emulate it. Each of these tables is an index in that table where the sums of the SIFT-fields are stored for each level.
  See also http://www.mibuso.com/forum/viewtopic.php?t=7235.
  

  - In the table "Object" I try to filter (F7) the CAPTION (because I come from germany) and search for a specific table. It's tooooo slow to wait for the result. I tried to create an index on CAPTION in SQL-Server, but there is no such column ?! From where does Navision take the CAPTION ? And how can I make it search faster ?
  - When I open a table (in Navision) I can't even move up or down. But why ? In a normal application with a db server if you opened a query and filled a DB-grid it's a "screenshot" of the tabledata of the moment when you opened the query. So you can move around in the first 200 datasets easily. How is it in Navision ? Because it takes me so long to filter the table "Object" (with only 20.000 entries) I guess it must have something to do with the german translation (CAPTION), because it needs to be caught from another table ?

  This field is a flowfield. So the DB has to read all "Object"-records, then calculate the flowfield for each record (another read on the DB), and then Navision can decide to show a certain record or not. To make it faster : filter on Name, not on caption.
  

  - If I create an index in SQL-Server, what happens in Navision?
  Will the SQL-Server use this index? Is it the same as if I declare a Nav.-Index SQLMaintain=No ?

  ALWAYS create indexes in Navision, NEVER directly in SQL!!!
  Index SQLMaintain=No means Navision can use the index (like in SETCURRENTKEY) but in SQL the index is not created. The statement is translated in an "ORDER BY" with all the fields in the index + the fields of the primary key.
  See also http://www.mibuso.com/forum/viewtopic.php?t=7257.
  

  - Navision often executes a Stored Procedure sp_execute. What is it for ?

  I am not sure, but I think this procedure contains commands to translate Navision-functions to maintain the DB into SQL.
  

  - When Navision displays the message that it searches in a table. What is executed ? What can I do (in general) to speed up a progress like this?

  This means Navision is scanning records to search the records it needs (because of filters). To speed it up, you need to select a good Index (eg. if in T32:"Item ledger Entry", you have a filter on "Item No.", DON'T USE THE PRIMARY KEY, but use a key starting with "Item No.").
  
  In general:
  Search the forum for "SQL" to have more info.

  Regards,Alain Krikilion
  No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
  
  

  0
• Options

  bbrown Member Posts: 3,268

  2005-08-27

  How is the conversion going?
  
  Some comments/advice:
  
  Navision often executes a Stored Procedure sp_execute. What is it for ?
  
  sp_execute is an SQL System Stored procedure. It is used to execute transact-sql statements. The Navision SQL client translates the C/side code into transact-sql and calls sp_execute to run them.
  
  Navision appends the primary key fields to any secondary index created. If you create the index (Name) on the customer table, the actual index created will be (Name, No.). This applies to both databases.
  
  The primary indexes are created in SQL as clustered and the secondary indexes are non-clustered. With a clustered index, new index entries are inserted in their proper location. With a non-clustered index, new entries are just added to the end. Over time, as records are inserted, the non-clustered index becomes less efficient. This is why regular index rebuilding is important.
  
  The SETCURRENTKEY statement does not insure SQL will select the records in that order. It is only the order in which the client will use/display the records. SQL will retrieve its records in what it determines as the most efficient order regardless of the "order by" clause.

  There are no bugs - only undocumented features.

  0
• Options

  recall Member Posts: 36

  2005-08-27

  But what is the difference between sp_execute and sp_executsql ?
  Because sp_execute is often executed with some Handels as parameters ?*confused*
  
  And how do I change an Index of a table in navision ?
  
  Conversion is going very easy. You have to use migrate.fob FIRST, then export and import again in SQL-Server. Especially under Nav 4.0 we had no problems! Under 3.x there were a lot of mistakes and error-dialogs
  
  Thanks.

  0
• Options

  bbrown Member Posts: 3,268

  2005-08-27

  The system stored procedures sp_execute and sp_executesql provide similar function. SP_executesql is used when working directly in SQL, such as query analyzer or writing stored procedures. SP_execute supportts the prepare/execute model of executing SQL statements in ADO, OLDDB, and ODBC.
  
  And how do I change an Index of a table in navision ?
  
  Not sure what you are asking? You maintain indexes with the table designer. But I don't think that is what you are asking. Why would you want to change an index. I would be careful about changing a standard Navision index without first understanding where and how it is being used and what impact the change will have. I can probably answer this better with more information on what you are trying to accomplish.

  There are no bugs - only undocumented features.

  0