Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

SQL Join in Alteryx

JitenderChawla
8 - Asteroid

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 –

  1. Creates a temp table
  2. Inserts data into the temp table
  3. 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.

6 REPLIES 6
jrgo
14 - Magnetar

Hi @JitenderChawla,

 

One thing I've learned with In-DB tools is that it doesn't work great if you initial SQL is already a fairly complex query. This approach kinda defeats the purpose of why these tools were created in the first place. If you already have a custom SQL that work and you want to use it, use it in a normal Input tool.

 

The reason I suspect this is the case with In-DB is because of how these tools create the script for processing on the DB. Most of the time, complex queries will fail and the reason I've seen most common is because the the table names are aliased. This can cause a conflict with the In-DB tools. In-DB takes advantage of CTE's and Alteryx adds it's own alias's to the various CTE's it creates. This is where I suspect the conflict occurs that generate errors, or, in your case, may add inefficiencies to the request.

 

Instead of copy/pasting your SQL script, recreate the logic using the various In-DB tools available to perform the same operations. Take a look at the workflow I attached. While it doesn't completely replicate each operation/join your query does, this will give you an idea of what I'm referring to. You may need to make some adjustments since I don't have access to your data.

 

Hope this helps!

 

Jimmy

 

 

JitenderChawla
8 - Asteroid

Hi @jrgo, Thanks for your response.

 

I currently have Alteryx designer version installed and I am unable to open the workflow you attached. Is there any other way you could provide this workflow so I am able to view it.

 

The issue is not that the queries are not running, The issue is that the query is taking ages to run. I am not sure if removing the aliases will help however will still give it a try.

jrgo
14 - Magnetar

@JitenderChawla

 

Here's a copy using the same version of your original workflow.

JitenderChawla
8 - Asteroid

Hi jrgo.

 

Thanks a bunch for all your help !!

 

Regards,

Jitender

JitenderChawla
8 - Asteroid

Hi Jrgo,

 

The workflow I created works fine however the moments I add the browse in DB to or output tool to capture he data it slows down at the last step and seems like it will continue to run for ages.

 

Regards,

Jitender

BenMoss
ACE Emeritus
ACE Emeritus

The browse in-db tool pulls the data to your local machine which will cause a speed problem as you have identified depending on the amount of data you are trying to pull and your network connection, alongside what type of DB you are using.

 

Ben

Labels