kriki: Find in NAV : performance

[Blog bot]

We all know that “Find as you type” = true is bad for performance. At least I hope so….



But there is another problem with performance.



A lot of people use “Any part of field” to search a field. So, if you want to search for Customer “Deerfield Graphics Company” (customer No. ‘40000′ in W1), you type “Deerfield” and hit enter (hopefully you already have “Find as you type”=false!), NAV sends a <WHERE Name = “*Deerfield*”> to search for it. Even if you have an index on the field “Name”, SQL has to scan the whole index.



If it would be <WHERE Name = “Deerfield*”>, it would be a lot better because SQL can use an index seek which is a lot faster (and you now “Deerfield” is the beginning part of the name).



So best use “Beginning of field” to search.



But if you want to search “*Graphics*”, you need to change it to “Any part of field”? Very annoying!



Answer: No, you don’t need to do it. Just put an * before the name, and your done. E.g. <*Graphics>



BTW: SQL doesn’t use * as wildcard, but %. But for the sake of making it easier to understand, I used * for both NAV and SQL as wildcard.



Читать дальше