SetRange and SetFilter comparision

kenl

Hello,

In term of performance, is there any different between using setRange and SetFilter?

I am not sure, but I guess SetRange would be a bit faster, since setRange is more straight forward to set the range (from .. to). For SetFilter, system may need to convert the complicated filter value.

Any idea? :roll:

DenSter

I believe that SETRANGE is nothing more than a user friendly way of doing SETFILTER. Navision actually 'translates' it internally. As far as user performance I think if there's any difference, it's bound to be tiny.

Luc_VanDyck

I always presumed that SETRANGE is faster than SETFILTER.

kenl

Thank you both of you.

Personally, if SetRange is suffient for the case, I would use SetRange rather than SetFilter.

I don't find any NA document that talks about the performance between SetRange and SetFilter. Really don't know any difference for the performance impact . :?:

kriki

Depends on the key you use.
If the field is NOT in the key, it doesn't matter.
If the field is in the key, it is better to use SETRANGE, because with this you are sure that Navision can set a start and end to the records to scan. If not Navision has to scan all records.

Ex. Key with fields A,B,C:

If you set a SETRANGE on all 3 fields, it will be very fast in finding the records you want.
If you set a SETFILTER on field C, it depends how many records Navision has to check after applying SETRANGE on fields A and B. If only some hundreds of records remain, it is very fast.
If you set a SETFILTER on field A, and Navision cannot translate it to a SETRANGE, Navision probably (not always I noted) has to scan the WHOLE table. And this can be SLOW.

Apart performance issues, it is always better to use SETRANGE. If you read the code, you can read/understand it faster ( :-k actually this point is also about performance) than a SETFILTER.

DenSter

I really don't think it makes any difference if you use SETFILTER or SETRANGE. SETRANGE is nothing more than a fancy SETFILTER, where you can use actual datatypes instead of formatting values into a string. Internally, a SETRANGE(MyDate,Date1,Date2) will be translated internally into a SETFILTER(MyDate,'%1..%2',FORMAT(Date1),FORMAT(Date2)). The first one is just easier to program, especially if your system uses a certain dateformat for instance.

Come to think of it, it even makes sense to me that SETFILTER is faster than SETRANGE, since the system doesn't have to translate. But a simple concatenation command shouldn't make much of a difference.

As for the readability of the code.... if you don't understand a SETFILTER command, you shouldn't even be looking at the code. That's all I am going to say about that .

Has anybody here ever done a benchmark for it?

Angelo

Just for more information:
In Navision Developement Exam there is a question about SETRANGE and SETFILTER. Which is faster? So, I think there is different speed between both of them. If there is no difference, why thw question appear on the exam by microsoft? :-k

Angelo
Angel's Home

jreynolds

Create a codeunit with the following code:

Customer.RESET;
Customer.SETRANGE("No.",'10000','20000');
Customer.FIND('-');

Customer.RESET;
Customer.SETFILTER("No.",'%1..%2','10000','20000');
Customer.FIND('-');

Turn client monitor on and run the codeunit, then look at the client monitor to see what happens. You will see no entries at all for either SETRANGE or SETFILTER as neither of these functions make any calls to the database. But you will see entries for each of the FIND's and you can see that they produce identical results and that the SETRANGE and SETFILTER functions have generated exactly the same filter for each FIND to use.

So I think that the answer to the question is that since SETRANGE and SETFILTER by themselves do not cause and calls to the database, who cares which is faster. All either is doing are setting the filter that FIND uses so I would imagine that both are about the same as far as performance is concerned.

kriki

So I think that the answer to the question is that since SETRANGE and SETFILTER by themselves do not cause and calls to the database, who cares which is faster

RESET,SETCURRENTKEY,SETRANGE,SETFILTER don't generate calls to the DB, but influences how FIND,NEXT generate calls to the DB.

Some years ago, I created a very big table (more than 1 million records) with an index and tried out SETRANGE and SETFILTER and I found that SETRANGE is faster. (Of course I remembered to close the DB and reopen it to clean the DB-buffer between the tests).

An even better reason : Navision uses SETFILTER only if the command cannot be translated in a SETRANGE (eg. SETFILTER(Quantity,'<>0'); ).

PS. On SQL I don't think it matters (didn't test it), because it is SQL who interprets the WHERE generated by Navision.