查询效率低

just_j++

目前我是sql2008，现做了一下压力测试，grid分页栏显示如下：
当前显示1-20条，共524288条
进入页面时加载前20条数据，但查询用了二十几秒，日志如下：
exec KSQL: SELECT PG_Tracking.*,Base_DropDown.Name as t_status,Base_Client.ChineseName as t_ClientID,Base_ClientPoint.Name as t_StartPointID,Base_ClientPoint_1.Name as t_EndPointID,Base_Organization.Name as t_StartOrgID,Base_Organization_2.Name as t_EndOrgID,Base_DropDown_3.Name as t_ProductTypeID,Base_DropDown_4.Name as t_ReturnBillTypeID,Base_Area.Name as t_StartProvinceID,Base_Area_5.Name as t_StartCityID,Base_Area_6.Name as t_StartCountyID,Base_Area_7.Name as t_StartVillageID,Base_Area_8.Name as t_EndProvinceID,Base_Area_9.Name as t_EndCityID,Base_Area_10.Name as t_EndCountyID,Base_Area_11.Name as t_EndVillageID,Base_DropDown_12.Name as t_PaymentType,Base_ClientPoint_14.MobilePhone as t_startMobilePhone,Base_ClientPoint_15.Name as t_startName,Base_ClientPoint_16.Name as t_endName,Base_ClientPoint_17.MobilePhone as t_endMobilePhone,Base_ClientPoint_18.Address as t_startAddress,Base_ClientPoint_19.Address as t_endAddress,Base_DropDown_20.Name as t_BalanceTypeID,Base_DropDown_21.Name as t_ControlGoodsTypeID  FROM PG_Tracking PG_Tracking  optional  join Base_DropDown Base_DropDown on PG_Tracking.TruckingStatus = Base_DropDown optional  join Base_Client Base_Client on PG_Tracking.Base_ClientID = Base_Client optional  join Base_ClientPoint Base_ClientPoint on PG_Tracking.StartPointID = Base_ClientPoint optional  join Base_ClientPoint Base_ClientPoint_1 on PG_Tracking.EndPointID = Base_ClientPoint_1 optional  join Base_Organization Base_Organization on PG_Tracking.StartOrgID = Base_Organization optional  join Base_Organization Base_Organization_2 on PG_Tracking.EndOrgID = Base_Organization_2 optional  join Base_DropDown Base_DropDown_3 on PG_Tracking.ProductTypeID = Base_DropDown_3 optional  join Base_DropDown Base_DropDown_4 on PG_Tracking.ReturnBillTypeID = Base_DropDown_4 optional  join Base_Area Base_Area on PG_Tracking.StartProvinceID = Base_Area optional  join Base_Area Base_Area_5 on PG_Tracking.StartCityID = Base_Area_5 optional  join Base_Area Base_Area_6 on PG_Tracking.StartCountyID = Base_Area_6 optional  join Base_Area Base_Area_7 on PG_Tracking.StartVillageID = Base_Area_7 optional  join Base_Area Base_Area_8 on PG_Tracking.EndProvinceID = Base_Area_8 optional  join Base_Area Base_Area_9 on PG_Tracking.EndCityID = Base_Area_9 optional  join Base_Area Base_Area_10 on PG_Tracking.EndCountyID = Base_Area_10 optional  join Base_Area Base_Area_11 on PG_Tracking.EndVillageID = Base_Area_11 optional  join Base_DropDown Base_DropDown_12 on PG_Tracking.PaymentType = Base_DropDown_12 optional  join Base_ClientPoint Base_ClientPoint_14 on PG_Tracking.StartPointID = Base_ClientPoint_14 optional  join Base_ClientPoint Base_ClientPoint_15 on PG_Tracking.StartPointID = Base_ClientPoint_15 optional  join Base_ClientPoint Base_ClientPoint_16 on PG_Tracking.EndPointID = Base_ClientPoint_16 optional  join Base_ClientPoint Base_ClientPoint_17 on PG_Tracking.EndPointID = Base_ClientPoint_17 optional  join Base_ClientPoint Base_ClientPoint_18 on PG_Tracking.StartPointID = Base_ClientPoint_18 optional  join Base_ClientPoint Base_ClientPoint_19 on PG_Tracking.EndPointID = Base_ClientPoint_19 optional  join Base_DropDown Base_DropDown_20 on PG_Tracking.BalanceTypeID = Base_DropDown_20 optional  join Base_DropDown Base_DropDown_21 on PG_Tracking.ControlGoodsTypeID = Base_DropDown_21 WHERE (( PG_Tracking.Valid = 1 )) LIMIT 0, 20
  --> sql:SELECT  PG_Tracking.BALANCETYPEID AS BalanceTypeID, PG_Tracking.BASE_CLIENTID AS Base_ClientID, PG_Tracking.CARRYFEE AS CarryFee, PG_Tracking.CONTROLGOODSTYPEID AS ControlGoodsTypeID, PG_Tracking.CREATELOGINID AS CreateLoginID, PG_Tracking.CREATETIME AS CreateTime, PG_Tracking.ENDCITYID AS EndCityID, PG_Tracking.ENDCOUNTYID AS EndCountyID, PG_Tracking.ENDORGID AS EndOrgID, PG_Tracking.ENDPOINTID AS EndPointID, PG_Tracking.ENDPROVINCEID AS EndProvinceID, PG_Tracking.ENDVILLAGEID AS EndVillageID, PG_Tracking.ESTIMATEARRIVETIME AS EstimateArriveTime, PG_Tracking.ESTIMATECARRYTIME AS EstimateCarryTime, PG_Tracking.EXTERNORDERNUMBER AS ExternOrderNumber, PG_Tracking.FEETYPE AS FeeType, PG_Tracking.INSUREDFEE AS InsuredFee, PG_Tracking.INSUREDVALUE AS InsuredValue, PG_Tracking.INVOICENUMBER AS InvoiceNumber, PG_Tracking.INVOICEVALUE AS InvoiceValue, PG_Tracking.ISBILL AS IsBill, PG_Tracking.ISPICKUP AS IsPickup, PG_Tracking.LOADINGFEE AS LoadingFee, PG_Tracking.MODIFYLOGINID AS ModifyLoginID, PG_Tracking.MODIFYTIME AS ModifyTime, PG_Tracking.ORDERNUMBER AS OrderNumber, PG_Tracking.OTHERFEE AS OtherFee, PG_Tracking.fID AS PG_Tracking, PG_Tracking.PACKINGFEE AS PackingFee, PG_Tracking.PACKINGQTY AS PackingQty, PG_Tracking.PAYMENTTYPE AS PaymentType, PG_Tracking.PRODUCTTYPEID AS ProductTypeID, PG_Tracking.RECEIPTTIME AS ReceiptTime, PG_Tracking.RELEVANCETRUCKINGNUMBER AS RelevanceTruckingNumber, PG_Tracking.REMARK AS Remark, PG_Tracking.RETURNBILLTYPEID AS ReturnBillTypeID, PG_Tracking.RETURNFEE AS ReturnFee, PG_Tracking.ROUTECODE AS RouteCode, PG_Tracking.SALESDISCOUNT AS SalesDiscount, PG_Tracking.SALESPERSON AS SalesPerson, PG_Tracking.STARTCITYID AS StartCityID, PG_Tracking.STARTCOUNTYID AS StartCountyID, PG_Tracking.STARTORGID AS StartOrgID, PG_Tracking.STARTPOINTID AS StartPointID, PG_Tracking.STARTPROVINCEID AS StartProvinceID, PG_Tracking.STARTVILLAGEID AS StartVillageID, PG_Tracking.TOTALFEE AS TotalFee, PG_Tracking.TOTALQTY AS TotalQty, PG_Tracking.TOTALVOLUME AS TotalVolume, PG_Tracking.TOTALWEIGHT AS TotalWeight, PG_Tracking.TRUCKINGNUMBER AS TruckingNumber, PG_Tracking.TRUCKINGSTATUS AS TruckingStatus, PG_Tracking.URGENCYID AS UrgencyID, PG_Tracking.VALID AS Valid, Base_DropDown_20.NAME AS t_BalanceTypeID, Base_Client.CHINESENAME AS t_ClientID, Base_DropDown_21.NAME AS t_ControlGoodsTypeID, Base_Area_9.NAME AS t_EndCityID, Base_Area_10.NAME AS t_EndCountyID, Base_Organization_2.NAME AS t_EndOrgID, Base_ClientPoint_1.NAME AS t_EndPointID, Base_Area_8.NAME AS t_EndProvinceID, Base_Area_11.NAME AS t_EndVillageID, Base_DropDown_12.NAME AS t_PaymentType, Base_DropDown_3.NAME AS t_ProductTypeID, Base_DropDown_4.NAME AS t_ReturnBillTypeID, Base_Area_5.NAME AS t_StartCityID, Base_Area_6.NAME AS t_StartCountyID, Base_Organization.NAME AS t_StartOrgID, Base_ClientPoint.NAME AS t_StartPointID, Base_Area.NAME AS t_StartProvinceID, Base_Area_7.NAME AS t_StartVillageID, Base_ClientPoint_19.ADDRESS AS t_endAddress, Base_ClientPoint_17.MOBILEPHONE AS t_endMobilePhone, Base_ClientPoint_16.NAME AS t_endName, Base_ClientPoint_18.ADDRESS AS t_startAddress, Base_ClientPoint_14.MOBILEPHONE AS t_startMobilePhone, Base_ClientPoint_15.NAME AS t_startName, Base_DropDown.NAME AS t_status, PG_Tracking.VERSION AS version FROM PG_TRACKING PG_Tracking LEFT JOIN Base_DropDown Base_DropDown ON PG_Tracking.TRUCKINGSTATUS = Base_DropDown.fID LEFT JOIN Base_Client Base_Client ON PG_Tracking.BASE_CLIENTID = Base_Client.fID LEFT JOIN Base_ClientPoint Base_ClientPoint ON PG_Tracking.STARTPOINTID = Base_ClientPoint.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_1 ON PG_Tracking.ENDPOINTID = Base_ClientPoint_1.fID LEFT JOIN Base_Organization Base_Organization ON PG_Tracking.STARTORGID = Base_Organization.fid LEFT JOIN Base_Organization Base_Organization_2 ON PG_Tracking.ENDORGID = Base_Organization_2.fid LEFT JOIN Base_DropDown Base_DropDown_3 ON PG_Tracking.PRODUCTTYPEID = Base_DropDown_3.fID LEFT JOIN Base_DropDown Base_DropDown_4 ON PG_Tracking.RETURNBILLTYPEID = Base_DropDown_4.fID LEFT JOIN Base_Area Base_Area ON PG_Tracking.STARTPROVINCEID = Base_Area.fID LEFT JOIN Base_Area Base_Area_5 ON PG_Tracking.STARTCITYID = Base_Area_5.fID LEFT JOIN Base_Area Base_Area_6 ON PG_Tracking.STARTCOUNTYID = Base_Area_6.fID LEFT JOIN Base_Area Base_Area_7 ON PG_Tracking.STARTVILLAGEID = Base_Area_7.fID LEFT JOIN Base_Area Base_Area_8 ON PG_Tracking.ENDPROVINCEID = Base_Area_8.fID LEFT JOIN Base_Area Base_Area_9 ON PG_Tracking.ENDCITYID = Base_Area_9.fID LEFT JOIN Base_Area Base_Area_10 ON PG_Tracking.ENDCOUNTYID = Base_Area_10.fID LEFT JOIN Base_Area Base_Area_11 ON PG_Tracking.ENDVILLAGEID = Base_Area_11.fID LEFT JOIN Base_DropDown Base_DropDown_12 ON PG_Tracking.PAYMENTTYPE = Base_DropDown_12.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_14 ON PG_Tracking.STARTPOINTID = Base_ClientPoint_14.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_15 ON PG_Tracking.STARTPOINTID = Base_ClientPoint_15.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_16 ON PG_Tracking.ENDPOINTID = Base_ClientPoint_16.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_17 ON PG_Tracking.ENDPOINTID = Base_ClientPoint_17.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_18 ON PG_Tracking.STARTPOINTID = Base_ClientPoint_18.fID LEFT JOIN Base_ClientPoint Base_ClientPoint_19 ON PG_Tracking.ENDPOINTID = Base_ClientPoint_19.fID LEFT JOIN Base_DropDown Base_DropDown_20 ON PG_Tracking.BALANCETYPEID = Base_DropDown_20.fID LEFT JOIN Base_DropDown Base_DropDown_21 ON PG_Tracking.CONTROLGOODSTYPEID = Base_DropDown_21.fID  WHERE PG_Tracking.VALID = ?
--> binds:[1]
2015-07-07 00:29:05 sql 执行时间：24186ms
2015-07-07 00:29:05 sql 执行时间：24186ms
有时候会会到达30秒，仔细看了一下，KSQL虽然有分页，感觉上是有，但拼出来的sql是没有分页的。如果将拼出来的sql拷贝出来运行，确实要二三十秒的时间，但如果加了分页 select top 20 **  用时是0.x秒，相差两三百倍的时间。就算是单表没有任何关联也会有这种情况。给人的感觉就是查询是整表查询，界面再根据设置的分页参数分页。难道要自己写action返回table，在代码中实现分页？又不了解x5分页机制，求优化方案。。

jishuang

runtime\BusinessServer\WEB-INF\justep.xml文件中把
<db-paging>false</db-paging>
改为
<db-paging>true</db-paging>然后重启服务

just_j++

效率快了很多，结贴吧

懒猪柏拉图

just_j++ 发表于 2015-7-7 16:04
效率快了很多，结贴吧

楼主，你好，我也在弄这个问题，我看了一下我的justep.xml文件里面并没有<db-paging>false</db-paging>这行，帮忙解答一下？

费曼

jishuang 发表于 2015-7-7 13:44
runtime\BusinessServer\WEB-INF\justep.xml文件中把
false
改为

设置这个有什么用 这个配置是干嘛的