Use Findset, ... vs find('-')...in C/SIDE DB

sblot · 2006-04-13

Is anybody else who use this news function in C/SIDE DB?

In the help file I can see : This function is designed to optimize finding and updating sets. If you set any or both of the parameters to FALSE, you can still modify the records in the set but these updates will not be performed optimally.

May in C/SIDE the DB engine makes a Snapshop and it's better than find('-')?
I know this functions are design for SQL but may it's good in C/SIDE?

DenSter · 2006-04-13

Those commands were introduced to enhance performance on SQL Server, and to have more specific ways to access data on SQL Server, but it should work in C/SIDE as well.

kriki · 2006-04-13

With the Navision-DB, I think those new commands (FINDSET,FINDFIRST,FINDLAST) is just translated in it's old-style FIND-statement, so no problem. And like Denster said, they are created for SQL.

Joe_Little · 2006-04-13

I am also curious to know the answer to this. Returning record sets from SQL rather than processing records one at a time has some potential benefits for performance. There seems to be support in the database options (record set) on the SQL version (and not as far as I can see in the proprietary version) so is likely that this is just translated using the proprietary back end to normal record by record grabs, but I don't know if that is the case.

DenSter · 2006-04-14

They were introduced specifically to solve SQL Server performance issues. The old FIND commands were not specific enough as far as the intent of the command (am I going to just read, am I going to write, do I just want to see if records exist, do I really want to get the first record, do I only want the first record or am I going to loop through, etc.), so they created these command so developers can specify the intent of their query.

Because they were created specifically for SQL Server I doubt that MS did any real performance enhancement with these commands on C/SIDE, but you can never tell. Maybe they just mapped the new commands to the old ones for C/SIDE, but they could just as well have done some internal tweaking.

ara3n · 2006-04-14

Sadly they haven't changed old functions to the new function in existing code. I don't know if in sp2 they used them. Maybe v 5?

kriki · 2006-04-14

ara3n wrote:

Sadly they haven't changed old functions to the new function in existing code. I don't know if in sp2 they used them. Maybe v 5?

Because of your post, I just checked SP2, not expecting the new commands. But I found some of them in C12:"Gen. Jnl.-Post Line". :shock:
But not in C80,C90,C22.

So I suppose they only used the new codes in case they had to fix something.

Phenno · 2006-04-14

are this functions work ok with native database?

If I have addon or somehting like that, I don't want to make 2 different copies of it only cause of these three commands...

kriki · 2006-04-14

Phenno wrote:

are this functions work ok with native database?

If I have addon or somehting like that, I don't want to make 2 different copies of it only cause of these three commands...

I didn't test it, but the fact they put them in means they should work fine with the native DB.
I don't think you have to worry about this. If this wouldn't work, they would have noticed it directly => Navision is full of "FIND(...)".

Phenno · 2006-04-14

kriki wrote:

I didn't test it, but the fact they put them in means they should work fine with the native DB.
I don't think you have to worry about this. If this wouldn't work, they would have noticed it directly => Navision is full of "FIND(...)".

hm... I'll sure test it before deployment because of possible speed problems in native db. Maybe these funcs are faster on sql, but they could be slower on native...

thaug · 2006-04-17

I've used them in my test database which is native without any problems, but of course I can't detect any speed differences.

I think the case with CU 80 is that they need to do a complete rewrite, which I wish they would do sooner rather than later.

kine · 2006-04-23

The new functions has difference in how they are using the cache. If you use FIND() for iterating through some set of records, it takes 3 loops for Navision start using the cache for .NEXT. If you use FINDFIRST etc. the cache is used since first loop and Navision will use firehose for reading the record. If you use FINDSET, Navision will read whole set of records (based on the option in File-Database-Alter) - and this can be BIG performance gain.

More about these new functions is somewhere in webcast about Navision 4.00SP1. May be that the webcast is somewhere in the download section... (there were examples of the related SQL commands etc.)

wochm · 2006-04-27

FIND statement is equal to SELECT *. For example this code:

Table.LOCKTABLE;
IF Table.Find('+') THEN
LastEntryNo := Table."Entry No.";

will result full table scan under SQL.

FINDLAST and FINDFIRST are equal to SELECT TOP statement which will reduce number of deadlocks.

Regards,
Marcin Woch

Dakkon · 2006-06-06

I just tried to use findfirst and findlast in the sample 4.0 demo database I had lying around. I get the whole "You have specified an unknown variable FINDFIRST". Does the native client not allow use of these new commands? If I understand correctly, some of you have just said that it does. Any ideas?
Thanks in advance.
PS My work is getting ready to upgrade to 4.0 with sql which is the reason I'm trying to test this stuff. I don't actually want to use the Find* functions inside native.

DenSter · 2006-06-06

on 4.0 SP1 and higher. Those commands were not yet available on regular 4.0

Dakkon · 2006-06-06

Ahh, this explains alot thank you so very much.

Doomhammer · 2006-06-15

i tried all three new funcs in Nav 4 SP2 on native database without problems, they worked fine for me. but big drawback is, that these statements are not implemented in regular Nav 4.0

kine · 2006-06-15

Why drawback? 4.00 without SP is unusable because too much problems. I hope that there is no customer using plain 4.00.

Use Findset, ... vs find('-')...in C/SIDE DB

Comments

Categories