perf: Perform db.set_value with single query only

[ankush]

Concludes #15560 by making all set_value calls to non-single doctypes run with 1 query only.

Explanation:

Previously we were fetching all the primary keys which need to be updated and then updating separately.

Before:

names = frappe.db.get_values(... filters)
frappe.qb.update(fields).where(table.name.isin(names))

After:

frappe.qb.update(fields).where(filters)

QB can already do single update query without much fuss. The last reason for keeping this appears to be for update which locks the rows before updating them.

Why remove for_update completely?

This was added to make sure that N+1 queries that happen don't cause inconsistencies during the time queries are ran. Now that set_value is 1-1 translated to single UPDATE query there's no point in locking rows before updating them. UPDATE locks rows automatically.

Before:
1.02 ms ± 166 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)

After:
530 µs ± 144 µs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)

Roughly 2x faster. This will be much better on queries that end up affecting more than 1 row.

TODO:

• better cache eviction when dn is not a filter
• evict only doctype (deferred, needs design change in cache keys)

ERPNext CI: https://github.com/frappe/erpnext/actions/runs/3197465411 ✅

[ankush]

...will delay backport by a week or so.

[codecov]

Codecov Report

Merging #18305 (6436e67) into develop (6ed3ce5) will decrease coverage by 0.17%.
The diff coverage is 57.14%.

Additional details and impacted files

@@             Coverage Diff             @@
##           develop   #18305      +/-   ##
===========================================
- Coverage    62.72%   62.55%   -0.18%     
===========================================
  Files          746      746              
  Lines        67307    67709     +402     
  Branches      5966     5966              
===========================================
+ Hits         42218    42354     +136     
- Misses       21587    21853     +266     
  Partials      3502     3502              

Flag	Coverage Δ
server-mariadb	66.78% <44.44%> (-0.06%)	⬇️
server-postgres	66.78% <44.44%> (-0.08%)	⬇️

Flags with carried forward coverage won't be shown. Click here to find out more.