Input Source File - Shorten Field Length

[CherylWalsh71]

I am connecting to an SQL Server. My dataset is very large (over 33M). I have applied some "WHERE" clauses to made the data set smaller and chosen only fields that I need in the SQL Query.  

Is there a way to change the character size of a field as well?  Some fields are set to 300 Characters but none of the records actually meet this limit - it is more like 50 - 100 characters.

I am looking for a way to speed up the query process and thought this might help.  I know I can limit the characters downstream but was hoping to do it upon loading/input to save time in the initial load.

Thanks

[DanielG]

@CherylWalsh71   Are you using In-Database inputs or regular inputs?  Without knowing much in the way of details, I'd suggest using In-Database tools (not just the inputs) to pare down the information first.  I dont know all the technical lingo (or truthfully understand how it works... 😀 ) but basically the in-database tools use the server to do the heavy lifting on the data while in the in-database tools then you can transition the data to run in Alteryx once it is filtered and cleaned up a bit.

[CherylWalsh71]

I have not tried the In-DB before.  I will test that tomorrow.  Thank you for the suggestion. I will need to read up on what I can do with that.

[DanielG]

They are very cool tools.  I dont use them too often as I dont think I have ever worked with 33 million rows of data before, so processing speed for me is usually a secondary issue.  Good luck!  😊

[DataNath · Accepted solution]

Hey @CherylWalsh71, you could try adding the left() function to restrict the size of your incoming fields. I don't have any massive fields to demonstrate with. However, here's a little demo where I'm just taking the first character of a few fields:

Table as standard:

To try and reduce incoming data a bit I have used a TOP () restriction, which can also just be done by using the 'Record Limit' part of the Input Data configuration. I have also just took the first X characters of each field using Left(<Field>, X), and trimmed the DateTime down to Year:

This solution of course relies on the fact that you want to bring your data into Alteryx. However, as mentioned by @DanielG, you could use the In-DB palette instead (depending on your use case). These tools don't actually stream any data into Alteryx but essentially use your tool configurations to compile a SQL query that is then sent off to the DB when you hit run. In comparison to standard tools, the In-DB palette has reduced options/functionality and isn't as user-friendly/intuitive imo but can be good for performance if you'd otherwise be streaming in large amounts of data.

[CherylWalsh71]

Thank you!!  I am going to try the In_Database first and if that does not work I will try this new method. Thanks for the brief demo. VERY HELPFUL!