postgresql

@wendal

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

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

只有5000多条数据

我是用的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

```

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

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

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

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

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

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

有11个字段

这个是我自定义的回调。因为有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;