Terrible performance with basic joins

[dcoelho]

I have a simple query that joins to several tables and when executed directly on SQL Server Management studio, it takes 2 seconds. If I create an empty workflow, place that query in an input tool, it takes 2 hours and 49 minutes to execute. 

Why does Alteryx execute this query 5,000x slower than running it directly? If I break down the query into matching Alteryx components (using Alteryx join tools instead of SQL Left Joins, etc) it runs much faster, but that's so much extra unnecessary work. 

I'm using version 2019.2.5.62427, is this a known issue that was corrected in later releases?

For reference, the query is provided below. It's not a query specific problem though as any queries that involve multiple joins slows down to a crawl. It seems like joins increase the run time exponentially in Alteryx. 

SELECT AUM.carriername,
       AUM.aum,
       CASE
         WHEN oo.ownercrd IS NOT NULL THEN oo.ownercrd
         ELSE o.ownercrd
       END AS OwnerCRD
FROM   tbl_aum_account AS AUM
       LEFT JOIN tbl_client_account AS ca
              ON ca.accountid = AUM.accountid
       LEFT JOIN tbl_client AS c
              ON c.clientid = ca.clientid
       LEFT JOIN tbl_client_owner_location AS o
              ON o.clientid = c.clientid
       LEFT JOIN tbl_client_owner_override AS oo
              ON oo.clientid = c.clientid
       LEFT JOIN tbl_carrier_exclusion AS ce
              ON ce.carrierid = AUM.carrierid
WHERE  AUM.aum != 0
       AND ca.primaryaccountholder = 1
       AND ca.status = 'ACTIVE'
       AND ce.carrierid IS NULL 

[jrgo]

Hi @dcoelho 

I've seen this problem on several occasions and what seems to resolve it is to change the ODBC driver that Alteryx is establishing the connection with. IMO SQL Server Native Client 11.0 works the best.

[javiwood]

Either your statement is handling null incorrectly or your Case statement is not required. 

If X is not null why would you tell it to be itself.