sql - 在 SSRS 中使用存储过程代替 SQL 查询时性能下降

Question

为了提高我的报告在加载时间方面的性能，我想将源从直接查询更改为存储过程，但使用存储过程需要更多时间。

我有这个 SQL 查询：

SELECT DISTINCT 
    *,
    YEAR(DT_DATE) YEAR1,
    DATENAME(mm, DT_DATE) Month1,
    CAST([Original Service Line TCV] AS float) OSL_TCV,
    CAST([BB12] AS decimal) BB12_new 
FROM
    T_OPTYLINEVIEW_REP 
WHERE 
    [Selling SBU] IN (@selling_SBU) 
    AND [Selling BU] IN (@selling_BU) 
    AND [Selling MU] IN (@selling_MU) 
    AND [Stage] IN (@stage) 
    AND [Account Type] IN (@Account_Type) 
    AND [Business Line] IN (@Business_Line) 
    AND [Business Line Level 1] IN (@Business_Line_Level_1) 
    AND [Business Line Level 2] IN (@Business_Line_Level_2) 
    AND [Offer] IN (@Offer)
    AND [Sector] IN (@Sector) 
    AND [Portfolio] IN (@Portfolio) 
    AND [flag Altran Collaboration] IN (@flag_Altran_Collaboration) 
    AND [Selling Reporting Unit] IN (@Selling_Reporting_Unit) 
    AND CONVERT(VARCHAR, DT_TECH_UPD, 103) = CONVERT( VARCHAR, @ReportAsOn, 103)

我将此查询直接放入 SSRS。为此，如果我在报告服务器执行日志中看到检索时间，它大约需要 11000 毫秒。

我将其转换为存储过程以使处理更快->

[dbo].[OptyLineView_test_proc](    
@selling_SBU nvarchar(max),
@selling_BU nvarchar(max),  
@selling_MU nvarchar(max),  
@stage nvarchar(max),  
@Account_Type nvarchar(max) ,  
@Business_Line nvarchar(max),  
@Business_Line_Level_1 nvarchar(max),  
@Business_Line_Level_2 nvarchar(max),  
@Offer nvarchar(max),  
@Sector nvarchar(max),    
@Portfolio nvarchar(max),  
@flag_Altran_Collaboration nvarchar(max),  
@Selling_Reporting_Unit nvarchar(max),  
@ReportAsOn date)    
as       
select DISTINCT *,    
YEAR(DT_DATE) YEAR1,    
datename(mm,DT_DATE) Month1,    
CAST([Original Service Line TCV] as float)OSL_TCV,    
CAST([BB12] as decimal) BB12_new     
from T_OPTYLINEVIEW_REP     
where [Selling SBU] in (select * from STRING_SPLIT(@selling_SBU, ',')) 
and [Selling BU] in  (select * from STRING_SPLIT(@selling_BU, ','))    
and [Selling MU] in (select * from STRING_SPLIT(@selling_MU,','))     
and [Stage] in (select * from STRING_SPLIT(@stage,','))     
and [Account Type] in (select * from STRING_SPLIT(@Account_Type,','))     
and [Business Line] in (select * from STRING_SPLIT(@Business_Line,','))     
and [Business Line Level 1] in (select * from STRING_SPLIT(@Business_Line_Level_1,','))    
and [Business Line Level 2] in (select * from STRING_SPLIT(@Business_Line_Level_2,','))    
AND [Offer] in (select * from STRING_SPLIT(@Offer,','))   
AND [Sector] in (select * from STRING_SPLIT(@Sector,','))     
AND [Portfolio] in (select * from STRING_SPLIT(@Portfolio,','))     
AND [flag Altran Collaboration] in (select * from STRING_SPLIT(@flag_Altran_Collaboration,','))    
AND [Selling Reporting Unit] in (select * from STRING_SPLIT(@Selling_Reporting_Unit,','))    
AND CONVERT(VARCHAR,DT_TECH_UPD , 103) = CONVERT(VARCHAR,@ReportAsOn , 103)

当我在 SSRS 中使用此过程时，日期检索时间约为 100000 毫秒。

为什么它在存储过程的情况下增加了？

标签： sqlsql-servertsqlreporting-servicesdatabase-performance