SETFILTER/SETRANGE vs manual loop (on large data set)

kareshi
kareshi Member Posts: 9
edited 2008-12-23 in NAV/Navision Classic Client
Does anybody here have expirience with real DB (big one) with altering data in multi access case?

The question is:

Target: need to modify some set of records in 'big' table (Value Entry for example).
Fields to filter is decimal and there is no keys that contain these fields.
What approach will be faster and safer? :
1 - SetFilter on this field: Setfilter("My Field",'> X') then findset then modify (or modifyall?)
2 - loop for all records in table, if one is maches then modify.

On db with single user the 1st one is greatly faster.
But what about db with multi access case?
Does anybody here provide experiments with real life patients?

PS Locktable is used

Comments

  • garak
    garak Member Posts: 3,263
    native or SQL?
    Do you make it right, it works too!
  • kareshi
    kareshi Member Posts: 9
    garak wrote:
    native or SQL?

    That is intresting for both cases: native and sql actually.
  • kapamarou
    kapamarou Member Posts: 1,152
    If this is the only field you filter then I think the Filter option is better.

    If you can filter with other fields down to a small set of records and loop those records looking for just a couple of them then you could use the loop approach. I think it depends on the size of the resulting filtered set...
  • kareshi
    kareshi Member Posts: 9
    kapamarou wrote:
    If this is the only field you filter then I think the Filter option is better.

    If you can filter with other fields down to a small set of records and loop those records looking for just a couple of them then you could use the loop approach. I think it depends on the size of the resulting filtered set...


    Resulting set is undefined. It can be small or all records in table.

    //Actually in my case there is 2 such fields. And only them i can use to make selection.

    My concern is about db locks actually.
  • kriki
    kriki Member, Moderator Posts: 9,121
    I would use the MODIFYALL-solution with the correct SETRANGE-SETFILTER.

    A way to avoid locking others is to make your updates as fast as possible. The MODIFYALL is the best solution for it.

    In case you know that the resultset takes most of the records, I would think about looping the recordset, putting the record I want to change in another buffer and modifying that. After about 3 seconds, I would do a COMMIT. Of course if you use COMMIT's you need to create your code so that in can continue after the last COMMIT if there is an error (this is not always so easy to program).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • garak
    garak Member Posts: 3,263
    only one comment to that was Alain said.

    If you make a "big" updatetransaction with blocking other users (because u update many recs and there processes must wait -> Lockqueue) and u use the commit in your statement (for this u must know what you do, so that you don't create inconsistence datas) , you can also use after it a sleep to give other processes the chance to reduce the queue.

    i saw someware also a BLOG about these "modify" theme. Maybe it was Waldos Blog or Krikis Blog.

    Regards
    Do you make it right, it works too!
  • kriki
    kriki Member, Moderator Posts: 9,121
    I created a how-to or a tips&tricks on that, but it is only for the native DB.
    I didn't find yet a good trick for the SQL-DB.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories