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

kareshi

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

garak

native or SQL?

kareshi

garak wrote:

native or SQL?

That is intresting for both cases: native and sql actually.

kapamarou

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

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

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).

garak

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

kriki

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.