Let’s say we have a table (called table1) with follwing fileds FieldNo Name Class Length 1 company code 10 2 Number Integer 2 UserName Code 10 3 Date Date and we have a primary Key company,Number and a secondary key company, UserName, Date Now lets Say we have 1.000.000 records in that table, where each field in the table is filled with an value like this company = any company Number = any number Username = any username Date = any date Wich means we have no record, where the fileds username and date are not filled with an value! When changing the key to the secondary key and doing follwing setrange and setfilter: setrange(company, company1); setfilter(username, <>’’) setrange(date, 0D); The system begins to search the whole table for the specified values, which also will be shown in the navison status bar. Why does it take so long for navison (maybe for other sytems also) to do search on table, where the searched values are not availible ?? Cause when haveing a table like this FieldNo Name Class Length 1 company code 10 2 Number Integer 2 UserName Code 10 3 Date Date 4 not_filled boolean and keys like this 1) company,number 2) company,not_filled where whe have no recored where the value from the field not_filled is TRUE. and doing setrange and setfilter like this setrange(company, company1); setrange(not_filled, true); then the whole search will take only some seconds. For me the conclusion is, NOT TO USE: setrange or setfilter on fields, wich are not filled (Why a boolean in this case is faster, is not realy clear for me) even when the fileds are in the active key. BTW our system is Navision 2.01b We now have to change here so many objects and routines, to increase the setrange/setfilter performance. Can any form Navision grurs here say/write something about this ??? Would be great to some thoughts
It depends how the 1Mio records are divided to Customers: * Are there 10 customers with 100K records each? * Or 100K customers with 10 records each? In the first case the DB can only reduce the number of records to scan (Setrange(company, ‘Microsoft’) to all entries of ‘Microsoft’ and therefore has to scan 100’000 records as the condition <>’’ is true for all 100’000
Hello Fabian!
quote:
It depends how the 1Mio records are divided to Customers: * Are there 10 customers with 100K records each? * Or 100K customers with 10 records each?
Your example is clear. What i don’t understand is, even if we have the worst case, my second example with the boolean is x-times QUICKER then the first one. WHY ??
Hy Ufuk i think the problem is, that your code Setfilter(Customer,’<>%1’,’’) has no continuos result (first all Customers < ‘’ and then all Customers > ‘’). I think that Setfilter(Customer,’>%1’,’’) will be as fast as your filter using the boolean field. Bye(Stefan);
Hello Fabian ! I made a BIG mistake The example i provided
quote:
When changing the key to the secondary key and doing follwing setrange and setfilter: setrange(company, company1); setfilter(username, <>’’) setrange(date, 0D);
is worng (sorry for that) the right code example is setrange(username, ‘’) As i wrote, performance problem occours, when using setrange/setfilter on unfilled fileds(where an boolean is every time filled NO/YES).