sql语句子查询很多 怎么优化？五百多条数据要查2分钟

[花儿谢了后3]

ALTER PROCEDURE [dbo].[P2P_P_getPlanRealStatistics_V1]
	-- Add the parameters for the stored procedure here
	    @ObligorName NVARCHAR(50) = '%' ,
    @BusinessModel INT = NULL ,
    @MainContractNO NVARCHAR(50) = '%' ,
    @plan_repay_date_min DATETIME = NULL ,
    @plan_repay_date_max DATETIME = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT  t1.ObligorName 客户名称 ,
                            CASE t1.ObligorType
                              WHEN 1 THEN '个人'
                              WHEN 2 THEN '公司'
                            END 客户类型 ,
                            ( SELECT    CodeName
                              FROM      dbo.SYSCode
                              WHERE     dbo.SYSCode.CodeType = 'BusinessModel'
                                        AND CodeValue = t2.BusinessModel
                            ) 业务类型 ,
                            t3.Nickname 'SP' ,
                            t2.MainContractNO 支付表编号 ,
                            ( SELECT    plan_repay_benxi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 月租金,
                            ( SELECT    plan_repay_benjin
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
									AND term_preNum = 1
                            ) 本金 ,
                            ( SELECT     plan_repay_lixi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 利息 ,
                            ( SELECT     plan_repay_benxi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 租金合计 ,
                             ( SELECT   SUM(plan_repay_benjin)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID

                            ) 应收总本金 ,
                            ( SELECT     SUM(plan_repay_lixi)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                            ) 应收总利息 ,
                            ( SELECT     SUM(plan_repay_benxi)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                            ) 应收总额 ,
                            t2.LoanID ,
                            t2.LoanFlag ,
                            t5.Car_Type_Name 车型 ,
                             ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '首付款'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 1
                            ) 首付比例 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '手续费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 手续费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '保险费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 保险费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = 'GPS费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) GPS费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '购车价'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 车价 ,
                            t2.LoanAnnualRate 贷款利率,
                            t2.LoanTerm 总期数,
                            t4.SP_BankName SP开户银行,
                            t4.SP_BankNo SP银行账号,
                            t4.LoanTotal 实际放款,
                            t7.ProductTitle 产品类型,
                            t2.BusinessModel,
                            t4.CancellationTime,
                            ISNULL((SELECT SUM(OverdueChage) FROM P2P_Loan_ChargeOverdue WHERE t2.LoanID=P2P_Loan_ChargeOverdue.LoanID),0) 逾期金额 ,
                            ISNULL((SELECT SUM(ChargeSum) FROM P2P_Loan_Charge_Info WHERE t2.LoanID=P2P_Loan_Charge_Info.LoanID),0) 保证金 
                  FROM      P2P_Obligor t1 ,
                            P2P_Loan_Info t2 ,
                            GQShow_UserBase t3 ,
                            P2P_Loan_Process_Info t4 ,
                            P2P_Loan_Car_Info t5,
                            P2P_Loan_Product t7
             
                WHERE   t1.ObligorType = 1
                AND t2.LoanUserID = t1.ObligorID
                AND t3.UserID = t2.FamilyUserID
                AND t4.Flag = 2
                AND t4.LoanID = t2.LoanID
                AND t5.LoanID = t2.LoanID
                AND t2.LoanProductID = t7.ProductID
                AND t4.CancellationTime >= @plan_repay_date_min
                AND t4.CancellationTime <= @plan_repay_date_max
                AND t1.ObligorName LIKE '%' + @ObligorName + '%'
                AND t2.MainContractNO LIKE '%' + @MainContractNO + '%'
                AND t2.BusinessModel = CASE WHEN @BusinessModel = 0
                                            THEN t2.BusinessModel
                                            WHEN @BusinessModel IS NULL
                                            THEN t2.BusinessModel
                                            ELSE @BusinessModel
                                       END;
                
            
END

[shoppo0505]

既然写成了存储过程，那就分步执行简单语句好了，别用这种复杂的单个语句。

[㤁孞]

把应收的写成函数调用,不要在现在的位置做表链接,其他需要的字段用表链接后直接取啊,你放在里面重复链接那么多次肯定慢的

[Mr_Nice]

ctrl + L 查询计划发一下,好具体判断一下这个语句慢在哪儿. 初步看, 子查询太多了. 另外case在条件语句中也是减速的典型之一, 最后就是要看索引了. 链接相关字段是否都已经有索引. 查出来是500多行, 得看是从多少行中查的,怎么查的才能具体分析慢的原因.

[花儿谢了后3]

引用 3 楼 OrchidCat 的回复:

ctrl + L 查询计划发一下,好具体判断一下这个语句慢在哪儿. 初步看, 子查询太多了. 另外case在条件语句中也是减速的典型之一, 最后就是要看索引了. 链接相关字段是否都已经有索引. 查出来是500多行, 得看是从多少行中查的,怎么查的才能具体分析慢的原因.

就是子查询的问题 现在问题是 那个sum聚函数 不知道怎么提出来链接查询

[njz168]

( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '首付款'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 1
                            ) 首付比例 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '手续费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 手续费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '保险费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 保险费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = 'GPS费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) GPS费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '购车价'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 车价 ,

这部分都取自一个表，可以先行转列。然后和T2连接。

[njz168]

( SELECT    plan_repay_benxi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 月租金,
                            ( SELECT    plan_repay_benjin
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                    AND term_preNum = 1
                            ) 本金 ,
                            ( SELECT     plan_repay_lixi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 利息 ,
                            ( SELECT     plan_repay_benxi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 租金合计 ,
                             ( SELECT   SUM(plan_repay_benjin)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
 
                            ) 应收总本金 ,
                            ( SELECT     SUM(plan_repay_lixi)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                            ) 应收总利息 ,
                            ( SELECT     SUM(plan_repay_benxi)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                            ) 应收总额 ,

这部分取自一个表，可以以LoadniD先聚合作为子查询再和T2连接。