Need help in tuning this select query which takes 2hrs

Nainesh 2016-04-27 07:15:13

Please help me tune this query. It takes around 2 hrs to execute.

Environment:
SQL Server 2012 EE SP2

Please let me know if you need any further details.

Edit: Updated text file with some comments.

link text

SQLkiwi 2016-05-01 22:59:15
The function's purpose isn't very clear, and without sample data, input and expected results, it's going to be too time-consuming to attempt here. I suggest you ask a question about converting the scalar function on Stack Overflow or Database Administrators with all the details needed to work the problem through. Cheers.
Nainesh 2016-05-02 05:02:05
Thanks Paul! Appreciate your reply!
SQLkiwi 2016-04-27 10:12:59
Based on the estimated anonymized plan alone, I would say you simply need to get rid of the scalar function. It is even worse than usual because it accesses the database on each call.

If you need help with that, please provide the function definition and create statements for the tables and indexes. This is primarily a plan analysis site, but if the logic is clear, we might be able to suggest a way to rewrite it to avoid the horrible performance characteristics of non-inline functions that access the database.