MYTH - SQL Filter Order

 

Hi all,

Hynek at SQL Perform asked me to publish something about a myth being spread by some Microsoft specialists.

 

MYTH:

"Make sure that the order of the fields used for filtering is the same as order of these fields in the key."

 

REALITY:

Certainly the order of the filtering does NOT matter, nor in Classic, nor SQL Server. The driver does issue the filters according to the key anyway (because on classic it narrows the index tree...).

 

See an example below:

 

ItemLedgerEntry.SETCURRENTKEY(

  "Item No.",Open,"Variant Code",Positive,"Location Code","Posting Date", "Expiration Date","Lot No.","Serial No.");

ItemLedgerEntry.SETRANGE("Lot No.",'HELLO WORLD');

ItemLedgerEntry.SETRANGE("Posting Date",WORKDATE);

ItemLedgerEntry.SETRANGE("Item No.",'70000');

ItemLedgerEntry.SETRANGE(Open,TRUE);

IF ItemLedgerEntry.FIND('-') THEN;

 

With Client Monitor on (or using SQL Profiler) you would see the SQL Statement is:

 

SELECT  * FROM "CRONUS International Ltd_$Item Ledger Entry" WITH (READUNCOMMITTED)  

WHERE (("Item No_"='70000')) AND (("Open"=1)) AND (("Posting Date"={ts '2007-10-30 00:00:00.000'})) AND (("Lot No_"='HELLO WORLD'))

ORDER BY "Item No_","Open","Variant Code","Positive","Location Code","Posting Date","Expiration Date","Lot No.","Serial No."

 

Where do you see 'HELLO WORLD'? Which was the first filter applied?

 

As you could see the driver issues the filters in the order of the key. Actually it does not matter in what order these are in SQL Server either, but that is another story...

 

The code just looks tidier and nicer if you do it as they suggest, perhaps a really high price for everybody to go through all the "offending" code and correct.

Comment List
  • Question: is this true on a SQL server where IndexHinting has been turned off?

    I think I have seen a lot better performance when putting the last filter statement on the field that is first in the key. For instance, posted sales invoice line. If I do:

    setrange("line no.", 10000);

    setrange("document no.", 'XX');

    I seem to get better performance then if I put the line no. filter last.

    I have tested it several times, and it always seem to work. Now of course, it could be that the second time I run it, I take advantage of the caching that the server does, so maybe it was just an illusion :)

    Thanks

    Daniel

  • Very interesting.  Of course, it would help if MS didn't suggest this very behavior in the Tuning Navision for Better Performance doc, revision 5.  In it, they say when using SETCURRENTKEY:

    "You must set the filters following the order of the fields in the current key (holes must not be left in the key)"

  • I also always give the advice to put the filters in the order of the key, but ONLY because it is nicer to see and to review the code. (and I always add the because...)

    But it seems that most people forget the part after the "because"...

Related
Recommended