Alteryx going idle running SQL input

[RTHA]

I have an issue running a basic SQL query.

SELECT 
    	        PT.parkeertransactienummer   					
		,PG.parkeergarageomschrijving 					
		,PT.inrijdmoment              					
		,PT.uitrijdmoment             					
		,PT.parkeertijdinminuten      					
		--,BT.nettoomzet              					
		--,PA.omschrijving            					
		--,PRK.Booking_Reference       							
FROM 	
                parkeren_feiten_parkeertransactieskpk PT
		LEFT JOIN Parkeren_Dim_Parkeergarages PG                       ON PT.ParkeergarageCode = PG.ParkeergarageCode
		--LEFT JOIN Parkeren_Feiten_Betaaltransacties BT                 ON PT.Parkeertransactienummer = BT.ParkeerTransactieNummer
		--LEFT JOIN Parkeren_Feiten_BetaaltransactiesWaardekaarten BW  ON BT.BetalingstransactieCode = BW.BetalingstransactieCode
		--LEFT JOIN Parkeren_Dim_Parkeer_Artikelen PA                  ON BW.Artikelnummer = PA.Artikelnummer
		--LEFT JOIN Parkeren_Feiten_Reserveringen_koppeling PRK        ON PT.Parkeertransactienummer = PRK.Parkeertransactienummer
WHERE
		1=1
		AND PT.parkeertijdinminuten >= 600
		AND YEAR(PT.uitrijdmoment) = 2017

This runs fine.

 But when I add either of the two bold/italic/blue parts and press "run", Alteryx won't run the flow.

It starts, but gets stuck indefinately on:

	Input Data (1)	Alias translated to odbc:DRIVER={SQL Server Native Client 11.0};DATABASE=RTM-DWH;SERVER=rtm-fa01;Trusted_Connection=yes|||SELECT ¶    	PT.parkeertransactienummer   					¶		,PG.parkeergarageomschrijving 					¶		,PT.inrijdmoment              					¶		,PT.uitrijdmoment             					¶		,PT.parkeertijdinminuten      					¶		--,BT.nettoomzet              					¶		--,PA.omschrijving            					¶		,PRK.Booking_Reference       							¶FROM 	parkeren_feiten_parkeertransactieskpk PT¶		LEFT JOIN Parkeren_Dim_Parkeergarages PG ON PT.ParkeergarageCode = PG.ParkeergarageCode¶		--LEFT JOIN Parkeren_Feiten_Betaaltransacties BT ON PT.Parkeertransactienummer = BT.ParkeerTransactieNummer¶		--LEFT JOIN Parkeren_Feiten_BetaaltransactiesWaardekaarten BW ON BT.BetalingstransactieCode = BW.BetalingstransactieCode¶		--LEFT JOIN Parkeren_Dim_Parkeer_Artikelen PA ON BW.Artikelnummer = PA.Artikelnummer¶		LEFT JOIN Parkeren_Feiten_Reserveringen_koppeling PRK ON PT.Parkeertransactienummer = PRK.Parkeertransactienummer¶WHERE¶		1=1¶		AND PT.parkeertijdinminuten >= 600¶		AND YEAR(PT.uitrijdmoment) = 2017
	Input Data (1)	ODBC Driver version: 03.80

The percentage indicator on input data stays at 0% and no percentage indicator ever appears on browse.

I don't got an eror, Alteryx doesn't freeze and the workflow itself gets locked like it always does when it runs. I can open and run a workflow in a another tab (from the same DB even if I want to).

I can leave the workflow running for an hour and it wont do anything. It just stays in run state in an idle state. And the log file will just show the same as the workflow messages I posted above so no help there... 

Obviously, within Tableau or VSC the same query runs like it should without any issues.

I'm running Alteryx Designer 2018.2.5.48994. Does anyone have an idea?

Marc

[CharlieS]

Are there transactions/locks on those blue tables that Alteryx could be waiting on? Maybe try adding "with (nolock)" after each table specification and checking the "Read Uncommitted" box on the Input Tool. 

[Jim7]

I've run into this same issue a few times and it's extremely frustrating. I've had workflows run for over 7 hours, waiting for the SQL to process before finally timing out. Once the SQL runs, everything else in the workflow is lightning fast.

And it's inconsistent. Sometimes the identical SQL will take forever to run, and other times it runs in under a minute. When this happens now, I try to simplify the SQL to pull in parts of the data I need, then join everything together later in the workflow.

[RTHA]

I tried it just to be sure but it didin't work unfortunately. It's not a live transactional DB so it shouldn't make a difference.

I've run this query/these joins before with no problem. And running the table that creates the problem on it's own works without issues. I really feel like it's a 2018.x issue and will try it in 11.7 when I have the time.

I hope it's either user error on my part or a fixable issue...

[Jim7]

Maybe I'm blind, but where is the "Read Uncommitted" option on the Input tool? Is this only available on Alteryx server? And can you explain specifically what it does?

[newuser980345483]

Hi - I am experiencing something similar. I have left my workflow to run for an hour+ and it doesn't budge from 0%, but as you mention, Alteryx is not frozen. 

I should add then when I run this query elsewhere, it takes about 5 seconds and only returns 94,000 rows of data. That doesn't seem like a lot to cause such a problem with the workflow. 

Did you ever find a solution to this? 

[jcondon]

I also run into this situation often when my SQL queries join a 'certain' number of tables together or even when referencing a view that is complex.  I also find these queries run really fast in SSMS/Tableau.

One of my SQL server guys monitored the SQL that Alteryx throws at the SQL server and we hypothesized that the way Alteryx wraps the SQL query was causing a loop of some kind.

This is from the thread I started a while back: Same result.  Looking at the SQL Profiler, the query that Alteryx is throwing against the SQL server is sp_cursorfetch over and over and gets stuck on.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Differences-in-Performance-between-SQL...

There was a proposed solution which I thought worked, but it only works sometimes.

[dimsandwich]

Hi all,

Replying to an ancient thread but hope it helps someone as I couldn't find a more authoritative solution elsewhere.

I had this problem and (totally by accident) found a solution.

I just add select 1 at the end of the SQL.

Like this:

Yes.  Seriously.

I had a few queries with many joins which would run fine in seconds on an IDE but in Alteryx would just unpredictably hang the way you describe.

By following the main query with the "select 1" statement, they run consistently fine and it doesn't appear to affect the output from the tool.

This is on a Microsoft SQL Server DB.  Not sure if it works on other flavours of DB or what the equivalent syntax would be for Oracle or others.

Hope it helps.

[jmcconnell]

This absolutely worked for me,  no clue why or how but adding select 1 to the end of my query immediately resolved the issue.  Thank you.