Never use WHILE FIND('Which')

asieraiesasieraies Member Posts: 49
edited 2007-04-06 in SQL Performance
I 've read this in a document writted by SqlPerform. Why? What's the problem?




  • MbadMbad Member Posts: 344
    And you would use that where? Doubt it is navision like since we have functions returning sets.
  • fredefrede Member Posts: 80
    As I understand it - you should never use While find('-') because the SQL-server will make a "tablescan" (or at least look for too many record every time the WHILE is executed) when using the WHILE-function together with FIND, making the search toooooooooo slooooooowwww...

    It is just the way Navision C/SIDE "communicates" with SQL optimizer which creates these problems (as I understand it..)

    Henrik Frederiksen, Denmark
  • WaldoWaldo Member Posts: 3,412
    That's what I think.
    You're always going to the database to see if there is a record ... . Try to avoid doing this in a loop.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    The problem is that if you set a couple of filters on a record folowed by a FIND command, SQL will prepare a set of records for you to browse trough. If you, no matter how you do it, change that set in the FIND loop. SQL will have to remake that set for you. This is HELL for your performance.

    WHILE FIND is only a worst case, there are many others.

    Try viewing the webcasts we have done for microsoft on the downloads page. Loads of info's there.
  • krikikriki Member, Moderator Posts: 9,076
    [Topic moved from Upcoming version NAV 5.1 forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.