问一下.查询慢是什么原因.

badnjcf

菜鸟不懂,问一下.就是普通的查询...控制台有两条,第二条select count(*)怎么这么慢. .


action 里面
select MOD.*,MOH.TRAN_DATE as TRAN_DATE,MOH.FRMUSER as FRMUSER,MOH.TOUSER as TOUSER,MOH.FEE1 as FEE1,MOH.REMARK as HREMARK,MOH.FLOWREMARK1 as FLOWREMARK1,MOH.FLOWREMARK2 as FLOWREMARK2,MOH.SYSUSER as SYSUSER,MOH.SYSDATE as SYSDATE,MOH.FLOWSTATUS as FLOWSTATUS,ORDID.ORD_DESCC as ORD_DESCC,MOH.ORD_ID as ORD_ID,ITEMMSTR.SPEC01 as ITEMSPEC01,ITEMMSTR.SPEC02 as ITEMSPEC02,( SQL.ISNULL(MOD.PACKQTY, 0) - SQL.ISNULL(MOD.PACKQTY_F, 0) ) as PACKQTY_BAL,0.0000 as QTY_SEL,0.0000 as QTY_THIS,MOH.APPR_FLAG as APPR_FLAG,PARAUNIT.S_DESC as UNIT_DESC,MOH.FRMORG_CODE as HFRMORG_CODE,MOH.FRMORG_DESC as HFRMORG_DESC,MOH.TOORG_CODE as HTOORG_CODE,MOH.TOORG_DESC as HTOORG_DESC,PARACURR.S_DESC as CURR_DESC,MOH.FILE_NO as MOHFILE_NO,ITEMTYPE.ITEM_TYPE_DESCC as ITEM_TYPE_DESCC
    from MOD MOD
     optional  join MOH MOH on MOD.HID = MOH
     optional  join ORDID ORDID on MOH.ORD_ID = ORDID.ORD_ID
     optional  join ITEMMSTR ITEMMSTR on MOD.ITEM_NO = ITEMMSTR.ITEM_NO
     optional  join PARA PARAUNIT on MOD.UNIT = PARAUNIT.S AND PARAUNIT.P = '300'
     optional  join PARA PARACURR on MOD.CURR_CODE = PARACURR.S AND PARACURR.P = '200'
     optional  join ITEMTYPE ITEMTYPE on ITEMMSTR.ITEM_TYPE = ITEMTYPE.ITEM_TYPE

下面是js代码
        Model.prototype.button1Click = function(event){
      var M=this.comp("mainData");
      var D=this.comp("data1");
    // alert(D.getValue("FILE_NO"))
      M.setFilter("F1","MOH.TRAN_DATE>= '"+D.getValue("FRMDATE")+"'"+
      " AND (MOH.TRAN_DATE<= '"+D.getValue("TODATE")+"'"+
      " OR '"+D.getValue("TODATE")+"'='')"+
      " AND SQL.ISNULL(MOD.FILE_NO,'') LIKE  '%"+D.getValue("FILE_NO")+"%'"+
      " AND SQL.ISNULL(MOD.RFILE_NO,'') LIKE  '%"+D.getValue("JOB_NO")+"%'"+
      " AND SQL.ISNULL(MOD.ITEM_NO,'') LIKE  '%"+D.getValue("ITEM_NO")+"%'"+
      " AND SQL.ISNULL(MOD.ITEM_DESCC,'') LIKE  '%"+D.getValue("ITEM_DESC")+"%'"+
      " AND SQL.ISNULL(ITEMMSTR.SPEC01,'') LIKE  '%"+D.getValue("SPEC01")+"%'"+
      " AND SQL.ISNULL(MOH.FRMORG_DESC,'') LIKE  '%"+D.getValue("FRMLOC")+"%'"+
       " AND SQL.ISNULL(MOH.TOORG_DESC,'') LIKE  '%"+D.getValue("TOLOC")+"%'" +
       " AND SQL.ISNULL(MOH.ORD_ID,'') LIKE '%"+this.comp("data1").getValue("ORD_ID")+"%'"+
           " AND SQL.ISNULL(MOH.SYSUSER,'') LIKE '%"+this.comp("data1").getValue("SYSUSER")+"%'"
      );
      M.refreshData();

jishuang

sql中加索引进行优化

badnjcf

jishuang 发表于 2021-6-3 18:05
sql中加索引进行优化

还有问题1:就是 js代码去掉setFilter不过滤就会快很多?然后每加一个条件就会时间增加..?为什么
还有问题2:按理,select count(*)不很快...SQL直接去运行1秒差不多就好了.为何这里这么慢.

jishuang

1.加条件就是where条件中加了，同样的sql语句到数据库执行去优化sql啊，这个不是平台处理的，平台只是给你增加了where条件

2.上面输出的sql直接到数据库执行的吗？
java用jdbc的方式用setParamter的方式给参数复制执行看看时间