Never use WHILE FIND('Which')

asieraies

I 've read this in a document writted by SqlPerform. Why? What's the problem?

Thanks

Asier

Mbad

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

frede

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..)

Waldo

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.

Marije_Brummel

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.

kriki

[Topic moved from Upcoming version NAV 5.1 forum to SQL Performance forum]