Stored procedure performance Select SQL

Question

I have the following table:

CREATE TABLE [dbo].[_tbHRPro](
    [ideID] [int] IDENTITY(-2147483648,1) NOT NULL,
    [FID] [int] NULL,
    [myDate] [datetime] NULL,
    [myTime] [time](7) NULL,
    [oValue] [decimal](18, 2) NULL,
    [pValue] [decimal](18, 2) NULL,
 CONSTRAINT [PK__tbHRPro] PRIMARY KEY CLUSTERED 
(
    [ideIDID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Then, I have the following stored procedure:

DECLARE @MonthID int = 2;
DECLARE @Year varchar(5) = '2014';
DECLARE @date1 DATE;
DECLARE @date2 DATE;

SET @date1 = DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(CAST(@MonthID AS VARCHAR)+'/01/'+CAST(@Year AS VARCHAR)AS DATE)))
SET @date2 = CAST('01/01/'+CAST(@Year AS VARCHAR)AS DATE)

SELECT 
            a.ideID
            , a.FID
            , b.FC
            , a.myDate
            , a.myTime
            , a.oValue
            , a.pValue 
        INTO 
            #TEMPX
        FROM 
            _tbHRPro a 
            INNER JOIN _FC b ON a.FID = b.FID
        WHERE 
            myDate BETWEEN Dateadd(day, -1, @date2) AND dateadd(day, 1, @date1)

        ;WITH CTE AS
            (
                SELECT 
                    T1.ideID
                    , T1.FID
                    , T1.FC
                    , T1.myDate
                    , T1.myTime
                    , T1.oValue
                    , CASE WHEN T1.oValue = 0 THEN 0 ELSE T1.oValue - T2.oValue END xValue
                    , T1.pValue
                    , CAST(T1.myDate AS datetime) + CAST(T1.myTime as datetime) oDateTime
                    , (CASE WHEN T1.oValue = 0 THEN 0 ELSE T1.oValue - T2.oValue END) - T1.pValue sDel
                FROM 
                    #TEMPX T1
                    LEFT JOIN #TEMPX T2 on T1.FID = T2.FID 
                    AND DATEADD(HOUR, -1, CAST(T1.myDate AS datetime) + CAST(T1.myTime AS datetime)) = CAST(T2.myDate AS datetime) + CAST(T2.myTime AS datetime)
                    LEFT JOIN #TEMPX T3 on T1.FID = T3.FID 
                    AND DATEADD(HOUR, 1, CAST(T1.myDate AS datetime) + CAST(T1.myTime AS datetime)) = CAST(T3.myDate AS datetime) + CAST(T3.myTime AS datetime)
            )
        , CTE2 AS
            (      
                SELECT 
                    T1.ideID
                    , T1.FID
                    , T1.FC
                    , T1.myDate
                    , T1.myTime
                    , T1.oValue
                    , CASE WHEN T1.oValue = 0 then (T2.xValue + T3.xValue) / 2 ELSE T1.xValue END AS xValue 
                    , T1.pValue  
                    , (CASE WHEN T1.oValue = 0 then (T2.xValue + T3.xValue) / 2 ELSE T1.xValue END) - T1.pValue sDel     
                FROM 
                    CTE T1
                    LEFT JOIN CTE T2 ON T1.myDate = T2.myDate 
                    AND T1.FID = T2.FID 
                    AND DATEADD(HOUR, -1, T1.oDateTime)= T2.oDateTime
                    LEFT JOIN CTE T3 on T1.myDate = T3.myDate and T1.FID = T3.FID 
                    AND DATEADD(HOUR, 1, T1.oDateTime)= T3.oDateTime
            )

        SELECT 
            ideID
            , FID
            , FC
            , myDate
            , myTime
            , oValue
            , xValue
            , pValue
            , sDel
        INTO 
            #_vwHRPro
        FROM 
            CTE2
        ORDER BY 
            FC
            , myDate
            , myTime

        ;WITH ff AS 
            (
                SELECT 
                    FID
                    , FC
                    , DATEADD(hh, - 1, DATEADD(hh, DATEPART(hh, myTime), myDate)) AS newDate
                    , xValue
                FROM 
                    #_vwHRPro
            )

        SELECT 
            A.FID1
            , A.FC1
            , A.myDate1
            , A.totxValue1
            , B.FID2
            , B.FC2
            , B.myDate2
            , B.totxValue2
            , C.FID3
            , C.FC3
            , C.myDate3
            , C.totxValue3
        INTO 
            #_vwpm102
        FROM 
            (
                SELECT 
                    FID AS FID1
                    , FC AS FC1
                    , CAST(newDate AS DATE) AS myDate1
                    , SUM(xValue) AS totxValue1
                FROM 
                    ff
                WHERE 
                    (FC LIKE '%1%')
                GROUP BY 
                    FID
                    , FC
                    , CAST(newDate AS DATE)
            ) AS A FULL OUTER JOIN
            (
                SELECT 
                    FID AS FID2
                    , FC AS FC2
                    , CAST(newDate AS DATE) AS myDate2
                    , SUM(xValue) AS totxValue2
                FROM 
                    ff
                WHERE 
                    (FC LIKE '%2%')
                GROUP BY 
                    FID
                    , FC
                    , CAST(newDate AS DATE)
            ) AS B ON A.myDate1 = B.myDate2 
            FULL OUTER JOIN
            (
                SELECT 
                    FID AS FID3
                    , FC AS FC3
                    , CAST(newDate AS DATE) AS myDate3
                    , SUM(xValue) AS totxValue3
                FROM 
                    ff
                WHERE 
                    (FC LIKE '%3%')
                GROUP BY 
                    FID
                    , FC
                    , CAST(newDate AS DATE)
            ) AS C ON A.myDate1 = C.myDate3 


SELECT * FROM #_vwpm102 order by FC1, myDate1

I use temporary table to reduce the load time of data. The data is huge, it has been run since 2001.

The load time when I execute the stored procedure is 32sec.

I tried to run from query windows, it loads also around 30sec. But because it stored on temporary table, the 2nd time I run.

I just need to select the following statement:

SELECT * FROM #_vwpm102 order by FC1, myDate1

It loads so fast, only 1sec.

Does anyone have an idea how to minimize the load time? at least, less then 10sec.

Appreciate your help and idea. Cheers,

Answer 1

To make CTE you have "LEFT JOIN #TEMPX T3" but never use T3 - remove it. There are a lot of columns being put into the temp tables that are never used - kill them.

CAST() are expensive and you are joining on them. I would add a computed column to the _tbHRPro table.

ALTER TABLE _tbHRPro 
Add Column myDateTime As CAST(myDate AS datetime) + CAST(myTime as datetime)

(I bet you will use this for a lot of other things too.)

Then add an index on this computed column:

 CREATE INDEX IDX_tbHRPro_myDateTime ON _tbHRPro(myDateTime)

This will speed things up a lot.

Instead of having columns named FC1, FC2, FC3, myDate1, myDate2, etc. I would have a column called FCType (maybe make this a calculated column on the _FC table.)

ALTER TABLE _FC
Add Column FCType AS CASE WHEN FC LIKE '%1%' THEN 1
CASE WHEN FC LIKE '%2%' THEN 2
CASE WHEN FC LIKE '%3%' THEN 3 END

Then later you can group on this.

Then I think you can simplify the SQL to:

        ;WITH CTE AS
            (
                SELECT 
                    me.ideID
                    ,me.FID
                    ,me.myDateTime
                 ,CASE WHEN me.oValue = 0 or me.oValue - behind.oValue = 0 
THEN 
                 (CASE WHEN behind.oValue = 0 
                       THEN 0 
                       ELSE behind.oValue - behind2.oValue END + 
                 CASE WHEN infront.oValue = 0 
                      THEN 0 
                      ELSE me.oValue - infront.oValue END) END / 2 
                 ELSE .oValue - T2.oValue END xValue
                FROM _tbHRPro me
                LEFT JOIN _tbHRPro behind on me.FID = behind.FID 
                   AND DATEADD(HOUR, -1, me.myDateTime) = behind.myDateTime 
                LEFT JOIN _tbHRPro behind2 on me.FID = behind2.FID 
                   AND DATEADD(HOUR, -2, me.myDateTime) = behind2.myDateTime 
                LEFT JOIN _tbHRPro infront on me.FID = infront.FID 
                   AND DATEADD(HOUR, 1, me.myDateTime) = infront.myDateTime
                WHERE me.myDate 
                BETWEEN Dateadd(day, -1, @date2) AND dateadd(day, 1, @date1)
            )

And then make your result set from this:

select cast(CTE.myDateTime as Date) theDate, 
_FC.FCType, 
_FC.FC,
SUM(CTE.xValue) AS totxValue
from CTE
inner join _FC 
ON CTE.FID = _FC.FID
GROUP BY  cast(CTE.myDateTime as Date)
,_FC.FCType
,_FC.FC

Not really sure what the correct calculation is for xValue, but you get the idea. And I didn't do details like take an hour off to make myDate1.

Answer 2

Without table schema and execution plan people can guess only. here in your query : make some changes in SP

(1) Use recompilation sp to use statistics fullest.
(2) Don`t use function in where condition make a column in temp table and then join that column
(3) FC LIKE '%1%' and FC LIKE '%1%'  is this really necessary avoid these kind of condition.