Create #Table VS Select into #Table
-
Hello,
I need to understand something. The "Select Into TEST" portion returns less table scans (and SQL creates a worktable) vs the CREATE TABLE #Table & INSERT #Table Select statement.
Based on the code and stats results below, why does SQL Server treat each method differently?
I am dumbfounded.
--SET STATISTICS IO ON
/******************************************
SELECT INTO TEST
*******************************************/
DBCC FREEPROCCACHE
BEGIN TRAN
PRINT 'SELECT INTO EXTransactionsToRollup'
SELECT DISTINCT
A.FTId,
B.FTIRMSBudgetPeriod,
B.FTIRMSDeptId,
C.BTTTreeLevel AS FTDeptTreeLevel,
C.BTTTreeLevelNum AS FTDeptTreeLevelNum,
'HOCDC' AS FTIRMSSetId,
B.FTIRMSAccountCode,
D.BTTTreeLevel AS FTAcctTreeLevel,
D.BTTTreeLevelNum AS FTAcctTreeLevelNum,
B.FTIRMSFundCode,
E.BTTTreeLevel AS FTFundTreeLevel,
E.BTTTreeLevelNum AS FTFundTreeLevelNum,
B.FTIRMSClassFieldCode,
F.BTTTreeLevel AS FTSCTreeLevel,
F.BTTTreeLevelNum AS FTSCTreeLevelNum,
B.FTIRMSProgramCode,
G.BTTTreeLevel AS FTProgramTreeLevel,
G.BTTTreeLevelNum AS FTProgramTreeLevelNum,
B.FTIRMSProjectCode,
B.FTBudgetDate
INTO dbo.exTransactionstoRollUp
FROM dbo.exFTRollUp A
JOIN dbo.FinancialTransaction B
ON A.FTId = B.FTID
LEFT JOIN dbo.exBudgetTranslationTrees C
ON B.FTIRMSDeptId = C.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees D
ON B.FTIRMSAccountCode = D.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees E
ON B.FTIRMSFundCode = E.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees F
ON B.FTIRMSClassFieldCode = F.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees G
ON B.FTIRMSProgramCode = G.BTTTreeNode
WHERE B.FTFinTransactionTypeCode <> 'BU'
AND B.FTIRMSFundCode not like('F%')
AND C.BTTTreeEffdt = (
SELECT MAX(C1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTSetId
AND C.BTTTreeName = C1.BTTTreename
AND C1.BTTTreeEffdt <= B.FTBudgetdate
)
AND C.BTTTreeName = 'ORG_BT_TREE'
AND D.BTTTreeEffdt = (
SELECT MAX(D1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees D1
WHERE D.BTTSetId = D1.BTTSetId
AND D.BTTTreeName = D1.BTTTreename
AND D1.BTTTreeEffdt <= B.FTBudgetdate
)
AND D.BTTTreeName = 'ACCT_BT_TREE'
AND E.BTTTreeEffdt = (
SELECT MAX(E1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees E1
WHERE E.BTTSetId = E1.BTTSetId
AND E.BTTTreeName = E1.BTTTreename
AND E1.BTTTreeEffdt <= B.FTBudgetdate
)
AND E.BTTTreeName = 'FUND_BT_TREE'
AND F.BTTTreeEffdt = (
SELECT MAX(F1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees F1
WHERE F.BTTSetId = F1.BTTSetId
AND F.BTTTreeName = F1.BTTTreename
AND F1.BTTTreeEffdt <= B.FTBudgetdate
)
AND F.BTTTreeName = 'SUBCL_BT_TREE'
AND G.BTTTreeEffdt = (
SELECT MAX(G1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees G1
WHERE G.BTTSetId = G1.BTTSetId
AND G.BTTTreeName = G1.BTTTreename
AND G1.BTTTreeEffdt <= B.FTBudgetdate
)
AND G.BTTTreeName = 'PROG_BT_TREE'
PRINT 'Update Class Field BudgetRollUpCode' + ' ' + cast(getdate() as varchar(30))
/* Update Class Field BudgetRollUpCode */
SELECT
B.FTId,
B.FTIRMSSetId,
B.FTIRMSClassFieldCode,
A.LEDGER_GROUP,
C.BTTTreeName,
TREE_EFFDT = C.BTTTreeEffdt,
TRANSACTION_TREE_NODE_NUM = C.BTTTreeNodeNum,
ROLL_UP_TREE_LEVEL = CASE WHEN A.Tree_Level_Num <= B.FTSCTreeLevelNum
THEN A.Tree_Level
ELSE B.FTSCTreeLevel
END
INTO #TEMPClassField
FROM exNewTransTranslationRules A
JOIN exTransactionstoRollUp B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON A.FTIRMSSetID = C.BTTSetId
AND A.TREE_NAME = C.BTTTreename
AND B.FTIRMSClassFieldCode = C.BTTTreeNode
AND C.BTTTreeEffdt = (
SELECT MAX(C1.BTTTreeEffdt)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTSetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreename = C1.BTTTreeName
AND C1.BTTTreeEffDt <= A.FTBudgetDate
)
WHERE A.TREE_NAME = 'SUBCL_BT_TREE'
PRINT 'Fill class field code Fields' + ' ' + cast(getdate() as varchar(30))
/* Fill class field code Fields */
UPDATE exRolledUpFinancialTransaction
SET AAClassFieldCode = ISNULL(C.BTTTreeNode,A.FTClassFieldCode)
FROM exRolledUpFinancialTransaction A
JOIN #TempClassField B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON B.FTIRMSSetId = C.BTTSetId
AND B.BTTTreeName = C.BTTTreeName
AND B.TREE_EFFDT = C.BTTTreeEffdt
AND C.BTTTreeNodeNum = (
SELECT MAX(C1.BTTTreeNodeNum)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTsetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreeName = C1.BTTTreeName
AND C.BTTTreeEffdt = C1.BTTTreeEffdt
AND C1.BTTTreeLevel = B.ROLL_UP_TREE_LEVEL
AND C1.BTTTreeNodeNum <= B.TRANSACTION_TREE_NODE_NUM
)
WHERE B.BTTTreeName = 'SUBCL_BT_TREE'
AND B.LEDGER_GROUP = 'APPROP'
UPDATE exRolledUpFinancialTransaction
SET ORClassFieldCode = ISNULL(C.BTTTreeNode,A.FTClassFieldCode)
FROM exRolledUpFinancialTransaction A
JOIN #TempClassField B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON B.FTIRMSSetId = C.BTTSetId
AND B.BTTTreeName = C.BTTTreeName
AND B.TREE_EFFDT = C.BTTTreeEffdt
AND C.BTTTreeNodeNum = (
SELECT MAX(C1.BTTTreeNodeNum)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTsetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreeName = C1.BTTTreeName
AND C.BTTTreeEffdt = C1.BTTTreeEffdt
AND C1.BTTTreeLevel = B.ROLL_UP_TREE_LEVEL
AND C1.BTTTreeNodeNum <= B.TRANSACTION_TREE_NODE_NUM
)
WHERE B.BTTTreeName = 'SUBCL_BT_TREE'
AND B.LEDGER_GROUP = 'ORG'
UPDATE exRolledUpFinancialTransaction
SET PRClassFieldCode = ISNULL(C.BTTTreeNode,A.FTClassFieldCode)
FROM exRolledUpFinancialTransaction A
JOIN #TempClassField B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON B.FTIRMSSetId = C.BTTSETId
AND B.BTTTreeName = C.BTTTreeName
AND B.TREE_EFFDT = C.BTTTreeEffdt
AND C.BTTTreeNodeNum = (
SELECT MAX(C1.BTTTreeNodeNum)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTsetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreeName = C1.BTTTreeName
AND C.BTTTreeEffdt = C1.BTTTreeEffdt
AND C1.BTTTreeLevel = B.ROLL_UP_TREE_LEVEL
AND C1.BTTTreeNodeNum <= B.TRANSACTION_TREE_NODE_NUM
)
WHERE B.BTTTreeName = 'SUBCL_BT_TREE'
AND B.LEDGER_GROUP = 'PROJ_GRT'
PRINT 'DROP TABLE' + ' ' + cast(getdate() as varchar(30))
DROP TABLE #TEMPClassField
--ROLLBACK TRAN
/*
5:25
SELECT INTO EXTransactionsToRollup
(10045 row(s) affected)
Table 'exTransactionstoRollUp'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 287, logical reads 2092, physical reads 0, read-ahead reads 25.
Table 'FinancialTransaction'. Scan count 10123, logical reads 52545, physical reads 955, read-ahead reads 944.
Table 'EXFTRollup'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 18.
Update Class Field BudgetRollUpCode Nov 23 2004 2:31PM
(30135 row(s) affected)
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 56, logical reads 47248, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 2, logical reads 552, physical reads 0, read-ahead reads 0.
Table 'exNewTransTranslationRules'. Scan count 1, logical reads 513, physical reads 2, read-ahead reads 514.
Table 'exTransactionstoRollUp'. Scan count 1, logical reads 191, physical reads 0, read-ahead reads 0.
Fill class field code Fields Nov 23 2004 2:31PM
(10045 row(s) affected)
Table 'EXRolledUpFinancialTransaction'. Scan count 1, logical reads 19336, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 155, logical reads 52116, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 2, logical reads 552, physical reads 0, read-ahead reads 0.
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 1, logical reads 288, physical reads 0, read-ahead reads 0.
(10045 row(s) affected)
Table 'EXRolledUpFinancialTransaction'. Scan count 1, logical reads 19336, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 155, logical reads 52116, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 2, logical reads 552, physical reads 0, read-ahead reads 0.
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 1, logical reads 288, physical reads 0, read-ahead reads 0.
(10045 row(s) affected)
Table 'EXRolledUpFinancialTransaction'. Scan count 1, logical reads 19336, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 155, logical reads 52116, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 2, logical reads 552, physical reads 0, read-ahead reads 0.
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 1, logical reads 288, physical reads 0, read-ahead reads 0.
DROP TABLE Nov 23 2004 2:36PM
*/
/******************************************
CREATE TABLE TEST
*******************************************/
DBCC FREEPROCCACHE
BEGIN TRAN
PRINT 'CREATE TABLES'
IF EXISTS(
SELECT *
FROM sysobjects
WHERE ID = object_id(
N'[dbo].[exTransactionstoRollUp]'
)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
 
DROP TABLE [dbo].[exTransactionstoRollUp]
CREATE TABLE dbo.exTransactionstoRollUp (
[FTId] [int] NOT NULL ,
[FTIRMSBudgetPeriod] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTIRMSDeptId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTDeptTreeLevel] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[FTDeptTreeLevelNum] [smallint] NOT NULL ,
[FTIRMSSetID] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[FTIRMSAccountCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTAcctTreeLevel] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTAcctTreeLevelNum] [smallint] NULL ,
[FTIRMSFundCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTFundTreeLevel] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTFundTreeLevelNum] [smallint] NULL ,
[FTIRMSClassFieldCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTSCTreeLevel] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTSCTreeLevelNum] [smallint] NULL ,
[FTIRMSProgramCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTProgramTreeLevel] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTProgramTreeLevelNum] [smallint] NULL ,
[FTIRMSProjectCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[FTBudgetDate] [datetime] NULL
)
CREATE TABLE dbo.#TEMPClassField (
[FTId] [INT] NOT NULL,
[FTIRMSSetID] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[FTIRMSClassFieldCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[LEDGER_GROUP] [CHAR] (10) NULL,
[BTTTreeName] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[TREE_EFFDT] [datetime] NOT NULL ,
[TRANSACTION_TREE_NODE_NUM] [int] NOT NULL ,
[ROLL_UP_TREE_LEVEL] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL
)
PRINT 'Insert into exTransactionsToRollup'
INSERT INTO
dbo.exTransactionstoRollUp
SELECT DISTINCT
A.FTId,
B.FTIRMSBudgetPeriod,
B.FTIRMSDeptId,
C.BTTTreeLevel AS FTDeptTreeLevel,
C.BTTTreeLevelNum AS FTDeptTreeLevelNum,
'HOCDC' AS FTIRMSSetId,
B.FTIRMSAccountCode,
D.BTTTreeLevel AS FTAcctTreeLevel,
D.BTTTreeLevelNum AS FTAcctTreeLevelNum,
B.FTIRMSFundCode,
E.BTTTreeLevel AS FTFundTreeLevel,
E.BTTTreeLevelNum AS FTFundTreeLevelNum,
B.FTIRMSClassFieldCode,
F.BTTTreeLevel AS FTSCTreeLevel,
F.BTTTreeLevelNum AS FTSCTreeLevelNum,
B.FTIRMSProgramCode,
G.BTTTreeLevel AS FTProgramTreeLevel,
G.BTTTreeLevelNum AS FTProgramTreeLevelNum,
B.FTIRMSProjectCode,
B.FTBudgetDate
FROM dbo.exFTRollUp A
JOIN dbo.FinancialTransaction B
ON A.FTId = B.FTID
LEFT JOIN dbo.exBudgetTranslationTrees C
ON B.FTIRMSDeptId = C.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees D
ON B.FTIRMSAccountCode = D.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees E
ON B.FTIRMSFundCode = E.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees F
ON B.FTIRMSClassFieldCode = F.BTTTreeNode
LEFT JOIN dbo.exBudgetTranslationTrees G
ON B.FTIRMSProgramCode = G.BTTTreeNode
WHERE B.FTFinTransactionTypeCode <> 'BU'
AND B.FTIRMSFundCode not like('F%')
AND C.BTTTreeEffdt = (
SELECT MAX(C1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTSetId
AND C.BTTTreeName = C1.BTTTreename
AND C1.BTTTreeEffdt <= B.FTBudgetdate
)
AND C.BTTTreeName = 'ORG_BT_TREE'
AND D.BTTTreeEffdt = (
SELECT MAX(D1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees D1
WHERE D.BTTSetId = D1.BTTSetId
AND D.BTTTreeName = D1.BTTTreename
AND D1.BTTTreeEffdt <= B.FTBudgetdate
)
AND D.BTTTreeName = 'ACCT_BT_TREE'
AND E.BTTTreeEffdt = (
SELECT MAX(E1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees E1
WHERE E.BTTSetId = E1.BTTSetId
AND E.BTTTreeName = E1.BTTTreename
AND E1.BTTTreeEffdt <= B.FTBudgetdate
)
AND E.BTTTreeName = 'FUND_BT_TREE'
AND F.BTTTreeEffdt = (
SELECT MAX(F1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees F1
WHERE F.BTTSetId = F1.BTTSetId
AND F.BTTTreeName = F1.BTTTreename
AND F1.BTTTreeEffdt <= B.FTBudgetdate
)
AND F.BTTTreeName = 'SUBCL_BT_TREE'
AND G.BTTTreeEffdt = (
SELECT MAX(G1.BTTTreeEffdt)
FROM ExBudgetTranslationTrees G1
WHERE G.BTTSetId = G1.BTTSetId
AND G.BTTTreeName = G1.BTTTreename
AND G1.BTTTreeEffdt <= B.FTBudgetdate
)
AND G.BTTTreeName = 'PROG_BT_TREE'
PRINT 'Update Class Field BudgetRollUpCode' + ' ' + cast(getdate() as varchar(30))
/* Update Class Field BudgetRollUpCode */
INSERT INTO
#TEMPClassField
SELECT
B.FTId,
B.FTIRMSSetId,
B.FTIRMSClassFieldCode,
A.LEDGER_GROUP,
C.BTTTreeName,
TREE_EFFDT = C.BTTTreeEffdt,
TRANSACTION_TREE_NODE_NUM = C.BTTTreeNodeNum,
ROLL_UP_TREE_LEVEL = CASE WHEN A.Tree_Level_Num <= B.FTSCTreeLevelNum
THEN A.Tree_Level
ELSE B.FTSCTreeLevel
END
FROM exNewTransTranslationRules A
JOIN exTransactionstoRollUp B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON A.FTIRMSSetID = C.BTTSetId
AND A.TREE_NAME = C.BTTTreename
AND B.FTIRMSClassFieldCode = C.BTTTreeNode
AND C.BTTTreeEffdt = (
SELECT MAX(C1.BTTTreeEffdt)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTSetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreename = C1.BTTTreeName
AND C1.BTTTreeEffDt <= A.FTBudgetDate
)
WHERE A.TREE_NAME = 'SUBCL_BT_TREE'
PRINT 'Fill class field code Fields' + ' ' + cast(getdate() as varchar(30))
/* Fill class field code Fields */
UPDATE exRolledUpFinancialTransaction
SET AAClassFieldCode = ISNULL(C.BTTTreeNode,A.FTClassFieldCode)
FROM exRolledUpFinancialTransaction A
JOIN #TempClassField B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON B.FTIRMSSetId = C.BTTSetId
AND B.BTTTreeName = C.BTTTreeName
AND B.TREE_EFFDT = C.BTTTreeEffdt
AND C.BTTTreeNodeNum = (
SELECT MAX(C1.BTTTreeNodeNum)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTsetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreeName = C1.BTTTreeName
AND C.BTTTreeEffdt = C1.BTTTreeEffdt
AND C1.BTTTreeLevel = B.ROLL_UP_TREE_LEVEL
AND C1.BTTTreeNodeNum <= B.TRANSACTION_TREE_NODE_NUM
)
WHERE B.BTTTreeName = 'SUBCL_BT_TREE'
AND B.LEDGER_GROUP = 'APPROP'
UPDATE exRolledUpFinancialTransaction
SET ORClassFieldCode = ISNULL(C.BTTTreeNode,A.FTClassFieldCode)
FROM exRolledUpFinancialTransaction A
JOIN #TempClassField B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON B.FTIRMSSetId = C.BTTSetId
AND B.BTTTreeName = C.BTTTreeName
AND B.TREE_EFFDT = C.BTTTreeEffdt
AND C.BTTTreeNodeNum = (
SELECT MAX(C1.BTTTreeNodeNum)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTsetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreeName = C1.BTTTreeName
AND C.BTTTreeEffdt = C1.BTTTreeEffdt
AND C1.BTTTreeLevel = B.ROLL_UP_TREE_LEVEL
AND C1.BTTTreeNodeNum <= B.TRANSACTION_TREE_NODE_NUM
)
WHERE B.BTTTreeName = 'SUBCL_BT_TREE'
AND B.LEDGER_GROUP = 'ORG'
UPDATE exRolledUpFinancialTransaction
SET PRClassFieldCode = ISNULL(C.BTTTreeNode,A.FTClassFieldCode)
FROM exRolledUpFinancialTransaction A
JOIN #TempClassField B
ON A.FTId = B.FTId
JOIN exBudgetTranslationTrees C
ON B.FTIRMSSetId = C.BTTSETId
AND B.BTTTreeName = C.BTTTreeName
AND B.TREE_EFFDT = C.BTTTreeEffdt
AND C.BTTTreeNodeNum = (
SELECT MAX(C1.BTTTreeNodeNum)
FROM exBudgetTranslationTrees C1
WHERE C.BTTSetId = C1.BTTsetId
AND C.BTTSetCNTRLValue = C1.BTTSetCNTRLValue
AND C.BTTTreeName = C1.BTTTreeName
AND C.BTTTreeEffdt = C1.BTTTreeEffdt
AND C1.BTTTreeLevel = B.ROLL_UP_TREE_LEVEL
AND C1.BTTTreeNodeNum <= B.TRANSACTION_TREE_NODE_NUM
)
WHERE B.BTTTreeName = 'SUBCL_BT_TREE'
AND B.LEDGER_GROUP = 'PROJ_GRT'
PRINT 'DROP TABLE' + ' ' + cast(getdate() as varchar(30))
DROP TABLE #TEMPClassField
ROLLBACK TRAN
/*CREATE TABLES
Table 'sysobjects'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Insert into exTransactionsToRollup
(10045 row(s) affected)
Table 'exTransactionstoRollUp'. Scan count 0, logical reads 10046, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 287, logical reads 2092, physical reads 0, read-ahead reads 0.
Table 'FinancialTransaction'. Scan count 10123, logical reads 44313, physical reads 0, read-ahead reads 0.
Table 'EXFTRollup'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0.
Update Class Field BudgetRollUpCode Nov 23 2004 2:37PM
(30135 row(s) affected)
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 0, logical reads 30136, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 30191, logical reads 857864, physical reads 12, read-ahead reads 76.
Table 'exNewTransTranslationRules'. Scan count 1, logical reads 513, physical reads 0, read-ahead reads 0.
Table 'exTransactionstoRollUp'. Scan count 1, logical reads 194, physical reads 0, read-ahead reads 0.
Fill class field code Fields Nov 23 2004 2:37PM
(10045 row(s) affected)
Table 'EXRolledUpFinancialTransaction'. Scan count 10045, logical reads 39473, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 10200, logical reads 9353447, physical reads 0, read-ahead reads 0.
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 1, logical reads 285, physical reads 0, read-ahead reads 0.
Table 'EXRolledUpFinancialTransaction'. Scan count 10045, logical reads 39473, physical reads 0, read-ahead reads 0.
(10045 row(s) affected)
Table 'EXBudgetTranslationTrees'. Scan count 10200, logical reads 9353447, physical reads 0, read-ahead reads 0.
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 1, logical reads 285, physical reads 0, read-ahead reads 0.
(10045 row(s) affected)
Table 'EXRolledUpFinancialTransaction'. Scan count 10045, logical reads 39473, physical reads 0, read-ahead reads 0.
Table 'EXBudgetTranslationTrees'. Scan count 10200, logical reads 9353447, physical reads 0, read-ahead reads 0.
Table '#TEMPClassField_____________________________________________________________________________________________________00000000258D'. Scan count 1, logical reads 285, physical reads 0, read-ahead reads 0.
DROP TABLE Nov 23 2004 2:44PM
7:25
*/
-
This was removed by the editor as SPAM
-
G'day,
It will help all of us to provide a better answer if you can simplify the problem to a smaller test case. Once quick observation I can offer is that you use "distinct" in one query, but not in the other. This observation may or may not be valid because the overall queries may well do the same thing. Simplify this if possible, and we can probably give you a better answer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply