Hi,
below is the SQL Query I am trying to execute.
--Create a temp table ##LoanListBK
Create table ##LoanListBK(MasterSORLoanNum Bigint,AppSysId Bigint,UniqueLoanNum Bigint)
--Insert data into ##LoanListBK
INSERT INTO ##LoanListBK
SELECT DISTINCT RD.MasterSORLoanNum, RD.AppSysId, ULM.UniqueLoanNum
FROM Oversight.SVMSandbox.dbo.BKCH7Consolidated_RawData RD (NOLOCK)
LEFT JOIN Oversight.Lobo.dbo.UniqueLoanNumMapping ULM (NOLOCK)
ON RD.MasterSORLoanNum = ULM.LoanNum
AND RD.AppSysId = ULM.AppSysId
WHERE RD.RepMonth=201804
--Use ##LoanListBK to join to other tables
Select 201804 as RepMonth,a.MasterSORLoanNum,
Case FPC.HeldType when 'SFO' then 0
when 'HFI' then 1
when 'HFS' then 2
when 'REO' then 3
when 'OTA' then 4
else null end heldStatusId,
FSC.FinStatusCdLongDesc,
ACD.BasisOfAcctingCdLongDesc,
PT.GLProdTypeLongDesc
from ##LoanListBK a
Inner join LynxReporting.dbo.factPortCore FPC(NoLock)
on a.UniqueLoanNum = FPC.LoanNum and FPC.SnapshotKey = 20180406 --Change Date to max(SnapshotKey)
Inner Join LynxReporting.dbo.dimFinStatusCd FSC(NoLock)
on FPC.FinStatusCd = FSC.FinStatusCd and FSC.EndDt='2079-01-01'
Inner Join LynxReporting.dbo.dimBasisOfAcctingCd ACD(NoLock)
on FPC.BasisOfAcctingCd = ACD.BasisOfAcctingCd and ACD.EndDt='2079-01-01'
Inner Join LynxReporting.dbo.dimGLProdType PT(NoLock)
on FPC.GLProdType = PT.GLProdType and PT.EndDt='2079-01-01'
The query –
- Creates a temp table
- Inserts data into the temp table
- Uses the temp table in a SQL Join.
SQL takes 20 seconds to execute the code.
I tried to create a workflow in Alteryx however that is taking a lifetime to run, maybe because table LynxReporting.dbo.factPortCore alone has 12MM records. Would appreciate if you could advise me on what I am doing wrong and how can I make the workflow more efficient. Attaching the workflow for your reference.
