这样的语句改如何优化，没分了，大神帮帮忙

Question

Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("DeliveryType", "DeliveryType");
            dic.Add("StatusCode", "ChargeMasterStatus");
            dic.Add("Priority", "Priority");
            dic.Add("TransType", "TransType");
            dic.Add("NoteBookingStatus", "bookingStatus");
            dic.Add("NoteTransMode", "DeliveryType");
            dic.Add("ReceiptStatus", "ReceiptStatus");
            dic.Add("NoteInsureType", "InsureType");
            dic.Add("NoteInsureStatus", "InsureStatus");
            dic.Add("NoteBookingPayMode", "BookingPayMode");
            dic.Add("Misc22", "ChargeUnit");
            dic.Add("Misc25", "OverDueStatus");
            dic.Add("PayMode", "PayMode");
            DateTime dt1 = DateTime.Now;
            dt = service.Query("v_FmsBookingNote_task_LoadOrder_Booking_Packing_ChargeMaster", columnList, "CreateTime desc", filter, true, dic, "IsSign,IsRF,IsDanger,IsInsure,IsChangeConsignee,IsRoad,IsWX,IsFreeze,IsReceivePay,IsTurn,IsDispatch,IsBD,IsReportCtnrNo,IsCheckCtnrNo,IsDetain,IsPrintBookingOrder,IsPrintLoadOrder,IsPrintDeliveryOrder,IsPrintCustomerAgentOrder,IsPrintInsureOrder,IsPrintLoadBoxOrder,IsPrintShipOrderDetail,NoteIsDispatch_Delivery,Misc1,Misc2,Misc3");
            DateTime dt2 = DateTime.Now;
            TimeSpan ts = dt2 - dt1;
            double sec = ts.TotalSeconds;            dt1 = DateTime.Now;
            this.gvFmsBookingNote.DataSource = AddChargeDetailColumn(dt, filter);            dt2 = DateTime.Now;
            ts = dt2 - dt1;
            sec = ts.TotalSeconds;

Answer 1

--"v_FmsBookingNote_task_LoadOrder_Booking_Packing_ChargeMaster" 这是你的视图名?
--好长哇。。
你先修改一下 where 条件 对CreateTime的处理。由
where
 Convert(varchar(10),CreateTime,120) >= '2011-04-01' 
and Convert(varchar(10),CreateTime,120) <= '2011-10-01' 
改为
where
 CreateTime>= '2011-04-01' 
and  CreateTime <= '2011-10-01' --看一下 CreateTime 在哪个表的。在这个字段加一下索引。试一下

Answer 2

终于看完了....
1、from前面没有标量子查询 不用优化了
2、from后面where 没有内联视图，也OK
3、有个exists半连接，貌似也没啥搞头.....

Answer 3

SELECT DISTINCT
        TaskStatus ,
        CASE WHEN ISNULL(ChargeCheckTotal, 0) > 0
                  AND CONVERT(DECIMAL(18, 2), ISNULL(ChargeUnCheckTotal, 0)) = 0
             THEN '已收款'
             ELSE '未收款'
        END AS ChargeStatus ,
        CONVERT(BIT, IsSign) AS IsSign ,
        BillNo ,
        CustomerShortName ,
        ShipperShortName ,
        ConsigneeShortName ,
        ForwarderAgentShortName ,
        NotifyShortName ,
        POLPortCHIName ,
        PODPortCHIName ,
        LoadAddress ,
        DeliveryAddress ,
        LoadFeePlaceName ,
        DeliveryFeePlaceName ,
        LoadRequest ,
        DeliveryRequest ,
        VesselName ,
        VoyName ,
        ISNULL(VesselName, '') + '/' + ISNULL(VoyName, '') AS VesselNameVoy ,
        BookingNoteID ,
        CustomerBillNo ,
        SalesManName ,
        CanvassMode ,
        DeliveryType ,
        PayMode ,
        CONVERT(VARCHAR(16), LoadDate, 120) AS LoadDate ,
        BigCategoryName ,
        CONVERT(BIT, IsRF) AS IsRF ,
        CONVERT(BIT, IsDanger) AS IsDanger ,
        CONVERT(VARCHAR(10), PlanDeliveryTime, 120) AS PlanDeliveryTime ,
        CONVERT(VARCHAR(10), RealDeliveryTime, 120) AS RealDeliveryTime ,
        CONVERT(BIT, ISNULL(IsChangeConsignee, 0)) AS IsChangeConsignee ,
        IsRoad ,
        BlNos ,
        CtnrNos ,
        IsWX ,
        CreateUser ,
        CreateTime ,
        ConfirmUser ,
        ConfirmTime ,
        Re ,
        VesselCompanyName ,
        CustomerAddress ,
        CustomerLinkMan ,
        CustomerTel ,
        AuditorName ,
        FbsAuditorName ,
        AbolisherName ,
        FbsAbolisherName ,
        FbsAuditDate ,
        FbsAbolishDate ,
        AuditDate ,
        AbolishDate ,
        ChargeMasterNo ,
        StatusCode ,
        CONVERT(BIT, IsFreeze) AS IsFreeze ,
        CONVERT(BIT, IsReceivePay) AS IsReceivePay ,
        CONVERT(DECIMAL(18, 2), Amount) AS Amount ,
        CtnrNos_SealNos ,
        BookingNos ,
        BookingAgents ,
        CtnrSizeNum ,
        ShipperLinkMan ,
        ShipperTel ,
        ConsigneeLinkMan ,
        ConsigneeTel ,
        LoadTruckCompanyShortName ,
        DeliveryTruckCompanyShortName ,
        Priority ,
        TransType ,
        CONVERT(VARCHAR(16), ETA, 120) AS ETA ,
        CONVERT(VARCHAR(16), ETD, 120) AS ETD ,
        CONVERT(VARCHAR(16), AAB, 120) AS AAB ,
        CONVERT(VARCHAR(16), ADB, 120) AS ADB ,
        NoteSecondVesselName ,
        NoteSecondVoyName ,
        CONVERT(VARCHAR(16), Misc6, 120) AS Misc6 ,
        CONVERT(VARCHAR(16), Misc7, 120) AS Misc7 ,
        CONVERT(VARCHAR(16), NoteSecondADB, 120) AS NoteSecondADB ,
        CONVERT(VARCHAR(16), NoteSecondAAB, 120) AS NoteSecondAAB ,
        InlandVesselName ,
        InlandVoy ,
        CONVERT(VARCHAR(16), Misc8, 120) AS Misc8 ,
        CONVERT(VARCHAR(16), Misc9, 120) AS Misc9 ,
        CONVERT(VARCHAR(16), NoteInlandADB, 120) AS NoteInlandADB ,
        CONVERT(VARCHAR(16), NoteInlandAAB, 120) AS NoteInlandAAB ,
        NoteBookingStatus ,
        NoteTransMode ,
        Misc2 ,
        CONVERT(DECIMAL(18, 0), Misc16) AS Misc16 ,
        ReceiptStatus ,
        NoteBookingPayMode ,
        ReceiptNo ,
        ReceiptRe ,
        CONVERT(BIT, IsBD) AS IsBD ,
        CONVERT(VARCHAR(10), BDDate, 120) AS BDDate ,
        IsReportCtnrNo ,
        CONVERT(VARCHAR(10), ReportCtnrNoDate, 120) AS ReportCtnrNoDate ,
        CONVERT(BIT, IsCheckCtnrNo) AS IsCheckCtnrNo ,
        CONVERT(VARCHAR(10), CheckCtnrNoDate, 120) AS CheckCtnrNoDate ,
        CONVERT(BIT, IsInsure) AS IsInsure ,
        InsurerShortName ,
        CONVERT(DECIMAL(18, 2), NoteCargoAmount) AS NoteCargoAmount ,
        CONVERT(DECIMAL(18, 2), NoteInSurance) AS NoteInSurance ,
        NoteInsureRates ,
        CONVERT(DECIMAL(18, 2), NoteAmountCovered) AS NoteAmountCovered ,
        NoteInsureNumber ,
        NoteInsureType ,
        NoteInsureStatus ,
        CONVERT(VARCHAR(10), NoteInsureLeaveDate, 120) AS NoteInsureLeaveDate ,
        CONVERT(VARCHAR(10), NoteInsureBuyDate, 120) AS NoteInsureBuyDate ,
        CONVERT(VARCHAR(10), NoteInsureAcceptedDate, 120) AS NoteInsureAcceptedDate ,
        CONVERT(VARCHAR(10), NoteInsureModifyDate, 120) AS NoteInsureModifyDate ,
        CONVERT(VARCHAR(10), NoteInsureValidDate, 120) AS NoteInsureValidDate ,
        CONVERT(VARCHAR(10), NoteInsureOccurredDate, 120) AS NoteInsureOccurredDate ,
        NoteInsureRe ,
        CONVERT(BIT, IsDetain) AS IsDetain ,
        CONVERT(VARCHAR(10), DetainTime, 120) AS DetainTime ,
        DetainReson ,
        CONVERT(VARCHAR(10), UnDetainTime, 120) AS UnDetainTime ,
        UnDetainReson ,
        IsPrintBookingOrder ,
        CONVERT(VARCHAR(10), PrintBookingOrderDate, 120) AS PrintBookingOrderDate ,
        IsPrintLoadOrder ,
        CONVERT(VARCHAR(10), PrintLoadOrderDate, 120) AS PrintLoadOrderDate ,
        IsPrintDeliveryOrder ,
        CONVERT(VARCHAR(10), PrintDeliveryOrderDate, 120) AS PrintDeliveryOrderDate ,
        IsPrintCustomerAgentOrder ,
        CONVERT(VARCHAR(10), PrintCustomerAgentOrderDate, 120) AS PrintCustomerAgentOrderDate ,
        IsPrintInsureOrder ,
        CONVERT(VARCHAR(10), PrintInsureOrderDate, 120) AS PrintInsureOrderDate ,
        IsPrintLoadBoxOrder ,
        CONVERT(VARCHAR(10), PrintLoadBoxOrderDate, 120) AS PrintLoadBoxOrderDate ,
        IsPrintShipOrderDetail ,
        CONVERT(VARCHAR(10), PrintShipOrderDetailDate, 120) AS PrintShipOrderDetailDate ,
        IsDispatch ,
        NoteDispatchUserName ,
        NoteDispatchTime ,
        ToOrganizationChiName ,
        NoteBizOrderID ,
        NoteBizOrderNo ,
        NoteIsDispatch_Delivery ,
        NoteDispatchUserName_Delivery ,
        NoteDispatchTime_Delivery ,
        NoteToOrganizationID_Delivery ,
        NoteBizOrderID_Delivery ,
        NoteBizOrderNo_Delivery ,
        CONVERT(DECIMAL(18, 2), ISNULL(ChargeTotal, 0)) AS ChargeTotal ,
        CONVERT(DECIMAL(18, 2), ISNULL(PayTotal, 0)) AS PayTotal ,
        CONVERT(DECIMAL(18, 2), ISNULL(Profit, 0)) AS Profit ,
        CONVERT(DECIMAL(18, 2), ISNULL(ChargeCheckTotal, 0)) AS ChargeCheckTotal ,
        CONVERT(DECIMAL(18, 2), ISNULL(ChargeUnCheckTotal, 0)) AS ChargeUnCheckTotal ,
        Misc1 ,
        Status ,
        DepartureRe ,
        SailRe ,
        ArrivalRe ,
        SecondDepartureRe ,
        SecondSailRe ,
        SecondArrivalRe ,
        InlandDepartureRe ,
        InlandSailRe ,
        InlandArrivalRe ,
        Misc22 ,
        Misc23 ,
        Misc24 ,
        CONVERT(DECIMAL(18, 2), Quantity) AS Quantity ,
        CBM ,
        GrossWeight ,
        CONVERT(BIT, Misc3) AS Misc3 ,
        CONVERT(VARCHAR(10), Misc10, 120) AS Misc10 ,
        CONVERT(DECIMAL(18, 2), ISNULL(ChargeBalanceAmount, 0)) AS ChargeBalanceAmount ,
        CONVERT(DECIMAL(18, 2), ISNULL(ChargeUnBalanceAmount, 0)) AS ChargeUnBalanceAmount ,
        OrganizationCode ,
        DATEDIFF(day, CreateTime, GETDATE()) AS days ,
        DATEDIFF(month, CreateTime, GETDATE()) AS months ,
        DATEPART(wk, GETDATE()) AS wk ,
        DATEPART(yy, GETDATE()) AS yy ,
        DATEPART(wk, CreateTime) AS Notewk ,
        DATEPART(yy, CreateTime) AS Noteyy ,
        DATEDIFF(day, GETDATE(), LoadDate) AS days_LoadDate ,
        DATEPART(wk, LoadDate) AS Notewk_LoadDate ,
        DATEPART(yy, LoadDate) AS Noteyy_LoadDate ,
        Misc25 ,
        DATEDIFF(month, LoadDate, GETDATE()) AS months_LoadDate ,
        ISNULL(HYF, 0) AS HYF ,
        ISNULL(HYFChecked, 0) AS HYFChecked ,
        ISNULL(HyfUnChecked, 0) AS HyfUnChecked ,
        CONVERT(VARCHAR(16), SaleManETD, 120) AS SaleManETD ,
        CONVERT(VARCHAR(16), SaleManETA, 120) AS SaleManETA
FROM    v_FmsBookingNote_task_LoadOrder_Booking_Packing_ChargeMaster    --压力好像都在这个视图里面哦。 
WHERE   TransType <> '4'
        AND CONVERT(VARCHAR(10), CreateTime, 120) >= '2011-04-01'
        AND CONVERT(VARCHAR(10), CreateTime, 120) <= '2011-10-01'
        AND EXISTS ( SELECT 1
                     FROM   syssystemuser
                     WHERE  SystemUserID = 'b8f109d0-d2b1-4135-8bca-07dca1c17112'
                            AND CHARINDEX(OrganizationCode, AuthCode) > 0 )
                            
--lz看看v_FmsBookingNote_task_LoadOrder_Booking_Packing_ChargeMaster 视图中的执行计划吧。

Answer 4

谢谢大家这么热心，我现在把我的数据库发布出来，有兴趣的帮我研究下
http://kanboxshare.com/link/ujIEvgnvXZ3ehKVSuZr6AzCGoIEoBjMPvSoU9Rc4MTWfFVr1w查询语句
select distinct TaskStatus,Case when ISNULL(ChargeCheckTotal,0)>0 and Convert(decimal(18,2),ISNULL(ChargeUnCheckTotal,0))=0 then '已收款' else '未收款' end as ChargeStatus,convert(bit,IsSign) as IsSign,BillNo,CustomerShortName,ShipperShortName,ConsigneeShortName,ForwarderAgentShortName,NotifyShortName,POLPortCHIName,PODPortCHIName,LoadAddress,DeliveryAddress,LoadFeePlaceName,DeliveryFeePlaceName,LoadRequest,DeliveryRequest,VesselName,VoyName,ISNULL(VesselName,'')+'/'+ISNULL(VoyName,'') as VesselNameVoy,BookingNoteID,CustomerBillNo,SalesManName,CanvassMode,DeliveryType,PayMode,convert(varchar(16),LoadDate,120) as LoadDate,BigCategoryName,convert(bit,IsRF) as IsRF ,convert(bit,IsDanger) as IsDanger ,convert(varchar(10),PlanDeliveryTime,120) as PlanDeliveryTime,convert(varchar(10),RealDeliveryTime,120) as RealDeliveryTime,convert(bit,isnull(IsChangeConsignee,0)) as IsChangeConsignee,IsRoad,BlNos,CtnrNos,IsWX,CreateUser,CreateTime,ConfirmUser,ConfirmTime,Re,VesselCompanyName,CustomerAddress,CustomerLinkMan,CustomerTel,AuditorName,FbsAuditorName,AbolisherName,FbsAbolisherName,FbsAuditDate,FbsAbolishDate,AuditDate,AbolishDate,ChargeMasterNo,StatusCode,convert(bit,IsFreeze)as IsFreeze ,convert(bit,IsReceivePay)as IsReceivePay,Convert(decimal(18,2),Amount) as Amount,CtnrNos_SealNos,BookingNos,BookingAgents,CtnrSizeNum,ShipperLinkMan,ShipperTel,ConsigneeLinkMan,ConsigneeTel,LoadTruckCompanyShortName,DeliveryTruckCompanyShortName,Priority,TransType,convert(varchar(16),ETA,120) as ETA,convert(varchar(16),ETD,120) as ETD,convert(varchar(16),AAB,120) as AAB,convert(varchar(16),ADB,120) as ADB,NoteSecondVesselName,NoteSecondVoyName,convert(varchar(16),Misc6,120) as Misc6,convert(varchar(16),Misc7,120) as Misc7,convert(varchar(16),NoteSecondADB,120) as NoteSecondADB,convert(varchar(16),NoteSecondAAB,120) as NoteSecondAAB,InlandVesselName,InlandVoy,convert(varchar(16),Misc8,120) as Misc8,convert(varchar(16),Misc9,120) as Misc9,convert(varchar(16),NoteInlandADB,120) as NoteInlandADB,convert(varchar(16),NoteInlandAAB,120) as NoteInlandAAB,NoteBookingStatus,NoteTransMode,Misc2,Convert(decimal(18,0),Misc16) as Misc16,ReceiptStatus,NoteBookingPayMode,ReceiptNo,ReceiptRe,convert(bit,IsBD) as IsBD,convert(varchar(10),BDDate,120) as BDDate,IsReportCtnrNo,convert(varchar(10),ReportCtnrNoDate,120) as ReportCtnrNoDate,convert(bit,IsCheckCtnrNo) as IsCheckCtnrNo,convert(varchar(10),CheckCtnrNoDate,120) as CheckCtnrNoDate,convert(bit,IsInsure) as IsInsure ,InsurerShortName,Convert(decimal(18,2),NoteCargoAmount) as NoteCargoAmount,Convert(decimal(18,2),NoteInSurance) as NoteInSurance,NoteInsureRates,Convert(decimal(18,2),NoteAmountCovered) as NoteAmountCovered,NoteInsureNumber,NoteInsureType,NoteInsureStatus,convert(varchar(10),NoteInsureLeaveDate,120) as NoteInsureLeaveDate,convert(varchar(10),NoteInsureBuyDate,120) as NoteInsureBuyDate,convert(varchar(10),NoteInsureAcceptedDate,120) as NoteInsureAcceptedDate,convert(varchar(10),NoteInsureModifyDate,120) as NoteInsureModifyDate,convert(varchar(10),NoteInsureValidDate,120) as NoteInsureValidDate,convert(varchar(10),NoteInsureOccurredDate,120) as NoteInsureOccurredDate,NoteInsureRe,convert(bit,IsDetain) as IsDetain,convert(varchar(10),DetainTime,120) as DetainTime,DetainReson,convert(varchar(10),UnDetainTime,120) as UnDetainTime,UnDetainReson,IsPrintBookingOrder,convert(varchar(10),PrintBookingOrderDate,120) as PrintBookingOrderDate,IsPrintLoadOrder,convert(varchar(10),PrintLoadOrderDate,120) as PrintLoadOrderDate,IsPrintDeliveryOrder,convert(varchar(10),PrintDeliveryOrderDate,120) as PrintDeliveryOrderDate,IsPrintCustomerAgentOrder,convert(varchar(10),PrintCustomerAgentOrderDate,120) as PrintCustomerAgentOrderDate,IsPrintInsureOrder,convert(varchar(10),PrintInsureOrderDate,120) as PrintInsureOrderDate,IsPrintLoadBoxOrder,convert(varchar(10),PrintLoadBoxOrderDate,120) as PrintLoadBoxOrderDate,IsPrintShipOrderDetail,convert(varchar(10),PrintShipOrderDetailDate,120) as PrintShipOrderDetailDate,IsDispatch,NoteDispatchUserName,NoteDispatchTime,ToOrganizationChiName,NoteBizOrderID,NoteBizOrderNo,NoteIsDispatch_Delivery,NoteDispatchUserName_Delivery,NoteDispatchTime_Delivery,NoteToOrganizationID_Delivery,NoteBizOrderID_Delivery,NoteBizOrderNo_Delivery,Convert(decimal(18,2),ISNULL(ChargeTotal,0)) AS ChargeTotal,Convert(decimal(18,2),ISNULL(PayTotal,0)) AS PayTotal,Convert(decimal(18,2),ISNULL(Profit,0)) AS Profit,Convert(decimal(18,2),ISNULL(ChargeCheckTotal,0)) AS ChargeCheckTotal,Convert(decimal(18,2),ISNULL(ChargeUnCheckTotal,0)) AS ChargeUnCheckTotal,Misc1,Status,DepartureRe,SailRe,ArrivalRe,SecondDepartureRe,SecondSailRe,SecondArrivalRe,InlandDepartureRe,InlandSailRe,InlandArrivalRe,Misc22,Misc23,Misc24,Convert(decimal(18,2),Quantity) as Quantity,CBM,GrossWeight,convert(bit,Misc3) as Misc3,convert(varchar(10),Misc10,120) as Misc10,Convert(decimal(18,2),ISNULL(ChargeBalanceAmount,0)) AS ChargeBalanceAmount,Convert(decimal(18,2),ISNULL(ChargeUnBalanceAmount,0)) AS ChargeUnBalanceAmount,OrganizationCode,datediff(day,CreateTime,getdate()) as days,datediff(month,CreateTime,getdate()) as months,DATEPART(wk, getdate()) AS wk,DATEPART(yy, getdate()) as yy,DATEPART(wk, CreateTime) AS Notewk,DATEPART(yy, CreateTime) as Noteyy,datediff(day,getdate(),LoadDate) as days_LoadDate,DATEPART(wk, LoadDate) AS Notewk_LoadDate,DATEPART(yy, LoadDate) as Noteyy_LoadDate,Misc25,datediff(month,LoadDate,getdate()) as months_LoadDate,isnull(HYF,0) as HYF,isnull(HYFChecked,0) as HYFChecked,isnull(HyfUnChecked,0) as HyfUnChecked,convert(varchar(16),SaleManETD,120) as SaleManETD,convert(varchar(16),SaleManETA,120) as SaleManETA
from v_FmsBookingNote_task_LoadOrder_Booking_Packing_ChargeMaster
where TransType<>'4'  and Convert(varchar(10),CreateTime,120) >= '2013-12-01' 
and Convert(varchar(10),CreateTime,120) <= '2013-12-31' 
and  exists(select 1 from syssystemuser where SystemUserID='7BE40024-0EAB-4889-BD97-8448F765CA7E' and CHARINDEX(OrganizationCode,AuthCode)>0)

Answer 5

提供一下
v_FmsBookingNote_task_LoadOrder_Booking_Packing_ChargeMaster
的代码吧

Answer 6

我大致说下主题结构吧
FmsbookingNote 主单FmsbookingTask 明细表，与主表是一对一的FmsBooking FmsLoadOrder是挂在FmsbookingTask上的，一一对应的FmsPackingList是挂在FmsbookingTask上的，是多对一的FmschargeMaster与FmsbookingNote是一一对应的，表示单证对应的计费主单FbsChargedetail计费明细，与FmschargeMaster多对一的其他的基本都是基础表