当@SalesID为Null时,使用
new SqlParameter("@SalesID", SqlDbType.Int, 0,ParameterDirection.Input, true, 0, 0, "CuI_AdministratorID", DataRowVersion.Current,searchEntity.SalesID ?? (object)DBNull.Value)
查询速度很慢,但是改成
new SqlParameter("@SalesID",searchEntity.SalesID ?? (object)DBNull.Value)
就正常了,需要研究下原因
执行的SQL:
StringBuilder sbMainSQL = new StringBuilder();//SQL的主体
sbMainSQL.Append(@"SELECT COUNT(DISTINCT bpo.CuI_ID) as TotalCount
FROM Cus_A_BPO_View bpo
INNER JOIN dbo.Cus_Information cus ON bpo.CuI_ID = cus.CuI_ID
LEFT JOIN dbo.provider_list_ExtendExt cusExEx ON bpo.CuI_ID = cusExEx.CuI_ID
AND cusExEx.IsDel = 0
LEFT JOIN dbo.Discuss_Stage stage ON bpo.CuI_ID = stage.CuI_ID
AND stage.CuA_Type = '02'
WHERE ( cus.CuI_CompanyName LIKE @CompanyName
OR cus.CuI_CompanyNameEn LIKE @CompanyName
)
AND ( @SalesID IS NULL
OR cus.CuI_AdministratorID = @SalesID
)
AND ( @CusServiceID IS NULL
OR cusExEx.CustomerService = @CusServiceID
)
AND ( @DateFrom IS NULL
OR cus.CuI_Createtime >= @DateFrom
)
AND ( @DateTo IS NULL
OR cus.CuI_Createtime - 1 < @DateTo
)
AND ( @StageID IS NULL
OR stage.CDTYPE_ID = @StageID
)");