December 4, 2007 at 3:20 am
if u know the answer please share it with us:
i have a function declared in it six variable tables in the execution the process takes about 2 minutes.
i did convert the function to a stored procedure and the variable tables into a temp tables and run the execute again and the process completed in 4 seconds.
is this cos of the procedure or the temp tables, i assume it is cos of the temp tables , if so why is it much faster than the variable tables???
..>>..
MobashA
December 4, 2007 at 3:27 am
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.
December 4, 2007 at 3:33 am
Without knowing your code or even how much data your touching, it's difficult to give a full answer but what comes to my mind is the fact that temp tables make use of the statistics from the base tables (where you're data is coming from).
So especially when you have a large number of records temp tables usually perform better. But as I said for a conclusive answer we need more information.
[font="Verdana"]Markus Bohse[/font]
December 4, 2007 at 3:37 am
if thats so why the seconed solution runs faster.
first solution:function+variable tables
seconed solution:procedure+temp tables?
..>>..
MobashA
December 4, 2007 at 3:40 am
this is the function
CREATE FUNCTION dbo.BUD0_F_T_GENEXPDTL( @CATID AS INT,@BUDYEAR AS INT,@EMPID AS NVARCHAR(50),@SORTTYPE INT,@TOTALSORT INT, @VERNUM INT )
--SET @CATID =4
--SET @BUDYEAR =1
--SET @EMPID= 'KAT EMP00000073'
--SET @TOTALSORT = 2
--SET @SORTTYPE=1
RETURNS @FIN TABLE
(
EXPID INT,
DESCSORT INT,
[DESCRIPTION] NVARCHAR(50),
HASPRIVILAGE INT,
Jan DECIMAL(15,5),
Feb DECIMAL(15,5),
Mar DECIMAL(15,5),
Apr DECIMAL(15,5),
May DECIMAL(15,5),
Jun DECIMAL(15,5),
Jul DECIMAL(15,5),
Aug DECIMAL(15,5),
Sep DECIMAL(15,5),
Oct DECIMAL(15,5),
Nov DECIMAL(15,5),
[Dec] DECIMAL(15,5),
TOTAL DECIMAL(15,5),
EXPDESC NVARCHAR(100),
SORT INT
)
AS
BEGIN
-- @SORTTYPE: 1: SORT ALPHABETICALLY - 2: SORT BY EXPENSE ID - 3: SORT BY SORTING FIELD VALUE
-- @TOTALSORT: 1: TOP TOTAL - 2: BOTTOM TOTAL
DECLARE @TEMP TABLE
(
PID INT,
CID INT,
CSTID INT,
EXPID INT,
MAPID INT,
HasAccess INT,
BUDGETED INT,
ACTUAL INT,
SORT INT
)
DECLARE @ACTUAL TABLE
(
EXPID INT,
DESCSORT INT,
[DESCRIPTION] NVARCHAR(50),
HASPRIVILAGE INT,
Jan DECIMAL(15,5),
Feb DECIMAL(15,5),
Mar DECIMAL(15,5),
Apr DECIMAL(15,5),
May DECIMAL(15,5),
Jun DECIMAL(15,5),
Jul DECIMAL(15,5),
Aug DECIMAL(15,5),
Sep DECIMAL(15,5),
Oct DECIMAL(15,5),
Nov DECIMAL(15,5),
[Dec] DECIMAL(15,5) ,
TOTAL INT,
SORT INT
)
DECLARE @BUDGETED TABLE
(
EXPID INT,
DESCSORT INT,
[DESCRIPTION] NVARCHAR(50),
HASPRIVILAGE INT,
Jan DECIMAL(15,5),
Feb DECIMAL(15,5),
Mar DECIMAL(15,5),
Apr DECIMAL(15,5),
May DECIMAL(15,5),
Jun DECIMAL(15,5),
Jul DECIMAL(15,5),
Aug DECIMAL(15,5),
Sep DECIMAL(15,5),
Oct DECIMAL(15,5),
Nov DECIMAL(15,5),
[Dec] DECIMAL(15,5) ,
TOTAL INT,
SORT INT)
DECLARE @VARIANCE TABLE
(
EXPID INT,
DESCSORT INT,
[DESCRIPTION] NVARCHAR(50),
HASPRIVILAGE INT,
Jan DECIMAL(15,5),
Feb DECIMAL(15,5),
Mar DECIMAL(15,5),
Apr DECIMAL(15,5),
May DECIMAL(15,5),
Jun DECIMAL(15,5),
Jul DECIMAL(15,5),
Aug DECIMAL(15,5),
Sep DECIMAL(15,5),
Oct DECIMAL(15,5),
Nov DECIMAL(15,5),
[Dec] DECIMAL(15,5) ,
TOTAL INT,
SORT INT
)
DECLARE @TOTAL TABLE
(
EXPID INT,
DESCSORT INT,
[DESCRIPTION] NVARCHAR(50),
HASPRIVILAGE INT,
Jan DECIMAL(15,5),
Feb DECIMAL(15,5),
Mar DECIMAL(15,5),
Apr DECIMAL(15,5),
May DECIMAL(15,5),
Jun DECIMAL(15,5),
Jul DECIMAL(15,5),
Aug DECIMAL(15,5),
Sep DECIMAL(15,5),
Oct DECIMAL(15,5),
Nov DECIMAL(15,5),
[Dec] DECIMAL(15,5) ,
TOTAL INT,
SORT INT
)
DECLARE @VAR TABLE
(
EXPID INT,
DESCSORT INT,
[DESCRIPTION] NVARCHAR(50),
HASPRIVILAGE INT,
Jan DECIMAL(15,5),
Feb DECIMAL(15,5),
Mar DECIMAL(15,5),
Apr DECIMAL(15,5),
May DECIMAL(15,5),
Jun DECIMAL(15,5),
Jul DECIMAL(15,5),
Aug DECIMAL(15,5),
Sep DECIMAL(15,5),
Oct DECIMAL(15,5),
Nov DECIMAL(15,5),
[Dec] DECIMAL(15,5),
TOTAL DECIMAL(15,5),
EXPDESC NVARCHAR(100),
SORT INT
)
DECLARE @TOTALSORTID INT
DECLARE @TOTALEXPID INT
-----------------------------------FILL @TEMP----------------------------------------------------------------
INSERT INTO @TEMP(PID,CID,CSTID,EXPID,MAPID,HasAccess,BUDGETED,ACTUAL,SORT)
SELECT
BUD0_F_T_PARCLD.PID AS PID,
BUD0_F_T_PARCLD.CID,
BUD0_T_MHD_MAP.CSTID_INN,
BUD0_T_MHD_MAP.EXPID_INN,
BUD0_T_MHD_MAP.MAPID_INN,
BUD0_F_T_GENCSTCNTRTREAUT.HasAccess,
BUD0_F_T_GENEXPTREAUT.BUDGETED,
BUD0_F_T_GENEXPTREAUT.ACTUAL,
BUD0_F_T_GENEXPTREAUT.Sort
FROM
BUD0_F_T_PARCLD(@BUDYEAR) BUD0_F_T_PARCLD INNER JOIN
BUD0_T_MHD_MAP ON BUD0_F_T_PARCLD.CID = BUD0_T_MHD_MAP.CSTID_INN INNER JOIN
BUD0_F_T_GENCSTCNTRTREAUT(@BUDYEAR, @EMPID) BUD0_F_T_GENCSTCNTRTREAUT ON
BUD0_T_MHD_MAP.CSTID_INN = BUD0_F_T_GENCSTCNTRTREAUT.cNodeId INNER JOIN
BUD0_F_T_GENEXPTREAUT(@BUDYEAR, @EMPID) BUD0_F_T_GENEXPTREAUT ON
BUD0_T_MHD_MAP.EXPID_INN = BUD0_F_T_GENEXPTREAUT.cNodeId
WHERE
(BUD0_F_T_PARCLD.PID = @CATID) AND (BUD0_F_T_GENCSTCNTRTREAUT.HasAccess = 1) AND
(BUD0_F_T_GENEXPTREAUT.BUDGETED=1 OR BUD0_F_T_GENEXPTREAUT.ACTUAL=1)
SET @TOTALSORTID = (SELECT (CASE WHEN @TOTALSORT=1 THEN MIN(SORT)-1 WHEN @TOTALSORT=2 THEN MAX(SORT)+1 ELSE 0 END) FROM @TEMP)
SET @TOTALEXPID = (SELECT (CASE WHEN @TOTALSORT=1 THEN -1 WHEN @TOTALSORT=2 THEN 9999999 ELSE -1 END))
---------------------------------FILL @ACTUAL---------------------------------------------------------
INSERT INTO @ACTUAL(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,SORT)
SELECT
A.EXPID,
2 AS DESCSORT,
'ACTUAL' AS [DESCRIPTION],
A.ACTUAL,
SUM(CASE WHEN PERIODID=1 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Jan,
SUM(CASE WHEN PERIODID=2 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Feb,
SUM(CASE WHEN PERIODID=3 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Mar,
SUM(CASE WHEN PERIODID=4 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Apr,
SUM(CASE WHEN PERIODID=5 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS May,
SUM(CASE WHEN PERIODID=6 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Jun,
SUM(CASE WHEN PERIODID=7 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Jul,
SUM(CASE WHEN PERIODID=8 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Aug,
SUM(CASE WHEN PERIODID=9 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Sep,
SUM(CASE WHEN PERIODID=10 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Oct,
SUM(CASE WHEN PERIODID=11 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS Nov,
SUM(CASE WHEN PERIODID=12 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS [Dec],
SUM(CASE WHEN PERIODID=1 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=2 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=3 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=4 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=5 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=6 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=7 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=8 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=9 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=10 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=11 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) +
SUM(CASE WHEN PERIODID=12 AND A.ACTUAL=1 THEN PERDBLNC ELSE 0 END) AS TOTAL,
A.SORT
FROM
@TEMP AS A LEFT OUTER JOIN (
SELECT BB.ACTID_INN, BB.MAPID_INN, BB.ACCOUNTNUMBER_NVN, C.ACTINDX, C.PERIODID, C.PERDBLNC
FROM dbo.BUD0_V_FM1_GL10110 C INNER JOIN
dbo.BUD0_T_MNT_ACT BB ON C.ACTINDX = BB.ACCOUNTNUMBER_NVN
WHERE (C.YEAR1 =
(SELECT YEAR_INN
FROM BUD0_T_MHD_YEAR
WHERE YEARID_INN =@BUDYEAR))
) AS B ON A.MAPID=B.MAPID_INN
GROUP BY
A.EXPID,A.ACTUAL,A.SORT
------------------------------FILL @BUDGETED---------------------------------------------------------------------
INSERT INTO @BUDGETED(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec],TOTAL,SORT )
SELECT
A.EXPID,
1 AS DESCSORT,
'BUDGETED' AS [DESCRIPTION],
A.BUDGETED,
SUM(CASE WHEN C.MONTH_NVN=1 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Jan,
SUM(CASE WHEN C.MONTH_NVN=2 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Feb,
SUM(CASE WHEN C.MONTH_NVN=3 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Mar,
SUM(CASE WHEN C.MONTH_NVN=4 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Apr,
SUM(CASE WHEN C.MONTH_NVN=5 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS May,
SUM(CASE WHEN C.MONTH_NVN=6 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Jun,
SUM(CASE WHEN C.MONTH_NVN=7 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Jul,
SUM(CASE WHEN C.MONTH_NVN=8 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Aug,
SUM(CASE WHEN C.MONTH_NVN=9 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Sep,
SUM(CASE WHEN C.MONTH_NVN=10 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Oct,
SUM(CASE WHEN C.MONTH_NVN=11 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS Nov,
SUM(CASE WHEN C.MONTH_NVN=12 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS [Dec],
SUM(CASE WHEN C.MONTH_NVN=1 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=2 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=3 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=4 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=5 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=6 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=7 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=8 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=9 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=10 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=11 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) +
SUM(CASE WHEN C.MONTH_NVN=12 AND A.BUDGETED=1 THEN C.BUDAMOUNT_REN ELSE 0 END) AS TOTAL,
A.SORT
FROM
@TEMP AS A LEFT OUTER JOIN
(SELECT BB.BUDID_INN, CC.MAPID_INN, BB.TOTALAMOUNT_REN, BB.CURRENTSTATUS_NVN, BB.TYPE_NVN,
CC.MONTH_NVN, CC.BUDAMOUNT_REN,CC.VERSION_INN
FROM dbo.BUD0_T_MDT_BUDHDR BB INNER JOIN
dbo.BUD0_T_MDT_BUDDTL CC ON BB.BUDID_INN = CC.BUDID_INN
WHERE (CC.VERSION_INN=@VERNUM AND BB.CURRENTSTATUS_NVN = 'APPROVED')) AS C ON A.MAPID=C.MAPID_INN
GROUP BY
A.EXPID,A.BUDGETED,A.SORT
--------------------------------FILL @VARIANCE-----------------------------------------------------------------
INSERT INTO @VARIANCE(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec], TOTAL,SORT )
SELECT
A.EXPID,
3 AS DESCSORT,
'VARIANCE' AS [DESCRIPTION],
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE 1 END,0) AS HASPRIVILAGE,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Jan-A.Jan END,0) AS Jan,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Feb-A.Feb END,0) AS Feb,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Mar-A.Mar END,0) AS Mar,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Apr-A.Apr END,0) AS Apr,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.May-A.May END,0) AS May,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Jun-A.Jun END,0) AS Jun,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Jul-A.Jul END,0) AS Jul,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Aug-A.Aug END,0) AS Aug,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Sep-A.Sep END,0) AS Sep,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Oct-A.Oct END,0) AS Oct,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Nov-A.Nov END,0) AS Nov,
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.[Dec]-A.[Dec] END,0) AS [Dec],
ISNULL(CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Jan-A.Jan END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Feb-A.Feb END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Mar-A.Mar END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Apr-A.Apr END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.May-A.May END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Jun-A.Jun END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Jul-A.Jul END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Aug-A.Aug END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Sep-A.Sep END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Oct-A.Oct END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.Nov-A.Nov END +
CASE WHEN A.HASPRIVILAGE=0 OR B.HASPRIVILAGE=0 THEN 0 ELSE B.[Dec]-A.[Dec] END,0) AS TOTAL,
A.SORT
FROM @ACTUAL AS A INNER JOIN @BUDGETED AS B ON A.EXPID=B.EXPID
-------------------------FILL @VAR---------------------------------------------------------------
INSERT INTO @VAR(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,SORT)
SELECT A.EXPID,
4 AS DESCSORT,
'VARIANCE %' AS [DESCRIPTION],
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 ELSE 1 END,0) AS HASPRIVILAGE,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Jan=0 AND B.Jan = 0 THEN NULL WHEN A.Jan =0 THEN 0 ELSE CONVERT(INT,B.Jan/A.Jan*100) END,0) AS Jan,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Feb=0 AND B.Feb = 0 THEN NULL WHEN A.Feb =0 THEN 0 ELSE CONVERT(INT,B.Feb/A.Feb*100) END,0) AS Feb,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Mar=0 AND B.Mar = 0 THEN NULL WHEN A.Mar =0 THEN 0 ELSE CONVERT(INT,B.Mar/A.Mar*100) END,0) AS Mar,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Apr=0 AND B.Apr = 0 THEN NULL WHEN A.Apr =0 THEN 0 ELSE CONVERT(INT,B.Apr/A.Apr*100) END,0) AS Apr,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.May=0 AND B.May = 0 THEN NULL WHEN A.May =0 THEN 0 ELSE CONVERT(INT,B.May/A.May*100) END,0) AS May,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Jun=0 AND B.Jun = 0 THEN NULL WHEN A.Jun =0 THEN 0 ELSE CONVERT(INT,B.Jun/A.Jun*100) END,0) AS Jun,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Jul=0 AND B.Jul = 0 THEN NULL WHEN A.Jul =0 THEN 0 ELSE CONVERT(INT,B.Jul/A.Jul*100) END,0) AS Jul,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Aug=0 AND B.Aug = 0 THEN NULL WHEN A.Aug =0 THEN 0 ELSE CONVERT(INT,B.Aug/A.Aug*100) END,0) AS Aug,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Sep=0 AND B.Sep = 0 THEN NULL WHEN A.Sep =0 THEN 0 ELSE CONVERT(INT,B.Sep/A.Sep*100) END,0) AS Sep,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Oct=0 AND B.Oct = 0 THEN NULL WHEN A.Oct =0 THEN 0 ELSE CONVERT(INT,B.Oct/A.Oct*100) END,0) AS Oct,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Nov=0 AND B.Nov = 0 THEN NULL WHEN A.Nov =0 THEN 0 ELSE CONVERT(INT,B.Nov/A.Nov*100) END,0) AS Nov,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.[Dec]=0 AND B.[Dec] = 0 THEN NULL WHEN A.[Dec] =0 THEN 0 ELSE CONVERT(INT,B.[Dec]/A.[Dec]*100) END,0) AS [Dec],
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.TOTAL=0 AND B.TOTAL = 0 THEN NULL WHEN A.TOTAL =0 THEN 0 ELSE CONVERT(INT,B.TOTAL/A.TOTAL*100) END,0) AS TOTAL,
A.SORT
FROM @BUDGETED AS A INNER JOIN @VARIANCE AS B ON A.EXPID=B.EXPID
UNION
SELECT EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec], TOTAL,SORT FROM @ACTUAL
UNION
SELECT EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec], TOTAL,SORT FROM @BUDGETED
UNION
SELECT EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec], TOTAL,SORT FROM @VARIANCE
UPDATE @VAR
SET EXPDESC = B.EXPENSES_NVN
FROM @VAR AS A INNER JOIN BUD0_T_MHD_EXP AS B ON B.EXSPENSEID_INN = A.EXPID
--------------------------------------------------------------------------------------------
INSERT INTO @TOTAL(EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL)
SELECT
@TOTALEXPID,
DESCSORT,
[DESCRIPTION],
ISNULL(CASE WHEN SUM(CASE WHEN HASPRIVILAGE=1 THEN 1 ELSE 0 END)=COUNT (DISTINCT EXPID) THEN 1 WHEN SUM(CASE WHEN HASPRIVILAGE=1 THEN 1 ELSE 0 END)=0 THEN 0 ELSE 2 END,0) AS HASPRIVILAGE,
ISNULL(SUM(Jan),0) AS Jan,
ISNULL(SUM(Feb),0) AS Feb,
ISNULL(SUM(Mar),0) AS Mar,
ISNULL(SUM(Apr),0) AS Apr,
ISNULL(SUM(May),0) AS May,
ISNULL(SUM(Jun),0) AS Jun,
ISNULL(SUM(Jul),0) AS Jul,
ISNULL(SUM(Aug),0) AS Aug,
ISNULL(SUM(Sep),0) AS Sep,
ISNULL(SUM(Oct),0) AS Oct,
ISNULL(SUM(Nov),0) AS Nov,
ISNULL(SUM([Dec]),0) AS [Dec],
ISNULL(SUM(TOTAL),0) AS TOTAL
FROM @VAR
WHERE DESCSORT IN (1,2,3)
GROUP BY DESCSORT ,[DESCRIPTION]
-----------------------------------------------------------------------------------------------
INSERT INTO @VAR(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,'GRAND TOTAL',@TOTALSORTID FROM @TOTAL
UNION
SELECT
@TOTALEXPID,
4,
'VARIANCE %',
B.HASPRIVILAGE,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Jan=0 AND B.Jan = 0 THEN NULL WHEN A.Jan =0 THEN 0 ELSE CONVERT(INT,B.Jan/A.Jan*100) END,0) AS Jan,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Feb=0 AND B.Feb = 0 THEN NULL WHEN A.Feb =0 THEN 0 ELSE CONVERT(INT,B.Feb/A.Feb*100) END,0) AS Feb,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Mar=0 AND B.Mar = 0 THEN NULL WHEN A.Mar =0 THEN 0 ELSE CONVERT(INT,B.Mar/A.Mar*100) END,0) AS Mar,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Apr=0 AND B.Apr = 0 THEN NULL WHEN A.Apr =0 THEN 0 ELSE CONVERT(INT,B.Apr/A.Apr*100) END,0) AS Apr,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.May=0 AND B.May = 0 THEN NULL WHEN A.May =0 THEN 0 ELSE CONVERT(INT,B.May/A.May*100) END,0) AS May,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Jun=0 AND B.Jun = 0 THEN NULL WHEN A.Jun =0 THEN 0 ELSE CONVERT(INT,B.Jun/A.Jun*100) END,0) AS Jun,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Jul=0 AND B.Jul = 0 THEN NULL WHEN A.Jul =0 THEN 0 ELSE CONVERT(INT,B.Jul/A.Jul*100) END,0) AS Jul,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Aug=0 AND B.Aug = 0 THEN NULL WHEN A.Aug =0 THEN 0 ELSE CONVERT(INT,B.Aug/A.Aug*100) END,0) AS Aug,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Sep=0 AND B.Sep = 0 THEN NULL WHEN A.Sep =0 THEN 0 ELSE CONVERT(INT,B.Sep/A.Sep*100) END,0) AS Sep,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Oct=0 AND B.Oct = 0 THEN NULL WHEN A.Oct =0 THEN 0 ELSE CONVERT(INT,B.Oct/A.Oct*100) END,0) AS Oct,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.Nov=0 AND B.Nov = 0 THEN NULL WHEN A.Nov =0 THEN 0 ELSE CONVERT(INT,B.Nov/A.Nov*100) END,0) AS Nov,
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.[Dec]=0 AND B.[Dec] = 0 THEN NULL WHEN A.[Dec] =0 THEN 0 ELSE CONVERT(INT,B.[Dec]/A.[Dec]*100) END,0) AS [Dec],
ISNULL(CASE WHEN B.HASPRIVILAGE = 0 THEN 0 WHEN A.TOTAL=0 AND B.TOTAL = 0 THEN NULL WHEN A.TOTAL =0 THEN 0 ELSE CONVERT(INT,B.TOTAL/A.TOTAL*100) END,0) AS TOTAL,
'GRAND TOTAL',
@TOTALSORTID
FROM
@TOTAL AS A CROSS JOIN @TOTAL B
WHERE
A.DESCSORT = 1 AND
B.DESCSORT=3
---------------------------------------------------------------------------------------------------------------------------------------------
IF @SORTTYPE= 1 --ALPHABETICAL SORTING
BEGIN
IF @TOTALSORT=1 --TOP TOTAL
BEGIN
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
WHERE SORT = @TOTALSORTID
ORDER BY EXPDESC,DESCSORT
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
WHERE SORT <> @TOTALSORTID
ORDER BY EXPDESC,DESCSORT
END
ELSE IF @TOTALSORT=2 --BOTTOM TOTAL
BEGIN
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
WHERE SORT <> @TOTALSORTID
ORDER BY EXPDESC,DESCSORT
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
WHERE SORT = @TOTALSORTID
ORDER BY EXPDESC,DESCSORT
END
END
ELSE IF @SORTTYPE=2 --EXPENSE ID SORTING
BEGIN
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
ORDER BY EXPID,DESCSORT
END
ELSE IF @SORTTYPE=3 --SORT FIELD SORTING
BEGIN
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
ORDER BY SORT,EXPDESC,DESCSORT
END
ELSE
BEGIN
INSERT INTO @FIN(EXPID ,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT)
SELECT EXPID,DESCSORT ,[DESCRIPTION] ,HASPRIVILAGE, Jan ,Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sep ,Oct ,Nov ,[Dec] ,TOTAL,EXPDESC,SORT FROM @VAR
ORDER BY SORT,EXPDESC,DESCSORT
END
RETURN
END
..>>..
MobashA
December 4, 2007 at 3:44 am
i know it is a litel big one but they give it to me and said make it work better?
..>>..
MobashA
December 4, 2007 at 4:32 am
vyas (12/4/2007)
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.
Not always.
Table variables cannot have indexes on them (other than a primary key) and, more importantly, do not have column statistics kept on them.
The query optimiser has no idea, when a query runs, how many rows are in a table variable. It estimates 1. If there are a lot of rows in the table variable, this can result in a very poor plan and very poor query performance.
Temp tables can have indexes and do have column stats.
Both are created in the tempdb database, both have entries in the tempdb system tables and have space reserved for them in the tempdb data file. Neither will actually be written to disk unless necessary (low memory)
I prefer table variables for small numbers of rows (<50) and temp tables for anything larger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2007 at 4:54 am
the indexes you r talking about, is it automatically built by sql server?
..>>..
MobashA
December 4, 2007 at 4:56 am
b]
the indexes you r talking about, is it automatically built by sql server?
NO, the indexes on a temporary table you have to create yourself. Statistics on the other hand are created automatically by SQL Server.
[font="Verdana"]Markus Bohse[/font]
December 4, 2007 at 5:01 am
one last question
dose it make a difference that in my case using proc instead of func.
..>>..
MobashA
December 4, 2007 at 5:19 am
There are places where table variables are a very bad idea, and the other way around. For example, storing much data in table variables on 2005 is usually a bad idea, even though one would thing that since they are not logged, ... they could speed things up. There is a nice summary on:
http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx.
It has some timings with large data, and shows differences between 2000 and 2005.
Microsoft also describes many differences and advantages/disadvantages on http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b305977&Product=sql2k
Regards,
Andras
December 4, 2007 at 5:28 am
thanks this is perfect.
..>>..
MobashA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply