Keys

Comments

• Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2007-09-28

  Try to find the SQL Perform webcasts on the download pages.
  
  These will give you a good idea into how this works.
  
  It is completely different from Native and way to complex to explain in just one short reply.
  
  Please let me know if any questions are there after viewing the webcast.

  Marije Brummel | Freelance Business Central Specialist

  0
• kine Member Posts: 12,562

  2007-09-29

  Main part is: SETCURRENTKEY in NAV is just sort order for MS SQL. (but the "just" doesn't mean that it will not have impact to execution plan).

  Kamil Sacek
  MVP - Dynamics NAV
  My BLOG
  NAVERTICA a.s.

  0
• stryk Member Posts: 645

  2007-09-29

  Quivelus wrote:

  SETCURRENTKEY("Field1","Field2","Field3");SETRANGE("Field1",value1);SETRANGE("Field2");  // Does this line matter?SETRANGE("Field3",value3);The question is wether the line with range on Field2 does matter?

  No, it doesn't. With the next FIND statement the query is sent and all "active" filters are combined to one WHERE clause, it should look like this:

  SELECT * FROM MyRecord WHERE (Field1 = Value1) AND (Field3 = Value3) ORDER BY Field1,Field2,Field3
  

  Quivelus wrote:

  2) Supose you have a key defined consisting of Field1, Field2 and Field3 (in that order!). With this key you define a SQLIndex (and you maintain an SQLIndex) Field3, Field2 and Field1 (in this order). In code it is probably best to write SETCURRENTKEY("Field1","Field2","Field3");SETRANGE("Field1",value1);SETRANGE("Field2",value2);SETRANGE("Field3",value3);But is this automatically converted to the correct SQLIndex? Because the first field to 'filter' on there is Field3. How is this done by Navision?

  First: there is nothing "converted". The problem is the general understanding of keys and indexes in SQL, which is totally different than with "native". The SETCURRENTKEY defines Index and Sorting in "native"; as this is an ISAM database it is crucial to filter in the sequence of these key/index fields.With SQL the SETCURRENTKEY only defines only the ORDER BY clause of a statement. The Index is picked by the SQL Server itself! (Except if you use some stupid Index Hinting, but that's a different story.)When choosing the right Index (= "an internal B-Tree structure to find records") SQL Server primarily regards the WHERE clause, secondly the sorting (ORDER BY).In your example the statement would look like this:

  SELECT * FROM MyRecord WHERE (Field1 = Value1) AND (Field2 = Value2) AND (Field3 = Value3) ORDER BY Field1,Field2,Field3
  

  The Index is built like this: Field3, Field2, Field1So, now trouble COULD arise: the index includes all fields from the WHERE clause which is good. But the sorting is quite the opposite way; thus SQL Server has to spent some work to re-sort the resultset after retrieving the records with the Index. Now it depends on the other available indexes and their selectivity - especially the Clustered Index - if SQL Server indeed will pick this Index, or another one - or starts scanning the full table (very bad).The higher the difference between Index and Sorting, the higher the probaility to cause Index Scans. That's why Index Hinting is stupid: You force SQL Server to use an Index which may not be the best one which could result in Index Scanning. Scanning takes longer than Seeking and brings higher potentials for blocking conflicts ...Hope this could help a little.Best regards,Jörg

  Jörg A. Stryk (MVP - Dynamics NAV)
  NAV/SQL Performance Optimization & Troubleshooting
  STRYK System Improvement
  The Blog - The Book - The Tool

  0