question

gotqn avatar image
gotqn asked

Inner Join between inline functions is too slow

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.
inline-table-valued-functionjoin hints
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What happens if you put the output from the two functions into temporary tables, and then try the JOIN against those temporary tables? How long does that take? What's the execution plan?
0 Likes 0 ·
The times drops from 60 seconds to 30 seconds. Please, I am not able to use this technique and store results in temporary tables. The only thing I can change is the functions themselves. I can rewrite them to be table-valued but I doubt this would help.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, that's make sense. I am going to try to what you have suggested and thanks again for the detailed explanation.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.