Stored Procedure Takes Longer to Execute then TSQL Query in SQL Server 2005

Question

I am very new to TSQL and stored procedures

TSQL and stored procedure both uses defined variable. TSQL takes 19 seconds to get results, however stored procedure takes around 8 minutes

Stored procedure to get security prices for each day in the month. If day is weekend then use the last working days price.

Code of stored procedure:

SET NOCOUNT ON
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_SEC_PRICES_FOR_SKP] @sett_date datetime as

SELECT A.SECID, A.PRODTYPE, A.CCY, B1.D1, B2.D2,B3.D3,B4.D4, B5.D5,B6.D6,B7.D7,B8.D8,B9.D9,B10.D10,  
B11.D11,B12.D12,B13.D13,B14.D14,B15.D15,B16.D16,B17.D17,B18.D18,B19.D19,B20.D20,  
B21.D21,B22.D22,B23.D23,B24.D24,B25.D25,B26.D26,B27.D27,B28.D28,B29.D29,B30.D30,  
B31.D31,
@sett_date, @sett_date+4, @sett_date+30 FROM SECM A

LEFT JOIN (SELECT SECID, (CLPRICE_8)D1 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date) = 'Saturday' then (@sett_date-1)
                                 when DATENAME (dw, @sett_date) = 'Sunday' then (@sett_date-2)
                                 else @sett_date end) B1
ON A.SECID = B1.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D2 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+1) = 'Saturday' then ((@sett_date+1)-1)
                                 when DATENAME (dw, @sett_date+1) = 'Sunday' then ((@sett_date+1)-2)
                                 else @sett_date+1 end) B2
ON A.SECID = B2.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D3 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+2) = 'Saturday' then ((@sett_date+2)-1)
                                 when DATENAME (dw, @sett_date+2) = 'Sunday' then ((@sett_date+2)-2)
                                 else @sett_date+2 end) B3
ON A.SECID = B3.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D4 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+3) = 'Saturday' then ((@sett_date+3)-1)
                                 when DATENAME (dw, @sett_date+3) = 'Sunday' then ((@sett_date+3)-2)
                                 else @sett_date+3 end) B4
ON A.SECID = B4.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D5 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+4) = 'Saturday' then ((@sett_date+4)-1)
                                 when DATENAME (dw, @sett_date+4) = 'Sunday' then ((@sett_date+4)-2)
                                 else @sett_date+4 end) B5
ON A.SECID = B5.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D6 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+5) = 'Saturday' then ((@sett_date+5)-1)
                                 when DATENAME (dw, @sett_date+5) = 'Sunday' then ((@sett_date+5)-2)
                                 else @sett_date+5 end) B6
ON A.SECID = B6.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D7 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+6) = 'Saturday' then ((@sett_date+6)-1)
                                 when DATENAME (dw, @sett_date+6) = 'Sunday' then ((@sett_date+6)-2)
                                 else @sett_date+6 end) B7
ON A.SECID = B7.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D8 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+7) = 'Saturday' then ((@sett_date+7)-1)
                                 when DATENAME (dw, @sett_date+7) = 'Sunday' then ((@sett_date+7)-2)
                                 else @sett_date+7 end) B8
ON A.SECID = B8.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D9 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+8) = 'Saturday' then ((@sett_date+8)-1)
                                 when DATENAME (dw, @sett_date+8) = 'Sunday' then ((@sett_date+8)-2)
                                 else @sett_date+8 end) B9
ON A.SECID = B9.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D10 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+9) = 'Saturday' then ((@sett_date+9)-1)
                                 when DATENAME (dw, @sett_date+9) = 'Sunday' then ((@sett_date+9)-2)
                                 else @sett_date+9 end) B10
ON A.SECID = B10.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D11 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+10) = 'Saturday' then ((@sett_date+10)-1)
                                 when DATENAME (dw, @sett_date+10) = 'Sunday' then ((@sett_date+10)-2)
                                 else @sett_date+10 end) B11
ON A.SECID = B11.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D12 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+11) = 'Saturday' then ((@sett_date+11)-1)
                                 when DATENAME (dw, @sett_date+11) = 'Sunday' then ((@sett_date+11)-2)
                                else @sett_date+11 end) B12
ON A.SECID = B12.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D13 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+12) = 'Saturday' then ((@sett_date+12)-1)
                                 when DATENAME (dw, @sett_date+12) = 'Sunday' then ((@sett_date+12)-2)
                                 else @sett_date+12 end) B13
ON A.SECID = B13.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D14 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+13) = 'Saturday' then ((@sett_date+13)-1)
                                 when DATENAME (dw, @sett_date+13) = 'Sunday' then ((@sett_date+13)-2)
                                 else @sett_date+13 end) B14
ON A.SECID = B14.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D15 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+14) = 'Saturday' then ((@sett_date+14)-1)
                                 when DATENAME (dw, @sett_date+14) = 'Sunday' then ((@sett_date+14)-2)
                                 else @sett_date+14 end) B15
ON A.SECID = B15.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D16 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+15) = 'Saturday' then ((@sett_date+15)-1)
                                 when DATENAME (dw, @sett_date+15) = 'Sunday' then ((@sett_date+15)-2)
                                 else @sett_date+15 end) B16
ON A.SECID = B16.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D17 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+16) = 'Saturday' then ((@sett_date+16)-1)
                                 when DATENAME (dw, @sett_date+16) = 'Sunday' then ((@sett_date+16)-2)
                                 else @sett_date+16 end) B17
ON A.SECID = B17.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D18 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+17) = 'Saturday' then ((@sett_date+17)-1)
                                 when DATENAME (dw, @sett_date+17) = 'Sunday' then ((@sett_date+17)-2)
                                 else @sett_date+17 end) B18
ON A.SECID = B18.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D19 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+18) = 'Saturday' then ((@sett_date+18)-1)
                                 when DATENAME (dw, @sett_date+18) = 'Sunday' then ((@sett_date+18)-2)
                                 else @sett_date+18 end) B19
ON A.SECID = B19.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D20 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+19) = 'Saturday' then ((@sett_date+19)-1)
                                 when DATENAME (dw, @sett_date+19) = 'Sunday' then ((@sett_date+19)-2)
                                 else @sett_date+19 end) B20
ON A.SECID = B20.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D21 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+20) = 'Saturday' then ((@sett_date+20)-1)
                                 when DATENAME (dw, @sett_date+20) = 'Sunday' then ((@sett_date+20)-2)
                                 else @sett_date+20 end) B21
ON A.SECID = B21.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D22 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+21) = 'Saturday' then ((@sett_date+21)-1)
                                 when DATENAME (dw, @sett_date+21) = 'Sunday' then ((@sett_date+21)-2)
                                 else @sett_date+21 end) B22
ON A.SECID = B22.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D23 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+22) = 'Saturday' then ((@sett_date+22)-1)
                                 when DATENAME (dw, @sett_date+22) = 'Sunday' then ((@sett_date+22)-2)
                                 else @sett_date+22 end) B23
ON A.SECID = B23.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D24 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+23) = 'Saturday' then ((@sett_date+23)-1)
                                 when DATENAME (dw, @sett_date+23) = 'Sunday' then ((@sett_date+23)-2)
                                 else @sett_date+23 end) B24
ON A.SECID = B24.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D25 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+24) = 'Saturday' then ((@sett_date+24)-1)
                                 when DATENAME (dw, @sett_date+24) = 'Sunday' then ((@sett_date+24)-2)
                                 else @sett_date+24 end) B25
ON A.SECID = B25.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D26 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+25) = 'Saturday' then ((@sett_date+25)-1)
                                 when DATENAME (dw, @sett_date+25) = 'Sunday' then ((@sett_date+25)-2)
                                 else @sett_date+25 end) B26
ON A.SECID = B26.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D27 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+26) = 'Saturday' then ((@sett_date+26)-1)
                                 when DATENAME (dw, @sett_date+26) = 'Sunday' then ((@sett_date+26)-2)
                                 else @sett_date+26 end) B27
ON A.SECID = B27.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D28 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+27) = 'Saturday' then ((@sett_date+27)-1)
                                 when DATENAME (dw, @sett_date+27) = 'Sunday' then ((@sett_date+27)-2)
                                 else @sett_date+27 end) B28
ON A.SECID = B28.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D29 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+28) = 'Saturday' then ((@sett_date+28)-1)
                                 when DATENAME (dw, @sett_date+28) = 'Sunday' then ((@sett_date+28)-2)
                                 else @sett_date+28 end) B29
ON A.SECID = B29.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D30 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+29) = 'Saturday' then ((@sett_date+29)-1)
                                 when DATENAME (dw, @sett_date+29) = 'Sunday' then ((@sett_date+29)-2)
                                 else @sett_date+29 end) B30
ON A.SECID = B30.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D31 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+30) = 'Saturday' then ((@sett_date+30)-1)
                                 when DATENAME (dw, @sett_date+30) = 'Sunday' then ((@sett_date+30)-2)
                                 else @sett_date+30 end) B31
ON A.SECID = B31.SECID

SET NOCOUNT OFF

Code of T-SQL query:

SET NOCOUNT ON

DECLARE @sett_date DateTime

set @sett_date = '20140101'

SELECT A.SECID, A.PRODTYPE, A.CCY, B1.D1, B2.D2,B3.D3,B4.D4, B5.D5,B6.D6,B7.D7,B8.D8,B9.D9,B10.D10,  
B11.D11,B12.D12,B13.D13,B14.D14,B15.D15,B16.D16,B17.D17,B18.D18,B19.D19,B20.D20,  
B21.D21,B22.D22,B23.D23,B24.D24,B25.D25,B26.D26,B27.D27,B28.D28,B29.D29,B30.D30,  
B31.D31,
@sett_date, @sett_date+4, @sett_date+30 FROM SECM A

LEFT JOIN (SELECT SECID, (CLPRICE_8)D1 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date) = 'Saturday' then (@sett_date-1)
                                 when DATENAME (dw, @sett_date) = 'Sunday' then (@sett_date-2)
                                 else @sett_date end) B1
ON A.SECID = B1.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D2 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+1) = 'Saturday' then ((@sett_date+1)-1)
                                 when DATENAME (dw, @sett_date+1) = 'Sunday' then ((@sett_date+1)-2)
                                 else @sett_date+1 end) B2
ON A.SECID = B2.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D3 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+2) = 'Saturday' then ((@sett_date+2)-1)
                                 when DATENAME (dw, @sett_date+2) = 'Sunday' then ((@sett_date+2)-2)
                                 else @sett_date+2 end) B3
ON A.SECID = B3.SECID

----------------------------

LEFT JOIN (SELECT SECID, (CLPRICE_8)D4 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+3) = 'Saturday' then ((@sett_date+3)-1)
                                 when DATENAME (dw, @sett_date+3) = 'Sunday' then ((@sett_date+3)-2)
                                 else @sett_date+3 end) B4
ON A.SECID = B4.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D5 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+4) = 'Saturday' then ((@sett_date+4)-1)
                                 when DATENAME (dw, @sett_date+4) = 'Sunday' then ((@sett_date+4)-2)
                                 else @sett_date+4 end) B5
ON A.SECID = B5.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D6 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+5) = 'Saturday' then ((@sett_date+5)-1)
                                 when DATENAME (dw, @sett_date+5) = 'Sunday' then ((@sett_date+5)-2)
                                 else @sett_date+5 end) B6
ON A.SECID = B6.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D7 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+6) = 'Saturday' then ((@sett_date+6)-1)
                                 when DATENAME (dw, @sett_date+6) = 'Sunday' then ((@sett_date+6)-2)
                                 else @sett_date+6 end) B7
ON A.SECID = B7.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D8 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+7) = 'Saturday' then ((@sett_date+7)-1)
                                 when DATENAME (dw, @sett_date+7) = 'Sunday' then ((@sett_date+7)-2)
                                 else @sett_date+7 end) B8
ON A.SECID = B8.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D9 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+8) = 'Saturday' then ((@sett_date+8)-1)
                                 when DATENAME (dw, @sett_date+8) = 'Sunday' then ((@sett_date+8)-2)
                                 else @sett_date+8 end) B9
ON A.SECID = B9.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D10 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+9) = 'Saturday' then ((@sett_date+9)-1)
                                 when DATENAME (dw, @sett_date+9) = 'Sunday' then ((@sett_date+9)-2)
                                 else @sett_date+9 end) B10
ON A.SECID = B10.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D11 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+10) = 'Saturday' then ((@sett_date+10)-1)
                                 when DATENAME (dw, @sett_date+10) = 'Sunday' then ((@sett_date+10)-2)
                                 else @sett_date+10 end) B11
ON A.SECID = B11.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D12 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+11) = 'Saturday' then ((@sett_date+11)-1)
                                 when DATENAME (dw, @sett_date+11) = 'Sunday' then ((@sett_date+11)-2)
                                 else @sett_date+11 end) B12
ON A.SECID = B12.SECID



LEFT JOIN (SELECT SECID, (CLPRICE_8)D13 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+12) = 'Saturday' then ((@sett_date+12)-1)
                                 when DATENAME (dw, @sett_date+12) = 'Sunday' then ((@sett_date+12)-2)
                                 else @sett_date+12 end) B13
ON A.SECID = B13.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D14 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+13) = 'Saturday' then ((@sett_date+13)-1)
                                 when DATENAME (dw, @sett_date+13) = 'Sunday' then ((@sett_date+13)-2)
                                 else @sett_date+13 end) B14
ON A.SECID = B14.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D15 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+14) = 'Saturday' then ((@sett_date+14)-1)
                                 when DATENAME (dw, @sett_date+14) = 'Sunday' then ((@sett_date+14)-2)
                                 else @sett_date+14 end) B15
ON A.SECID = B15.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D16 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+15) = 'Saturday' then ((@sett_date+15)-1)
                                 when DATENAME (dw, @sett_date+15) = 'Sunday' then ((@sett_date+15)-2)
                                 else @sett_date+15 end) B16
ON A.SECID = B16.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D17 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+16) = 'Saturday' then ((@sett_date+16)-1)
                                 when DATENAME (dw, @sett_date+16) = 'Sunday' then ((@sett_date+16)-2)
                                 else @sett_date+16 end) B17
ON A.SECID = B17.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D18 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+17) = 'Saturday' then ((@sett_date+17)-1)
                                 when DATENAME (dw, @sett_date+17) = 'Sunday' then ((@sett_date+17)-2)
                                 else @sett_date+17 end) B18
ON A.SECID = B18.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D19 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+18) = 'Saturday' then ((@sett_date+18)-1)
                                 when DATENAME (dw, @sett_date+18) = 'Sunday' then ((@sett_date+18)-2)
                                 else @sett_date+18 end) B19
ON A.SECID = B19.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D20 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+19) = 'Saturday' then ((@sett_date+19)-1)
                                 when DATENAME (dw, @sett_date+19) = 'Sunday' then ((@sett_date+19)-2)
                                 else @sett_date+19 end) B20
ON A.SECID = B20.SECID


---------------------------------


LEFT JOIN (SELECT SECID, (CLPRICE_8)D21 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+20) = 'Saturday' then ((@sett_date+20)-1)
                                 when DATENAME (dw, @sett_date+20) = 'Sunday' then ((@sett_date+20)-2)
                                 else @sett_date+20 end) B21
ON A.SECID = B21.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D22 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+21) = 'Saturday' then ((@sett_date+21)-1)
                                 when DATENAME (dw, @sett_date+21) = 'Sunday' then ((@sett_date+21)-2)
                                 else @sett_date+21 end) B22
ON A.SECID = B22.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D23 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+22) = 'Saturday' then ((@sett_date+22)-1)
                                 when DATENAME (dw, @sett_date+22) = 'Sunday' then ((@sett_date+22)-2)
                                 else @sett_date+22 end) B23
ON A.SECID = B23.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D24 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+23) = 'Saturday' then ((@sett_date+23)-1)
                                 when DATENAME (dw, @sett_date+23) = 'Sunday' then ((@sett_date+23)-2)
                                 else @sett_date+23 end) B24
ON A.SECID = B24.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D25 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+24) = 'Saturday' then ((@sett_date+24)-1)
                                 when DATENAME (dw, @sett_date+24) = 'Sunday' then ((@sett_date+24)-2)
                                 else @sett_date+24 end) B25
ON A.SECID = B25.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D26 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+25) = 'Saturday' then ((@sett_date+25)-1)
                                 when DATENAME (dw, @sett_date+25) = 'Sunday' then ((@sett_date+25)-2)
                                 else @sett_date+25 end) B26
ON A.SECID = B26.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D27 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+26) = 'Saturday' then ((@sett_date+26)-1)
                                 when DATENAME (dw, @sett_date+26) = 'Sunday' then ((@sett_date+26)-2)
                                 else @sett_date+26 end) B27
ON A.SECID = B27.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D28 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+27) = 'Saturday' then ((@sett_date+27)-1)
                                 when DATENAME (dw, @sett_date+27) = 'Sunday' then ((@sett_date+27)-2)
                                 else @sett_date+27 end) B28
ON A.SECID = B28.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D29 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+28) = 'Saturday' then ((@sett_date+28)-1)
                                 when DATENAME (dw, @sett_date+28) = 'Sunday' then ((@sett_date+28)-2)
                                 else @sett_date+28 end) B29
ON A.SECID = B29.SECID

LEFT JOIN (SELECT SECID, (CLPRICE_8)D30 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+29) = 'Saturday' then ((@sett_date+29)-1)
                                 when DATENAME (dw, @sett_date+29) = 'Sunday' then ((@sett_date+29)-2)
                                 else @sett_date+29 end) B30
ON A.SECID = B30.SECID


LEFT JOIN (SELECT SECID, (CLPRICE_8)D31 FROM SECP 
           where EFFDATE = case  when DATENAME (dw, @sett_date+30) = 'Saturday' then ((@sett_date+30)-1)
                                 when DATENAME (dw, @sett_date+30) = 'Sunday' then ((@sett_date+30)-2)
                                 else @sett_date+30 end) B31
ON A.SECID = B31.SECID

I think the difference is in Execution Plans...check execution plans — Arsen Mkrtchyan, Feb 11 '14 at 10:20
Is there any wayu I make both TSQL and Stored Procedure to use similar execution plan. — Chir, Feb 11 '14 at 10:27
Honestly I am not sure. IMHO at first you need to check plans, and try to find difference — Arsen Mkrtchyan, Feb 11 '14 at 10:36
Unfortunately I don't even know how to create let alone compare execution plan..... — Chir, Feb 11 '14 at 10:52
it depends from DBMS you are using, in general there is some button in management studio, just google how to get execution plan for your dbms — Arsen Mkrtchyan, Feb 11 '14 at 10:54

score 2 · Answer 1 · answered Feb 11 '14 at 12:34

Your procedure suffers performance lag due to parameter sniffing. Try re-writting it using local variable for input parameter @sett_date as:

ALTER PROCEDURE [dbo].[SP_SEC_PRICES_FOR_SKP] @sett_date datetime as

declare @v_sett_date datetime;
set @v_sett_date datetime =  @sett_date;

and then use the local variable @v_sett_date throughout your procedure. Advantage of this method is when you assign the paramaters to local variables SQL Server uses statistic densities instead of statistic histograms but disadvantage lies in the fact that some queries will use suboptimal plans because densities are not precise enough as the statistic histogram.

This has worked for me.... thanks a lot everyone for your time and effort.. You guys are the best — Chir, Feb 11 '14 at 12:54

score 1 · Accepted Answer · answered Feb 11 '14 at 10:56

1

This question has been there before and the answer is always the same: Either different SET options or parameter sniffing.

I'm suspicious of set QUOTED_IDENTIFIER ON. Why are you running that statement? Ensure, that the QUOTED_IDENTIFIER setting is always the same for both SSMS and your app. It should probably be ON everywhere. Audit your database by scripting it out using SSMS and doing a text search for "QUOTED_IDENTIFIER OFF". That should not exist.

answered Feb 11 '14 at 10:56

usr

168,620
35
240
369

1

I have just removed (dropped) stored procedure and created a new stored procedure (identical to T-SQL query) with another name, and also removed QUOTED_IDENTIFIER statement but still no joy...... – Chir Feb 11 '14 at 11:48
1

I have tried your suggestion and amended the storeprocedure as following but now all the D1, D2, D3.......D31 are all coming as null – Chir Feb 11 '14 at 12:14
1

copy of stored procedure .......ALTER PROCEDURE[dbo].[SP_SECPRICES_MONTHLY] @LOCAL_sett_date datetime as DECLARE "@sett'_date as datetime" SELECT "@LOCAL_sett_date = @sett_date" SELECT A.SECID, A.PRODTYPE – Chir Feb 11 '14 at 12:19

score 1 · Answer 3 · edited May 23 '17 at 10:25

1

Your procedure can be a victim of Parametter sniffing. This has been answere see: query-runs-fast-but-runs-slow-in-stored-procedure

edited May 23 '17 at 10:25

Community

1
1

answered Feb 11 '14 at 11:50

hrishi

1,531
8
28
43

score 1 · Answer 4 · answered Feb 11 '14 at 12:18

I just changed the way you tried to implement, generate a calendar table first and then made join and PIVOT Data, Try and think this way.. this definitely increase the performance

declare @start datetime
declare @end datetime

set @start = '2013-12-01'  --first Day
SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@start))),DATEADD(mm,1,@start))
SELECT @end=DATEADD(dd,-(DAY(DATEADD(mm,1,@start))),DATEADD(mm,1,@start))    --last Day of month
;with cte as
(
select @start firstday,
             CASE 
                 WHEN Datename (dw, @start) = 'Saturday' 
               THEN ( 
                 @start - 1 ) 
                 WHEN Datename (dw, @start) = 'Sunday' 
               THEN ( 
                 @start - 2 ) 
                 ELSE @start 
             END  WorkingDay
             ,1  [Day]
union all
select firstday + 1,
            CASE 
                 WHEN Datename (dw, firstday + 1) = 'Saturday' 
                 THEN ( 
                 @start - 1 ) 
                 WHEN Datename (dw, firstday + 1) = 'Sunday' 
               THEN ( 
                 @start - 2 ) 
                 ELSE @start 
             END  WorkingDay,[Day] +1   
from cte
where firstday < @end
)

SELECT * INTO #tmpCalendar FROM CTE

SELECT * FROM
(
Select A.secid, 
       A.prodtype, 
       A.ccy,
       sp.clprice_8,'d' + C.[Day] AS [DAY],
       @start, 
       @start + 4, 
       @start + 30 
    FROM   secm A 
    LEFT JOIN secp sp ON SP.secid=A.secid
    INNER JOIN #tmpCalendar C ON effdate=WorkingDay
) Result
PIVOT
(AVG(sp.clprice_8)   FOR [DAY] IN(d1,d2..............,d31)
 )
AS P

Unfortunately..... database compatilbilty levels is set sql 2000, eventhough I am running SQL2005 database. I cant change the compatilbity level for SQL2005 just in case there are issues with other SP's. — Chir, Feb 11 '14 at 12:46

Stored Procedure Takes Longer to Execute then TSQL Query in SQL Server 2005

4 Answers4