[SQL Time]sql 执行时间

ssoqyhm

查询15条记录用时有点长,与表的记录总数有关联,目前是1500条左右;如果只有200左右很快



2014-07-23 19:54:07 [SQL Time]sql 执行时间：1280ms, exec KSQL: select StorageItemStore.enterpriseNo as enterpriseNo,StorageItemStore.enterpriseName as enterpriseName,StorageItemStore.storeObject as storeObject,StorageItemStore.storageNo as storageNo,StorageItemStore.storageName as storageName,StorageItemStore.storageType as storageType,StorageItemStore.itemNo as itemNo,StorageItemStore.itemDescribe as itemDescribe,StorageItemStore.rationSize as rationSize,StorageItemStore.unitCode as unitCode,StorageItemStore.unitName as unitName,(sum(StorageItemStore.pieceQuantityL)) as pieceQuantityL,(sum(StorageItemStore.quantityL)) as quantityL,(min(StorageItemStore.dateFirstIn)) as dateFirstIn,(min(StorageItemStore.dateFirstOut)) as dateFirstOut,(max(StorageItemStore.dateLastIn)) as dateLastIn,(max(StorageItemStore.dateLastOut)) as dateLastOut,(sum(StorageItemStore.itemAmountI)) as itemAmountI,(sum(StorageItemStore.itemAmountO)) as itemAmountO,(sum(StorageItemStore.pieceI)) as pieceI,(sum(StorageItemStore.pieceO)) as pieceO,StorageItemStore.storageItemType as storageItemType from StorageItemStore StorageItemStore  where StorageItemStore.quantityL <> 0 and (StorageItemStore.storageType = '1' OR StorageItemStore.storageType = '2')   GROUP BY    StorageItemStore.enterpriseNo, StorageItemStore.enterpriseName,
StorageItemStore.storeObject, StorageItemStore.storageNo,
StorageItemStore.storageName, StorageItemStore.storageType,
StorageItemStore.itemNo, StorageItemStore.itemDescribe,
StorageItemStore.rationSize, StorageItemStore.unitCode,
StorageItemStore.unitName,StorageItemStore.storageItemType ORDER BY storageNo ASC limit 0,15
  --> sql:SELECT  StorageItemStore.ENTERPRISENO AS enterpriseNo, StorageItemStore.ENTERPRISENAME AS enterpriseName, StorageItemStore.STOREOBJECT AS storeObject, StorageItemStore.STORAGENO AS storageNo, StorageItemStore.STORAGENAME AS storageName, StorageItemStore.STORAGETYPE AS storageType, StorageItemStore.ITEMNO AS itemNo, StorageItemStore.ITEMDESCRIBE AS itemDescribe, StorageItemStore.RATIONSIZE AS rationSize, StorageItemStore.UNITCODE AS unitCode, StorageItemStore.UNITNAME AS unitName, SUM(StorageItemStore.PIECEQUANTITYL) AS pieceQuantityL, SUM(StorageItemStore.QUANTITYL) AS quantityL, MIN(StorageItemStore.DATEFIRSTIN) AS dateFirstIn, MIN(StorageItemStore.DATEFIRSTOUT) AS dateFirstOut, MAX(StorageItemStore.DATELASTIN) AS dateLastIn, MAX(StorageItemStore.DATELASTOUT) AS dateLastOut, SUM(StorageItemStore.ITEMAMOUNTI) AS itemAmountI, SUM(StorageItemStore.ITEMAMOUNTO) AS itemAmountO, SUM(StorageItemStore.PIECEI) AS pieceI, SUM(StorageItemStore.PIECEO) AS pieceO, StorageItemStore.STORAGEITEMTYPE AS storageItemType FROM StorageItemStore StorageItemStore  WHERE  (StorageItemStore.QUANTITYL <> ?) AND ( (StorageItemStore.STORAGETYPE = ?) OR (StorageItemStore.STORAGETYPE = ?) )    GROUP BY StorageItemStore.ENTERPRISENO, StorageItemStore.ENTERPRISENAME, StorageItemStore.STOREOBJECT, StorageItemStore.STORAGENO, StorageItemStore.STORAGENAME, StorageItemStore.STORAGETYPE, StorageItemStore.ITEMNO, StorageItemStore.ITEMDESCRIBE, StorageItemStore.RATIONSIZE, StorageItemStore.UNITCODE, StorageItemStore.UNITNAME, StorageItemStore.STORAGEITEMTYPE  ORDER BY storageNo ASC
--> binds:[0, 1, 2]

fpj

表中有大字段吗，如果有大字段的查询 与没有大字段的查询速度肯定不一样。

你可以将你的SQL或者 KSQL进行优化一下。

ssoqyhm

fpj 发表于 2014-7-23 20:26
表中有大字段吗，如果有大字段的查询 与没有大字段的查询速度肯定不一样。

你可以将你的SQL或者 KSQL进行 ...

主要是我每次只看15条， 有limint 好像不起作用，记录数只有15条出来 速度好像查了全部

fpj

ssoqyhm 发表于 2014-7-23 20:37
主要是我每次只看15条， 有limint 好像不起作用，记录数只有15条出来 速度好像查了全部 ...

你的这个SQL查询结果是用来干什么，只是用来展示用吗？
你可以这样试试，把你的复杂的SQL转化为视图，在平台中写个action，在action中写SQL，查询你的视图，然后是返回数据试试，看看速度有没有变化。

Tomato_2014

把你的sql语句，放在数据库中执行一遍，看看慢不慢？
慢，无非就两个办法，要么优化表，比如，增加索引，按时间分区等等；要么优化sql语句，
我看你的SQL语句中有or，你去掉or换成union试试，是否会快点？

ssoqyhm

Tomato_2014 发表于 2014-7-23 23:08
把你的sql语句，放在数据库中执行一遍，看看慢不慢？
慢，无非就两个办法，要么优化表，比如，增加索引，按 ...

数据库中执行非常快，就瞬间的事情

zhaixin

使用的是sql server数据库吗，如果是这个现象就是正常的，平台使用光标取15条数据，而不是通过在sql中写top 15这样的方式来实现。sql server在处理光标时比较慢

ssoqyhm

zhaixin 发表于 2014-7-24 13:42
使用的是sql server数据库吗，如果是这个现象就是正常的，平台使用光标取15条数据，而不是通过在sql中写top ...

那有没什么好办法

ssoqyhm

ssoqyhm 发表于 2014-7-25 11:56
那有没什么好办法

只能按条件去过滤吗

zhaixin

尽量使得查询的结果集记录少，特殊情况下，可以通过重写queryAction，写sql来实现分页