Never use WHILE FIND('Which')

Comments

• Mbad Member Posts: 344

  2007-03-16

  And you would use that where? Doubt it is navision like since we have functions returning sets.

  0
• frede Member Posts: 80

  2007-03-16

  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

  0
• Waldo Member Posts: 3,412

  2007-03-16

  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
  

  0
• Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2007-03-18

  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

  0
• kriki Member, Moderator Posts: 9,121

  2007-04-06

  [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!
  
  

  0