Performance - Coding best practises

poppins
poppins Member Posts: 647
in SQL Performance
Hi everyone,
I have been writing NAV code for a while now but I am aware that in many cases, my code isn't really good when it comes to performance.
I am wondering if there are any articles or books or special hints you can recommend me for coding better performance?
Thanks in advance :)

Comments

  • joshuasung
    joshuasung Member Posts: 37
    Watch out recordset with setcurrentkey and general design (minimize to access database, write is more expensive then read.. use query if possible etc..)
    Joshua H Sung

    Sr. Project Manager
    S-Metric, LLC
    jsung@smetric.com
  • Phenno
    Phenno Member Posts: 630
    Remember to always use proper record loops: findset when you are retreiving set od records, findfirst/findlast when you are retreiving only one, isempty when you are checking only if the record exists. If possible with best key used with setcurrentkey.
  • kine
    kine Member Posts: 12,562
    Phenno wrote: »
    Remember to always use proper record loops: findset when you are retreiving set od records, findfirst/findlast when you are retreiving only one, isempty when you are checking only if the record exists. If possible with best key used with setcurrentkey.

    Depends on which NAV version you are. Using FINDSET for every loop could not be optimal e.g. in NAV 2013+ it is better to use FIND('-') if you are supposing to break the loop before you go through all records etc. You need to understand the behavior behind the command to be able to choose correct one. You can study the blogs about this, attend conferences etc.

    Even the thing about setcurrentkey is not true for a long time already. It was true for Native DB, not true for SQL, but sometime good on some versions even on SQL (when dynamic cursors were used if I am right...).

    Do not forget that this is changing not only by each NAV version, but sometime even with NAV build....
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Phenno
    Phenno Member Posts: 630
    edited 2015-10-28
    null

    Didn't know about find('-') with break in the loop, will check that, but if we want to loop through whole set then findset should be used.

    Regarding setcurrentkey, it only sets ordering of SQL query, so Yes, it should be used with knowledge of particular table and its indexes, and keeping in mind which filters are applied.

Categories