Tableau Prep is not a tool for writing totally optimized SQL, at present it's more focused on individuals who are blending & preparing data. Each connection step is pulling data down locally into Prep and then the data is manipulated locally.
When you need computation to happen in the database server then at this time you can use the Custom SQL option for the connection. Also if you'd like Tableau Prep to have this feature then I suggest you submit a feature request in the Ideas forum Ideas.
Thanks! But this is Prep! I suppose prep is everything you need in the data extract, cleaning and blending. So simply an optimized SQL should always be part of it. The problem in my situation is that the performance is very bad and time consuming compare to my traditional bi tool. I just thinking to migrate all bi work onto tableau prep, but then this is the bottom neck i faced. It is too slow if the datasource is not connected and optimized with other steps as i can see other local processing is fine. Thanks!
I just found a more specific topic to my question, I am actually asking to add a parameter function to Table prep's custom sql. Better, to allow parameter as a step flow and add to any data source as an parameter input to the sql query. I hope this will happen soon!
This is similar idea: https://community.tableau.com/ideas/8877 , but please don't stop at just custom sql, it should go further and better.