Does anybody know the difference between FIND(’-’) AND (FIND(’=<>’). for example: Salesorder.setrange(orderdate, workdate); IF FIND(’-’) THEN … Salesorder.setrange(orderdate, workdate); IF FIND(’=<>’) THEN … We are working with financials 2.60G and SQL - database.
Quite simply, FIND(’-’) finds the first record within the filter criteria. Find(’=<>’) attempts to find the record that matches the primary key values in the record variable. Should that fail, a record whose primary key values precede the primary key values is searched. In case that fails as well, the system will try to find the first record whose primary key values are higher than the current values. Or… Did you mean to ask about the SQL-specific differences between these two statements?
Dear Jan, thank for your answer. You’re right, I want to know the specific differences between these statements on SQL-Server. We use this code in a time-critical environment in stock - controlling.
Hello, We asked Navision: Example: Variable: record1 - record (table with large number of records) code: record1.RESET; record1.SETCURRENTKEY(field1, field2,field3,field4,field5); record1.SETRANGE(field1,range1); record1.SETRANGE(field2,range2); record1.SETRANGE(field3,range3); record1.SETRANGE(field4,range4); IF NOT record1.FIND(’-’) THEN BEGIN //any other functionality END; Question: What will be faster in this example in native/SQL server : if we will use record1.FIND(’-’) or record1.FIND(’=<>’); or may be better use the statement record1.ISEMPTY.- The answer was: 2003.10.29 TS Answer from Denmark. ISEMPTY will run a SELECT TOP 1 * FROM… Since 3.70 we do not do a TOP 50 with the first FIND(-) but only if it is part of a NEXT loop. I have no calculations or measurements of the fastest method. My explanation. In previous version when you use Find(’-’) it will tries to read first 50 records and without key it can take more time then by using find(’=’). Starting from 3.70 it will not be a big difference.-
Dear Viktoras, thanks for your answer. We tested now FIND(’-’) FIND(’=<>’) AND IF Record.ISEMPTY = FALSE with 30000 records on Navision 2.60G and SQL-Server. e.g. FOR i:= 1 TO 30000 DO BEGIN vendor ledger entries.RESET; vendor ledger entries.SETFILTER(Description,’%1’,‘Telekom*’); vendor ledger entries.SETRANGE(“Document type”, “vendor ledger entries”.“Document type”::Payment); IF vendor ledger entries.ISEMPTY = FALSE THEN;// (different code)IF FIND (’-’) / (’=<>’) END; If we use FIND(’-’) in the code, time for this code is about 1 minute, 58 seconds If we use FIND(’=<>’) in the code, time for this code is about 1 minute, 23 seconds If we use ISEMPTY = FALSE in the code, time for this code is about 0 minute, 48 seconds So we will prefer ISEMPTY the next time.
In this order of preference, of course depending on what you actually need. 1. ISEMPTY() - Does a SELECT TOP 1 NULL FROM table WHERE filter (cached in subsequent calls) 2. FIND(’=’) or GET, 3. FIND(’-’) - Differing strategies depending on results of previous FINDs. (also cached) 4. Do not use FIND(’=<>’) if you can avoid it! Causes many cursors to be used when the ‘=’ doesn’t match(also, never cached) Regarding FIND(’-’), and ‘+’, there is a recent fix to retrieve a single record only for the first FIND for a particular key and filter. Thereafter, a buffered record set is used if it turns out that a NEXT is performed (the number of records read depends on the table width - it is not hardcoded to 50). You will see this behavour in the Client Monitor. Prior to this it was the reverse situation - buffering was done by default, then if no NEXT was encountered future FINDs would use a single row. This change was primarily done to prevent cursor row locks from being taken unnecessarily by the buffered set, when perhaps only the first row is required. It was therefore a concurrency optimization, not a performance optimization. Was done for 3.70, and in a 3.60 hotfix - I think 13. By the way, I think its very encouraging that you are taking the trouble to find out these things and test them.