dynamicsusers: MYTH - SQL Filter Order

[Blog bot]

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.



Подробнее... http://dynamicsuser.net/blogs/mark_brummel...lter-order.aspx