New Find functions

ta5

Hi

I have read topic http://www.mibuso.com/forum/viewtopic.php?t=11112

Is the conclusion from this to use the new functions where appropriate in all new projects, regardless of DB used in production system?

For example a quote from help on topic Findfirst:

This function should be used instead of FIND('-') when you only need the first record.

Any input on this will be appreciated.
Thomas

nunomaia

FINDFIRST it's optimized for performance in SQL Server.
In native database I think has the same performance has FIND('-')

The problem is to develop an add-on and you want to convert to 3.70, these functions won't work in that version.

When I made Navision customizations where the client supports the new functions, I always use it, even if the customer is using native DB, because the customer might some day to upgrade to SQL.

DenSter

Yes that would be my advice. I now use those commands wherever they apply, regardless of the database type. I don't worry about backward compatibility when I write the code, that is something I'll think about when it comes to taking development to a previous solution.

Marije_Brummel

The way I understand it is that FINDFIRST only returns the top row when using SQL.

If you want to do a repeat/until you can better do a FIND('-') because you'll need al the data anyway.

I only use FINDFIRST and FINDLAST when I don;t need all the records.

Or have I misunderstood?

ta5

IMHO find('-') can be replaced be findset if a loop on the reocrds is required.

Marije_Brummel

It should be tested I think

Cust.FIND('-')

SELECT  *,DATALENGTH("Picture") FROM "CRONUS$Customer" WITH (READUNCOMMITTED)   ORDER BY "No_" OPTION (FAST 5)

Cust.FINDSET

SELECT TOP 500 *,DATALENGTH("Picture") FROM "CRONUS$Customer" WITH (READUNCOMMITTED)   ORDER BY "No_" 

Cust.FINDSET(TRUE, FALSE);

SELECT  *,DATALENGTH("Picture") FROM "CRONUS$Customer" WITH (UPDLOCK)   ORDER BY "No_"

ta5

SELECT TOP 500 *,DATALENGTH("Picture") FROM "CRONUS$Customer" WITH (READUNCOMMITTED) ORDER BY "No_"

Findset:

Does this mean one have to keep in mind whether >500 Records are to be retrieved or not?

Marije_Brummel

That is an interersting statement.

What type of transaction will read more than 500 records without being a report?

if you define a report on the customer table the SQL statement is

SELECT  *,DATALENGTH("Picture") FROM "CRONUS$Customer" WITH (READUNCOMMITTED)   ORDER BY "No_" OPTION (FAST 5)

Batch processing? These are performance nightmares anyways.

These new functions are implemented to speedup posting transactions I think.

kine

In generally:

  MyRec.SETRANGE...
  <another filtering>
  If MyRec.FIND('-') then begin   // or FIND('+')
     <do something with the found record>
  end;

Should be replaced with

  MyRec.SETRANGE...
  <another filtering>
  If MyRec.FINDFIRST then begin   // or FINDLAST
     <do something with the found record>
  end;

  MyRec.SETRANGE...
  <another filtering>
  If MyRec.FIND('-') then 
  repeat
     <do something with the record>
  until MyRec.NEXT = 0;

Should be replaced with

  MyRec.SETRANGE...
  <another filtering>
  If MyRec.FINDSET(a,b) then 
  repeat
     <do something with the record>
  until MyRec.NEXT = 0;

a and b depends on what you are doing in the loop with the record (just reading, changing, changing field which is part of filter or key...)

The limit how much records are read through FINDSET can be changed in database settings - you cannot assume if it is 500 or just 10... :-)

Marije_Brummel

Is 500 a recomended setting? Has anyone experimented with this?

I still cannot see any logic in it.

Has anybody succesfully replaced FIND('-') by FINDSET and gained performance?

Marije_Brummel

From TechEd presentation:

Code Optimizations – FINDSET (4.00 SP1)

Syntax: [Ok :=] FINDSET([ForUpdate] [, UpdateKey])

ForUpdate – Set to FALSE if you do not intend to modify records. Set to TRUE if you intend to modify records. If TRUE, then a Locktable is performed prior to records being read.

UpdateKey – ForUpdate must be TRUE, set if you intend to update the current key

Optimize loops  explicit code
Reuse of cursors

Database property ‘Record Set’. Sets the amount of records retrieved in the default record set

Note: Dataset can only be retrieved in Ascending Order

FINDSET(FALSE,FALSE) Readonly
Firehose, No server Cursor used, Record Set is cached (No Fetch), Less Server round-trips

FINDSET(TRUE,FALSE) Update Non-Key Field
Dynamic Cursor

FINDSET(TRUE,TRUE) Update key field
Fetch, fetch (single row)

ta5

I guess that 500 is a good default value and if the loop will exeed this number more records are loaded into the recordset.

kine

ta5 wrote:

I guess that 500 is a good default value and if the loop will exeed this number more records are loaded into the recordset.

If the limit is reached, next 500 is loaded automatically when you reach end of the first set... :-)

Marije_Brummel

From the Resource kit:

Retrieving large sets

When you use FINDSET without any parameters it functions as a firehose and fetches
around 500 records. If you want to retrieve more than 500 records, you can still use
the FIND('-') function. Alternatively, you can use FINDSET(True).
If you need less than 500 records, use LOCKTABLE and then FINDSET.

In summary, there are three simple rules that you should follow when using FIND
statements:

· If you are writing a NEXT statement, always use FINDSET.
· Always tell the server when you want to update some records. Call LOCKTABLE first
and then use FINDSET to modifying the records.
· Your code should match the size of the record set that you want to retrieve.

Miklos_Hollender

I think backwards combatability (:-) ) is not really an issue, it chan be search-and-replaced when it's needed to. Not necessarily by hand, something like a Perl one-liner will do it automatically: perl.exe -e 's/FINDFIRST/FIND('-')/g while(<>)' infile.txt > outfile.txt

DenSter

The way I remember is to just not use FIND('-') or FIND('+') anymore, unless I know that I am getting more than 500 records, which is never

3 rules:
If you want to loop, use FINDSET. This returns a set of records.

If you need to have one record, use FINDFIRST or FINDLAST. This returns one record only.

If you need to know whether records exist in your filter, use ISEMPTY. This returns a bit (Yes/No) and is all you really need to know if there are records in the filter.

rocatis

I'm currently reviewing some code in which the following is used:

IF NOT ISEMPTY THEN BEGIN
  FINDSET;
  REPEAT
     <something>
  UNTIL NEXT = 0;
END;

as opposed to

IF FINDSET THEN
  REPEAT
     <something>
  UNTIL NEXT = 0;

I can see why that could potentially increase performance, but on the other hand I can also see how it could potentially decrease it...

I suppose the right solution depends on how often you could expect the (filtered) table to be empty, but does anybody have a non-guessing approach to this?

Waldo

This is what I would do (probably colin will disagree )

Just use the FINDSET.

Using the ISEMPTY will trigger 2 database calls:
- SELECT TOP NULL ...
- SELECT TOP 500 * FROM ...

When using only the FINDSET, it triggers 1 database call. I agree, it's a heavier one as the ISEMPTY, but when it returns no records, it doesn't really matter.

Just make sure your indexes are ok.

DenSter

I agree, it's not necessary to make the 2 calls. just use IF FINDSET. If there's no records, that will simply return false, which is what ISEMPTY does as well.

GoMaD

What is the most performant way to see if there are more than one lines in a recordset (using findfirst, findlast or findset) without using the recordvariable.count?

Belias

one question: a findset(true,true) is used when you're looping and modifying any field you want in the record, but...will you have problems modifying fields that are used in some filter?
I mean...I still have to use 2 variables and modifying the one I am not looping through or I can "do whatever I want without worries?"

Belias

why not count?
by the way you can

if myrec.findfirst then begin
  if next = 0 then
    message('1 record')
  else
    message('more records');
end else
  message('0 records');

it all depends on what is your purpose

rocatis

Belias wrote:

one question: a findset(true,true) is used when you're looping and modifying any field you want in the record, but...will you have problems modifying fields that are used in some filter?

The new find functions behave in exactly the same manner as the old ones in this regard. The parameters for FINDSET are only used for performance optimization on the SQL Server.

From a NAV standpoint the parameters are irrelevant - the code will work either way.

Waldo

Hi colleague .

I also ask myself why not to use COUNT.

Now, I doubt in what is the best resolution. the best way to test this is to create multiple functions and screen it with the client monitor. It only takes a few minutes.

dmccrae

If you need to know if there is more than 1 record in a filtered set then COUNT is a sensible choice (if there is no filter and the table is not locked, you will get an approximate count based on table statistcs).

Do not write code such as:

IF Rec.FINDFIRST THEN
   IF Rec.NEXT = 0 THEN

NEXT should not follow FINDFIRST because FINDFIRST does not establish a set operation against SQL Server - it is intended for retrieval of the first record only. The NEXT will then need to create the set which may be expensive because of the WHERE clause it will need to generate. Always use FINDSET or FIND('-') with NEXT.

Note:
From 5.0 onwards you can detect where there is code executing like this by setting the value of the [diagnostics] field in the [$ndo$dbproperty] table of the database:
Set to value 16384 - writes a SQL Error parameter to the Client Monitor if running,
Set to value 81920 - generates an ERROR, aborting execution.

Message: "Detected a NEXT function call on a record without a matching FIND/FINDSET call."

Belias

NEXT should not follow FINDFIRST because FINDFIRST does not establish a set operation against SQL Server - it is intended for retrieval of the first record only. The NEXT will then need to create the set which may be expensive because of the WHERE clause it will need to generate. Always use FINDSET or FIND('-') with NEXT.

Sorry! #-o
It's what the topic is about... :oops:

Waldo

dmccrae wrote:

Note:
From 5.0 onwards you can detect where there is code executing like this by setting the value of the [diagnostics] field in the [$ndo$dbproperty] table of the database:
Set to value 16384 - writes a SQL Error parameter to the Client Monitor if running,
Set to value 81920 - generates an ERROR, aborting execution.

Message: "Detected a NEXT function call on a record without a matching FIND/FINDSET call."

Is there some documentation available about functions like this?? This is very useful.

DenSter

Yes what Waldo said

Waldo

May be a suggestion: make the field available in the "Alter Database" option with a drop down list or whatever?

dmccrae

The values for the [diagnostics] field are not documented - they are intended either for short term internal use, or for features such as this that might later make it into the UI.

Waldo

OK, too bad.

Anyway, I will try this out.

thanks for the info!