Never use WHILE FIND('Which')

asieraies
asieraies 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?

Thanks

Asier

Comments

  • Mbad
    Mbad Member Posts: 344
    And you would use that where? Doubt it is navision like since we have functions returning sets.
  • frede
    frede 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..)
    Regards,

    Henrik Frederiksen, Denmark
  • Waldo
    Waldo 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_Brummel
    Marije_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.
    Marije Brummel | Freelance Business Central Specialist
  • kriki
    kriki Member, Moderator Posts: 9,121
    [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!

Categories