Keys

Quivelus

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 key instead of creating a new one.

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?


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?

Tx.

Marije_Brummel

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.

kine

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).

stryk

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