Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Temporary Storage

Author  Topic 

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-07-12 : 13:01:33
Hi

Im running the below query and its running more than 3 hrs.but still there is no single records found in the table. the source table has
60 million records apprx.

Now my question is where the storage is happening temporarily.I believe it is in temporary database (tempdb).if that's the case how to check the table's from it.

insert into dbo.Dat_MRC_FAGLFLEXB_FY1333333
( Account
, Company
, CostCtr
, ProfitCtr
, Product
, Market
, Format
, Territory
, SourceCode
, RollForwardActivity
, DocType
, ReferenceYear
, Customer
, FilmDimension
, Vendor
, InternalOrder
, TradingPartner
,Currency
, FiscalYr
, Period
, FTCurr
, Turnover
, FISCPER
,Count

)

SELECT Account
, Company
, CostCtr
, ProfitCtr
, Product
, Market
, Format
, Territory
, SourceCode
, RollForwardActivity
, DocType
, ReferenceYear
, Customer
, FilmDimension
, Vendor
, InternalOrder
, TradingPartner
-- , SUM( Currency ) AS CURTYPE -- For testing only comment out in production
, LEFT( CURRUNITforCURTYPE__LocCcy_DocCcy, 3 )
+ CASE SUM( Currency )
WHEN 1
THEN ' DocCcy-s'
WHEN 2
THEN ' LocCcy-s'
WHEN 4
THEN ' GrCcy-s'
WHEN 8
THEN ' GlCcy-s'
WHEN 3
THEN ' LocDocCcy-c'
WHEN 5
THEN ' GrDocCcy-c'
WHEN 6
THEN ' GrLocCcy-c'
WHEN 7
THEN ' GRLocDocCcy-c'
WHEN 9
THEN ' GlDocCcy-c'
WHEN 10
THEN ' GlLocCcy-c'
WHEN 11
THEN ' GlLocDocCcy-c'
ELSE 'Error - SUM( Currency ) = ' + CAST( SUM( Currency ) AS nvarchar(10) ) + ' should never exist as a possible 1,2,3 way sum of the values 1,2,4,8'
END AS Currency
, FiscalYr
, Period
, FTCurr
, Turnover
, FISCPER
-- , CURRUNITforCURTYPE__LocCcy_DocCcy -- For testing only comment out in production
, COUNT(*) AS DuplicateCount -- For testing only but DO NOT comment out in production needed for reconcilliation
FROM (
SELECT
CASE WHEN RACCT = '' OR RACCT IS NULL THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS Account
,CASE WHEN RBUKRS = '' OR RBUKRS IS NULL THEN 'No_CoCode' ELSE 'CO_' + RBUKRS + '' END AS Company
,CASE WHEN RCNTR = '' OR RCNTR IS NULL THEN 'No_CostCenter' ELSE 'CC_' + RCNTR + '' END AS CostCtr
,CASE WHEN PRCTR = '' OR PRCTR IS NULL THEN 'No_ProfitCenter' ELSE 'PC_' + PRCTR + '' END AS ProfitCtr
,CASE WHEN ZZMPMPRD = '' OR ZZMPMPRD IS NULL THEN 'No_Product' ELSE /*'PR_' + */ right(ZZMPMPRD, len(ZZMPMPRD)+1 - patindex('%[^0]%', ZZMPMPRD)) /*+ ''*/ END AS Product
,CASE WHEN ZZMARKET = '' OR ZZMARKET IS NULL THEN 'No_Market' ELSE 'MK_' + ZZMARKET + '' END AS Market
,CASE WHEN ZZFORMAT = '' OR ZZFORMAT IS NULL THEN 'No_Format' ELSE 'FT_' + ZZFORMAT + '' END AS Format
,CASE WHEN ZZTERTRY = '' OR ZZTERTRY IS NULL THEN 'No_Territory' ELSE 'TR_' + ZZTERTRY + '' END AS Territory
,CASE WHEN ZZSRCSYS = '' OR ZZSRCSYS IS NULL THEN 'No_System' ELSE 'SY_' + ZZSRCSYS END AS SourceCode
,CASE WHEN ZZRFACT = '' OR ZZRFACT IS NULL THEN 'No_RollForwardActivity' ELSE 'RA_' + ZZRFACT + '' END AS RollForwardActivity
,CASE WHEN ZZBLART = '' OR ZZBLART IS NULL THEN 'No_DocType' ELSE 'DT_' + ZZBLART END AS DocType
,CASE WHEN STR(ZZREFYR,4,0) = ' 0' OR ZZREFYR IS NULL THEN 'No_ReferenceYear' ELSE 'RY_' + STR(ZZREFYR,4,0) + '' END AS ReferenceYear
,CASE WHEN ZZSPKUNNR = '' OR ZZSPKUNNR IS NULL THEN 'No_Customer' ELSE 'CN_' + ZZSPKUNNR + '' END AS Customer
,CASE WHEN ZZFLMDIM = '' OR ZZFLMDIM IS NULL THEN 'No_FilmDimension' ELSE '' + ZZFLMDIM + '' END AS FilmDimension
,CASE WHEN ZZVENDOR = '' OR ZZVENDOR IS NULL THEN 'No_Vendor' ELSE 'VN_' + ZZVENDOR + '' END AS Vendor
,CASE WHEN ZZAUFNR = '' OR ZZAUFNR IS NULL THEN 'No_InternalOrder' ELSE 'IO_' + ZZAUFNR + '' END AS InternalOrder
,CASE WHEN RASSC = '' OR RASSC IS NULL THEN 'No_TradingPartner' ELSE 'TP_' + right(RASSC, len(RASSC)+1 - patindex('%[^0]%', RASSC)) + '' END AS TradingPartner
,CASE WHEN CURTYPE = '' OR CURTYPE IS NULL THEN NULL ELSE CASE CURTYPE WHEN '00' THEN 1 WHEN '10' THEN 2 WHEN '30' THEN 4 WHEN '60' THEN 8 ELSE 1000 + CURTYPE END END AS Currency
,CASE WHEN CURTYPE = '' OR CURTYPE IS NULL THEN 'No_CurrencyType' ELSE 'USD-GrCcy, ' + CASE CURTYPE WHEN '00' THEN '' WHEN '10' THEN '' WHEN '30' THEN '' WHEN '60' THEN CURRUNIT + '-GlCcy, ' ELSE CURRUNIT + '-' + CURTYPE + '-Ccy, ' END
END + FunctionalCurrency + '-LocCcy, ' + RTCUR + '-DocCcy' AS FTCurr
,CASE WHEN FISCPER = 0 OR FISCPER IS NULL THEN 'No_FiscalYr' ELSE 'FY' + substring(str(FISCPER,7,0),3,2) + '' END AS FiscalYr
,CASE WHEN FISCPER = 0 OR FISCPER IS NULL THEN 'No_Period' WHEN charindex(RIGHT(str(FISCPER,7,0),2),'01020304050607080910111200')=25 THEN 'BegBalance' ELSE substring( 'OctNovDecJanFebMarAprMayJunJulAugSep' , charindex( '-'+RIGHT(str(FISCPER,7,0),2) , '-01
-02-03-04-05-06-07-08-09-10-11-12-00' ), 3 ) END AS Period
,FISCPER
,sum(TURNOVER) AS Turnover
,CURRUNIT + FunctionalCurrency + RTCUR AS CURRUNITforCURTYPE__LocCcy_DocCcy
FROM ( select * from Dat_EAI_FAGLFLEXB WITH (NOLOCK)) q
LEFT OUTER JOIN DimDetailCompanyVw
on RBUKRS = Company
WHERE ( (DEBIT IS NOT NULL AND ABS(DEBIT) >.00000001) OR (CREDIT IS NOT NULL AND ABS(CREDIT) >.00000001) OR (TURNOVER IS NOT NULL AND ABS(TURNOVER) >.00000001))
AND ( CURTYPE = '00' OR CURTYPE = '10' OR CURTYPE = '30' )
AND CURRUNIT IS NOT NULL AND RTCUR IS NOT NULL and CURRUNIT <> '' AND RTCUR <> ''
and fiscper like '2013%'


Please correct me if i'm wrong..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-12 : 14:36:27
Take a look at the queries on this MSDN page, which tells you how to identify the space usage by databases, specific queries etc. http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
Go to Top of Page
   

- Advertisement -