I have to inline functions - the first returns about 28 000 rows and the second returns about 16 000 rows. The functions are executed for 0 seconds both. Then, the two functions are joined using inner clause. And the result is query which is executed for 60-70 seconds. Could anyone tell how can I optimized the execution or confirm joining inline functions returning huge results row sets is a bad idea? The only thing I have been able to try was to try different join options (HASH/MERGE) but while on the local machine the hash join as executed for 14 seconds and the merge join for 24 seconds, on the production server the three joins (hash/merge/no hit) were executed for about 20 seconds. So, I believe using this hints can guarantee nothing.
Rewriting the functions as table valued functions is going to kill performance worse than where you are now, so don't. SQL Server is an excellent mechanisms for storing and retrieving structural data. T-SQL is a good enough mechanism for performing set-based operations on data. But, one thing that doesn't work well at all within T-SQL is attempts to treat it like an object oriented language. This happens when people create views or functions and then try to treat them like new tables and join them or nest them within each other. It's not that each of these code units is a discrete piece of work that you can then combine the pieces of work. Instead, with views & inline functions, SQL Server unpacks the queries and tries to identify common objects, objects not referenced, and restructures the queries to satisfy the new thing, the combined query. That may, or may not, go well. The simplification process within the optimizer (where it eliminates unneeded objects) can be somewhat time consuming and lead to optimizer timeouts which generally means you don't have as good an execution plan as you could get. This means, the better approach, rather than try to combine these queries is to write a new query that actually does what you want. I know that violates the core tenants of code reuse, but T-SQL is a scripting language, not a programming language. As such, it was never designed around code reuse like actual programming languages. But, if you insist on attempting to try to tweak the combination of the queries, I'd suggest starting with the execution plan to understand the choices being made. You may just need a new or adjusted index to better satisfy the combined structure. You might be able to apply a query hint or two. But I'm still in favor of just writing a new query.