Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 fetchByJoin 为何效率更低呢
发布于 1802天前 作者 Hamming 1117 次浏览 复制 上一个帖子 下一个帖子
标签: 无

fetchByJoin 为何效率更低呢
偶然发现fetchByJoin 这个方法 感觉还不错
于是乎 对比了一下 用时 发现 fetchByJoin效率还不如用fetchLinks
代码如下 角色 关联 菜单

 long temp1 = System.currentTimeMillis();
            Role role = roleService.dao().fetch(Role.class,roleId);
            role = roleService.dao().fetchLinks(role, "menus");
            System.out.println("\n查询1消耗时间:" + (System.currentTimeMillis() - temp1) / 1000.0 + "秒");
            long temp2 = System.currentTimeMillis();
            Role role1 = roleService.dao().fetchByJoin(Role.class,"menus",roleId);
            System.out.println("\n查询2消耗时间:" + (System.currentTimeMillis() - temp2) / 1000.0 + "秒");

耗时如下

[DEBUG] 09:34:32.975 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [4u1tmois1givoob0491jflnd4l]
[DEBUG] 09:34:32.976 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/role/edit/1 : RoleController.edit(RoleController.java:82)
[DEBUG] 09:34:32.976 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'roleController'<class io.nutz.nutzsite.module.sys.controllers.RoleController>
[DEBUG] 09:34:32.977 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    | 1 |
    |---|
    | 1 |
  For example:> "SELECT * FROM sys_role  WHERE id='1'"
[DEBUG] 09:34:33.073 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [4u1tmois1givoob0491jflnd4l]
[DEBUG] 09:34:33.074 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/menu/roleMenuTreeData : MenuController.roleMenuTreeData(MenuController.java:188)
[DEBUG] 09:34:33.074 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'menuController'<class io.nutz.nutzsite.module.sys.controllers.MenuController>
[DEBUG] 09:34:33.075 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:192) - 	 >> Load definition name=menuController
[DEBUG] 09:34:33.075 org.nutz.ioc.loader.combo.ComboIocLoader.printFoundIocBean(ComboIocLoader.java:226) - Found IocObject(menuController) in AnnotationIocLoader(packages=[io.nutz.nutzsite])
[DEBUG] 09:34:33.075 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:223) - 	 >> Make...'menuController'<class io.nutz.nutzsite.module.sys.controllers.MenuController>
[DEBUG] 09:34:33.075 org.nutz.ioc.impl.ScopeContext.save(ScopeContext.java:65) - Save object 'menuController' to [app] 
[DEBUG] 09:34:33.082 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'menuService'<class io.nutz.nutzsite.module.sys.services.MenuService>
[DEBUG] 09:34:33.083 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'userService'<class io.nutz.nutzsite.module.sys.services.UserService>
[DEBUG] 09:34:33.085 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    | 1 |
    |---|
    | 1 |
  For example:> "SELECT * FROM sys_role  WHERE id='1'"
[DEBUG] 09:34:33.087 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='1')

查询1消耗时间:0.013秒
[DEBUG] 09:34:33.101 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id=? LIMIT 0, 1 
    | 1 |
    |---|
    | 1 |
  For example:> "SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id='1' LIMIT 0, 1 "
[DEBUG] 09:34:33.103 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='1')
[DEBUG] 09:34:33.115 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='1')

查询2消耗时间:0.024秒
[DEBUG] 09:34:33.123 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  ORDER BY order_num ASC

调换代码位置 反复测试 依旧效率低

[DEBUG] 09:38:28.244 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [54chbmt49uhfer7q7ku82ujbcc]
[DEBUG] 09:38:28.245 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/role/edit/1 : RoleController.edit(RoleController.java:82)
[DEBUG] 09:38:28.245 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'roleController'<class io.nutz.nutzsite.module.sys.controllers.RoleController>
[DEBUG] 09:38:28.245 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    | 1 |
    |---|
    | 1 |
  For example:> "SELECT * FROM sys_role  WHERE id='1'"
[DEBUG] 09:38:28.320 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [54chbmt49uhfer7q7ku82ujbcc]
[DEBUG] 09:38:28.321 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/menu/roleMenuTreeData : MenuController.roleMenuTreeData(MenuController.java:188)
[DEBUG] 09:38:28.321 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'menuController'<class io.nutz.nutzsite.module.sys.controllers.MenuController>
[DEBUG] 09:38:28.322 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id=? LIMIT 0, 1 
    | 1 |
    |---|
    | 1 |
  For example:> "SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id='1' LIMIT 0, 1 "
[DEBUG] 09:38:28.323 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='1')
[DEBUG] 09:38:28.326 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='1')

查询2消耗时间:0.009秒
[DEBUG] 09:38:28.330 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    | 1 |
    |---|
    | 1 |
  For example:> "SELECT * FROM sys_role  WHERE id='1'"
[DEBUG] 09:38:28.334 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='1')

查询1消耗时间:0.008秒
[DEBUG] 09:38:28.338 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  ORDER BY order_num ASC  
[DEBUG] 09:38:49.333 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [54chbmt49uhfer7q7ku82ujbcc]
[DEBUG] 09:38:49.334 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/role/edit/2 : RoleController.edit(RoleController.java:82)
[DEBUG] 09:38:49.334 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'roleController'<class io.nutz.nutzsite.module.sys.controllers.RoleController>
[DEBUG] 09:38:49.334 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    | 1 |
    |---|
    | 2 |
  For example:> "SELECT * FROM sys_role  WHERE id='2'"
[DEBUG] 09:38:49.404 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [54chbmt49uhfer7q7ku82ujbcc]
[DEBUG] 09:38:49.404 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/menu/roleMenuTreeData : MenuController.roleMenuTreeData(MenuController.java:188)
[DEBUG] 09:38:49.404 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'menuController'<class io.nutz.nutzsite.module.sys.controllers.MenuController>
[DEBUG] 09:38:49.405 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id=? LIMIT 0, 1 
    | 1 |
    |---|
    | 2 |
  For example:> "SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id='2' LIMIT 0, 1 "
[DEBUG] 09:38:49.407 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='2')
[DEBUG] 09:38:49.412 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='2')

查询2消耗时间:0.009秒
[DEBUG] 09:38:49.414 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    | 1 |
    |---|
    | 2 |
  For example:> "SELECT * FROM sys_role  WHERE id='2'"
[DEBUG] 09:38:49.417 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='2')

查询1消耗时间:0.005秒
[DEBUG] 09:38:49.420 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  ORDER BY order_num ASC  
[DEBUG] 09:38:59.098 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [54chbmt49uhfer7q7ku82ujbcc]
[DEBUG] 09:38:59.099 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/role/edit/60e47f5f799c414a8b6f2805a6f5b659 : RoleController.edit(RoleController.java:82)
[DEBUG] 09:38:59.100 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'roleController'<class io.nutz.nutzsite.module.sys.controllers.RoleController>
[DEBUG] 09:38:59.100 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    |                                1 |
    |----------------------------------|
    | 60e47f5f799c414a8b6f2805a6f5b659 |
  For example:> "SELECT * FROM sys_role  WHERE id='60e47f5f799c414a8b6f2805a6f5b659'"
[DEBUG] 09:38:59.173 org.apache.shiro.web.servlet.SimpleCookie.readValue(SimpleCookie.java:389) - Found 'sid' cookie value [54chbmt49uhfer7q7ku82ujbcc]
[DEBUG] 09:38:59.174 org.nutz.mvc.impl.UrlMappingImpl.get(UrlMappingImpl.java:101) - Found mapping for [GET] path=/sys/menu/roleMenuTreeData : MenuController.roleMenuTreeData(MenuController.java:188)
[DEBUG] 09:38:59.174 org.nutz.ioc.impl.NutIoc.get(NutIoc.java:166) - Get 'menuController'<class io.nutz.nutzsite.module.sys.controllers.MenuController>
[DEBUG] 09:38:59.174 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id=? LIMIT 0, 1 
    |                                1 |
    |----------------------------------|
    | 60e47f5f799c414a8b6f2805a6f5b659 |
  For example:> "SELECT sys_role.id as id,sys_role.role_name as role_name,sys_role.role_key as role_key,sys_role.role_sort as role_sort,sys_role.data_scope as data_scope,sys_role.status as status,sys_role.del_flag as del_flag,sys_role.remark as remark,sys_role.create_by as create_by,sys_role.create_time as create_time,sys_role.update_by as update_by,sys_role.update_time as update_time FROM sys_role  WHERE sys_role.id='60e47f5f799c414a8b6f2805a6f5b659' LIMIT 0, 1 "
[DEBUG] 09:38:59.176 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='60e47f5f799c414a8b6f2805a6f5b659')
[DEBUG] 09:38:59.178 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='60e47f5f799c414a8b6f2805a6f5b659')

查询2消耗时间:0.006秒
[DEBUG] 09:38:59.180 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_role  WHERE id=?
    |                                1 |
    |----------------------------------|
    | 60e47f5f799c414a8b6f2805a6f5b659 |
  For example:> "SELECT * FROM sys_role  WHERE id='60e47f5f799c414a8b6f2805a6f5b659'"
[DEBUG] 09:38:59.181 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  WHERE id IN (SELECT menu_id FROM sys_role_menu WHERE role_id='60e47f5f799c414a8b6f2805a6f5b659')

查询1消耗时间:0.003秒
[DEBUG] 09:38:59.183 org.nutz.dao.impl.sql.run.NutDaoExecutor.printSQL(NutDaoExecutor.java:391) - SELECT * FROM sys_menu  ORDER BY order_num ASC
2 回复

因为不走索引和缓存了吧

做了一下 实验
多数据下 queryByJoin 效率高;
数据多的情况下 query 再 fetchLinks 效率 很低

添加回复
请先登陆
回到顶部