I want to share my take on this
I mostly doing the same as you but using Redis as cache, improving permission performance by 100x more or less without too much hassle. I am managing a set of 14.000 territories with 4 different levels and it is working as of now
def generate_territory_cache():
conn = redis.from_url(frappe.local.conf.redis_cache)
prefix = frappe.conf.redis_territory + "_"
##Set a key in order to indicate that the cache has been initialized
conn.set(prefix + "cache", 1)
top = frappe.get_list("Territory", fields=["name"], user="Administrator")
for item in top:
conn.set(prefix + item.name, ";".join(get_all_children(item.name)))
def get_children_from_cache(territory):
##configure cache by site
conn = redis.from_url(frappe.local.conf.redis_cache)
prefix = frappe.conf.redis_territory + "_"
##If the cache is not initialized, we create it
if not conn.exists(prefix + "cache"):
generate_territory_cache()
try:
return conn.get(prefix + territory).decode("utf-8").split(";")
except AttributeError:
generate_territory_cache()
return get_all_children(territory)
def territory_query_conditions(user):
if user == "Administrator":
return ""
else:
territory = frappe.db.get_value("User", user, ["territory"])
##with cache, improved by 100x
child_territories = get_children_from_cache(territory)
##without cache
##child_territories = get_all_children(territory)
return u"""(territory in ({territories}))""".format(
territories='"' + '", "'.join(child_territories) + '"')