Sqlalcemy自关联递归查询性能对比

方式一：

    # 根据部门id获取所有子部门id
    def get_child_department_id(self, department_ids, db):
        t1 = time.time()
        child_department_ids = []
        all_department_list = db.query(DepartmentModel).all()
        department_map_pid_to_ids = {}
        for i in all_department_list:
            if i.parent_id not in department_map_pid_to_ids:
                department_map_pid_to_ids[i.parent_id] = []
            department_map_pid_to_ids[i.parent_id].append(i.id)
        def get_child(department_id):
            if department_id not in department_map_pid_to_ids:
                return
            for d in department_map_pid_to_ids[department_id]:
                child_department_ids.append(d)
                get_child(d)

        for i in department_ids:
            get_child(i)
        data = department_ids + child_department_ids
        t2 = time.time()
        print("时间总计---------",t2-t1)
        return data

调用三次的结果如下：

时间总计--------- 0.0019965171813964844
时间总计--------- 0.0010004043579101562
时间总计--------- 0.0009989738464355469

方式二：

    def get_children_department_ids(self, department_id,department_ids, db):
        t1 = time.time()
        department = db.query(DepartmentModel).filter_by(id=department_id).first()
        if not department:
            return department_ids
        department_ids.append(department_id)
        children = db.query(DepartmentModel).filter_by(parent_id=department_id).all()
        for child in children:
            department_ids = UserService.get_children_department_ids(self, child.id, department_ids, db)
        department_ids = [department_id] + department_ids
        t2 = time.time()
        print("时间总计---------",t2-t1)
        return department_ids

调用三次的结果如下：

时间总计--------- 0.012995481491088867
时间总计--------- 0.014801740646362305
时间总计--------- 0.015512943267822266