NutzCN Logo
问答 较长的sql执行很慢,使用jdbc和springboot测试类比较快。
发布于 329天前 作者 xianghongwu 602 次浏览 复制 上一个帖子 下一个帖子
标签:

我使用的是springboot和nutz整合+postgresql。
用postman调用接口执行查询sql(比较长)。查询数据库的时间需要花费6秒左右。我跟了下源码。就是执行开启事务那个地方卡了很久。

然后我用springboot测试类复制接口中的方法执行。只用2秒左右。

27 回复

postgresql 的话默认都会开事务

可以关掉这个特性, 在MainSetup.init内,调用

((NutDao)dao).setAutoTransLevel(0);

只需要调用一次.

设置了还是那样。我就重新跟了下源码原来不是事务的问题。还在里面一层。再执行回调赋值的时候很慢。
具体就在org.nutz.dao.pager.ResultSetLooping类下面的doLoop方法里面有一个while循环很慢。

但是用postman调用接口的方式执行查询需要6秒,我用测试类测试一样的代码只要2秒。这是为什么呀?
有么有可以优化的地方
@wendal

只有5000多条数据

入口方法把结果序列化成json了?

我是用的springboot和nutz整合,没有使用nutzboot。没有您说的入口方法设置。我只是在application.yml文件中配置了关于nutz的如下配置
nutz:
json:
auto-unicode: false
quote-name: true
ignore-null: true
null-as-emtry: true
enabled: true
mode: compact
dao:
runtime:
create: true
migration: false
basepackage:
- com.zgtech.kernel.model.mapped
sqlmanager:
paths:
- sqls

```

执行dao.xxx语句的前后打印一下时间就好了嘛, 就能算出实际耗时

doLoop是读取结果集的, 你测试的其他实现没有读取结果?

都读取了结果集的。
我把回调注释sql.setCallback(Sqls.callback.entities());就快了很多。

我就想问问。对于5000条数据量的返回读取结果集要执行5秒左右正常不。能不能优化下。

差不多了吧, 新建对象, 逐个赋值

如果是jdk8以上, 启用

NutConf.USE_FASTCLASS = true;

应该会快一些

NutConf.USE_FASTCLASS = true;这个配置在哪里呀

随便, 找个地方执行就行

例如系统启动的时候

nutz查询返回的数据多了。效率真的不高。读取结果集那一步需要的时间比较久(6s)。
我用同样的sql和条件。使用jdbc或者mybatis查询速度都很快(1s)。
@wendal

哦,可以试试自定义回调

可以了也。使用自定义回调完美解决。

谢谢。不过这个就不能从源码优化下

字段很多?测试过并不慢

把测试代码和对比代码(包括bean)贴一下?

这个是我自定义的回调。因为有3个sql都需要用。所以判断了下是哪个sql调用。

public static <T> List<T> getSqlListNoPagerEduStorageCount(Class<T> clazz, Dao dao, String sqlStr, Cnd cnd,EduStorageCountType type) {
        Sql sql = dao.sqls().create(sqlStr);
        if(cnd!=null){
            sql.setCondition(cnd);
        }
        sql.setCallback(new SqlCallback() {
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                List<StorageCollect> list = new LinkedList<StorageCollect>();
                StorageCollect storageCollect=null;
                while (rs.next()){
                    storageCollect=new StorageCollect();
                    storageCollect.setSchoolId(rs.getString("schoolId"));
                    storageCollect.setFoodName(rs.getString("foodName"));
                    switch (type){
                        case IN:
                            storageCollect.setInStorageMoney(rs.getDouble("inStorageMoney"));
                            storageCollect.setInStorageNumber(rs.getDouble("inStorageNumber"));
                            break;
                        case OUT:
                            storageCollect.setOutStorageMoney(rs.getDouble("outStorageMoney"));
                            storageCollect.setOutStorageNumber(rs.getDouble("outStorageNumber"));
                            break;
                        case OLD:
                            storageCollect.setEarlyMonthNumber(rs.getDouble("earlyMonthNumber"));
                            storageCollect.setEarlyMonthMoney(rs.getDouble("earlyMonthMoney"));
                            break;
                    }
                    list.add(storageCollect);
                }
                return list;
            }
        });
        dao.execute(sql);
        List<T> lists = sql.getList(clazz);
        return null != lists && lists.size() > 0 ? lists : null;
    }

pojo类:

/**
     * 食材名称
     */
    private String foodName;
    /**
     * 学校id
     */
    private String schoolId;

    /**
     * 初期库存数量
     */
    private Double earlyMonthNumber;

    /**
     * 初期库存金额
     */
    private Double earlyMonthMoney;

    /**
     * 本月入库数量
     */
    private Double inStorageNumber;

    /**
     * 本月入库金额
     */
    private Double inStorageMoney;

    /**
     * 本月出库数量
     */
    private Double outStorageNumber;

    /**
     * 本月出库金额
     */
    private Double outStorageMoney;

    /**
     * 期末库存数量
     */
    private Double endStorageNumber;

    /**
     * 期末库存金额
     */
    private Double endStorageMoney;

第一条sql:


select food_name foodName,nfs.school_id schoolId,sum(money) inStorageMoney,sum(storage_number) inStorageNumber from nm_food_storage nfs left join nm_gfood_sfood ngs on nfs.food_id=ngs.school_food_id and nfs.order_id=ngs.order_id left join nm_distributor_food_order ndfo on ngs.distributor_food_order_id=ndfo.id WHERE nfs.school_id IN ('5b7e058d0e667d5ba4bdfb86','5b7e058d0e667d5ba4bdfb87','5b7e058d0e667d5ba4bdfba8','5b7e058d0e667d5ba4bdfba9','5b7e058d0e667d5ba4bdfb88','5b7e058d0e667d5ba4bdfb89','5b7e058d0e667d5ba4bdfb8a','5b7e058d0e667d5ba4bdfb8b','5b7e058d0e667d5ba4bdfb8c','5afe26a5dab8c30d403b509a','5b7e058d0e667d5ba4bdfb7d','5b7e058d0e667d5ba4bdfb7f','5b7e058d0e667d5ba4bdfb80','5b7e058d0e667d5ba4bdfb81','5b7e058d0e667d5ba4bdfb82','5b7e058d0e667d5ba4bdfb83','5b7e058d0e667d5ba4bdfb84','5b7e058d0e667d5ba4bdfb85','5b7e058d0e667d5ba4bdfb8f','5b7e058d0e667d5ba4bdfb90','5b7e058d0e667d5ba4bdfb91','5b7e058d0e667d5ba4bdfb92','5b7e058d0e667d5ba4bdfb93','5b7e058d0e667d5ba4bdfb95','5b7e058d0e667d5ba4bdfb96','5b7e058d0e667d5ba4bdfb97','5b7e058d0e667d5ba4bdfb98','5b7e058d0e667d5ba4bdfb99','5b7e058d0e667d5ba4bdfb9a','5b7e058d0e667d5ba4bdfb9b','5b7e058d0e667d5ba4bdfb9c','5b7e058d0e667d5ba4bdfb9d','5b7e058d0e667d5ba4bdfb9f','5b7e058d0e667d5ba4bdfba1','5b7e058d0e667d5ba4bdfba2','5b7e058d0e667d5ba4bdfba4','5b7e058d0e667d5ba4bdfba5','5b7e058d0e667d5ba4bdfba6','5b7e058d0e667d5ba4bdfba7','5b7e058d0e667d5ba4bdfb94','5b7e058d0e667d5ba4bdfb8d','5b7e058d0e667d5ba4bdfb7e','5b7e058d0e667d5ba4bdfb8e','5b7e058d0e667d5ba4bdfb9e','5b7e058d0e667d5ba4bdfba3','5b7e058d0e667d5ba4bdfba0','626576400253456384','635592502300119040','622587161996169216','622587161996169217','622587161996169218','5b7e058d0e667d5ba4bdfb7c') AND nfs.year=2019 AND nfs.month=10 and nfs.storage_type=1 group by nfs.school_id,ndfo.food_name;

第二条sql:

select food_name foodName,nfs.school_id schoolId,sum(money) outStorageMoney,sum(storage_number) outStorageNumber from  nm_food_storage nfs left join nm_gfood_sfood ngs on nfs.food_id=ngs.school_food_id and nfs.order_id=ngs.order_id left join nm_distributor_food_order ndfo on ngs.distributor_food_order_id=ndfo.id
 WHERE nfs.school_id IN ('5b7e058d0e667d5ba4bdfb86','5b7e058d0e667d5ba4bdfb87','5b7e058d0e667d5ba4bdfba8','5b7e058d0e667d5ba4bdfba9','5b7e058d0e667d5ba4bdfb88','5b7e058d0e667d5ba4bdfb89','5b7e058d0e667d5ba4bdfb8a','5b7e058d0e667d5ba4bdfb8b','5b7e058d0e667d5ba4bdfb8c','5afe26a5dab8c30d403b509a','5b7e058d0e667d5ba4bdfb7d','5b7e058d0e667d5ba4bdfb7f','5b7e058d0e667d5ba4bdfb80','5b7e058d0e667d5ba4bdfb81','5b7e058d0e667d5ba4bdfb82','5b7e058d0e667d5ba4bdfb83','5b7e058d0e667d5ba4bdfb84','5b7e058d0e667d5ba4bdfb85','5b7e058d0e667d5ba4bdfb8f','5b7e058d0e667d5ba4bdfb90','5b7e058d0e667d5ba4bdfb91','5b7e058d0e667d5ba4bdfb92','5b7e058d0e667d5ba4bdfb93','5b7e058d0e667d5ba4bdfb95','5b7e058d0e667d5ba4bdfb96','5b7e058d0e667d5ba4bdfb97','5b7e058d0e667d5ba4bdfb98','5b7e058d0e667d5ba4bdfb99','5b7e058d0e667d5ba4bdfb9a','5b7e058d0e667d5ba4bdfb9b','5b7e058d0e667d5ba4bdfb9c','5b7e058d0e667d5ba4bdfb9d','5b7e058d0e667d5ba4bdfb9f','5b7e058d0e667d5ba4bdfba1','5b7e058d0e667d5ba4bdfba2','5b7e058d0e667d5ba4bdfba4','5b7e058d0e667d5ba4bdfba5','5b7e058d0e667d5ba4bdfba6','5b7e058d0e667d5ba4bdfba7','5b7e058d0e667d5ba4bdfb94','5b7e058d0e667d5ba4bdfb8d','5b7e058d0e667d5ba4bdfb7e','5b7e058d0e667d5ba4bdfb8e','5b7e058d0e667d5ba4bdfb9e','5b7e058d0e667d5ba4bdfba3','5b7e058d0e667d5ba4bdfba0','626576400253456384','635592502300119040','622587161996169216','622587161996169217','622587161996169218','5b7e058d0e667d5ba4bdfb7c') AND nfs.year=2019 AND nfs.month=10 and nfs.storage_type=2
group by nfs.school_id,ndfo.food_name;

第三天sql:


select instorage.foodName,(instorage.inStorageMoney-COALESCE(outstorage.outStorageMoney, 0))earlyMonthMoney,(instorage.inStorageNumber-COALESCE(outstorage.outStorageNumber,0))earlyMonthNumber,instorage.schoolId from (select food_name foodName,nfs.school_id schoolId,sum(money) inStorageMoney,sum(storage_number) inStorageNumber from nm_food_storage nfs left join nm_gfood_sfood ngs on nfs.food_id=ngs.school_food_id and nfs.order_id=ngs.order_id left join nm_distributor_food_order ndfo on ngs.distributor_food_order_id=ndfo.id WHERE nfs.school_id IN ('5b7e058d0e667d5ba4bdfb86','5b7e058d0e667d5ba4bdfb87','5b7e058d0e667d5ba4bdfba8','5b7e058d0e667d5ba4bdfba9','5b7e058d0e667d5ba4bdfb88','5b7e058d0e667d5ba4bdfb89','5b7e058d0e667d5ba4bdfb8a','5b7e058d0e667d5ba4bdfb8b','5b7e058d0e667d5ba4bdfb8c','5afe26a5dab8c30d403b509a','5b7e058d0e667d5ba4bdfb7d','5b7e058d0e667d5ba4bdfb7f','5b7e058d0e667d5ba4bdfb80','5b7e058d0e667d5ba4bdfb81','5b7e058d0e667d5ba4bdfb82','5b7e058d0e667d5ba4bdfb83','5b7e058d0e667d5ba4bdfb84','5b7e058d0e667d5ba4bdfb85','5b7e058d0e667d5ba4bdfb8f','5b7e058d0e667d5ba4bdfb90','5b7e058d0e667d5ba4bdfb91','5b7e058d0e667d5ba4bdfb92','5b7e058d0e667d5ba4bdfb93','5b7e058d0e667d5ba4bdfb95','5b7e058d0e667d5ba4bdfb96','5b7e058d0e667d5ba4bdfb97','5b7e058d0e667d5ba4bdfb98','5b7e058d0e667d5ba4bdfb99','5b7e058d0e667d5ba4bdfb9a','5b7e058d0e667d5ba4bdfb9b','5b7e058d0e667d5ba4bdfb9c','5b7e058d0e667d5ba4bdfb9d','5b7e058d0e667d5ba4bdfb9f','5b7e058d0e667d5ba4bdfba1','5b7e058d0e667d5ba4bdfba2','5b7e058d0e667d5ba4bdfba4','5b7e058d0e667d5ba4bdfba5','5b7e058d0e667d5ba4bdfba6','5b7e058d0e667d5ba4bdfba7','5b7e058d0e667d5ba4bdfb94','5b7e058d0e667d5ba4bdfb8d','5b7e058d0e667d5ba4bdfb7e','5b7e058d0e667d5ba4bdfb8e','5b7e058d0e667d5ba4bdfb9e','5b7e058d0e667d5ba4bdfba3','5b7e058d0e667d5ba4bdfba0','626576400253456384','635592502300119040','622587161996169216','622587161996169217','622587161996169218','5b7e058d0e667d5ba4bdfb7c') AND nfs.input_time < '2019-10-01' and nfs.storage_type=1 group by nfs.school_id,ndfo.food_name) instorage left join (select food_name foodName,nfs.school_id schoolId,sum(money) outStorageMoney,sum(storage_number) outStorageNumber from nm_food_storage nfs left join nm_gfood_sfood ngs on nfs.food_id=ngs.school_food_id and nfs.order_id=ngs.order_id left join nm_distributor_food_order ndfo on ngs.distributor_food_order_id=ndfo.id WHERE nfs.school_id IN ('5b7e058d0e667d5ba4bdfb86','5b7e058d0e667d5ba4bdfb87','5b7e058d0e667d5ba4bdfba8','5b7e058d0e667d5ba4bdfba9','5b7e058d0e667d5ba4bdfb88','5b7e058d0e667d5ba4bdfb89','5b7e058d0e667d5ba4bdfb8a','5b7e058d0e667d5ba4bdfb8b','5b7e058d0e667d5ba4bdfb8c','5afe26a5dab8c30d403b509a','5b7e058d0e667d5ba4bdfb7d','5b7e058d0e667d5ba4bdfb7f','5b7e058d0e667d5ba4bdfb80','5b7e058d0e667d5ba4bdfb81','5b7e058d0e667d5ba4bdfb82','5b7e058d0e667d5ba4bdfb83','5b7e058d0e667d5ba4bdfb84','5b7e058d0e667d5ba4bdfb85','5b7e058d0e667d5ba4bdfb8f','5b7e058d0e667d5ba4bdfb90','5b7e058d0e667d5ba4bdfb91','5b7e058d0e667d5ba4bdfb92','5b7e058d0e667d5ba4bdfb93','5b7e058d0e667d5ba4bdfb95','5b7e058d0e667d5ba4bdfb96','5b7e058d0e667d5ba4bdfb97','5b7e058d0e667d5ba4bdfb98','5b7e058d0e667d5ba4bdfb99','5b7e058d0e667d5ba4bdfb9a','5b7e058d0e667d5ba4bdfb9b','5b7e058d0e667d5ba4bdfb9c','5b7e058d0e667d5ba4bdfb9d','5b7e058d0e667d5ba4bdfb9f','5b7e058d0e667d5ba4bdfba1','5b7e058d0e667d5ba4bdfba2','5b7e058d0e667d5ba4bdfba4','5b7e058d0e667d5ba4bdfba5','5b7e058d0e667d5ba4bdfba6','5b7e058d0e667d5ba4bdfba7','5b7e058d0e667d5ba4bdfb94','5b7e058d0e667d5ba4bdfb8d','5b7e058d0e667d5ba4bdfb7e','5b7e058d0e667d5ba4bdfb8e','5b7e058d0e667d5ba4bdfb9e','5b7e058d0e667d5ba4bdfba3','5b7e058d0e667d5ba4bdfba0','626576400253456384','635592502300119040','622587161996169216','622587161996169217','622587161996169218','5b7e058d0e667d5ba4bdfb7c') AND nfs.input_time < '2019-10-01' and nfs.storage_type=2 group by nfs.school_id,ndfo.food_name) outstorage on instorage.foodName=outstorage.foodName and instorage.schoolId=outstorage.schoolId;

看不出为啥慢,待我写点测试跑一跑

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