Recursive query optimization in mssql

Yohan 0 Reputation points

Hi, I am fairly new to using MSSQL in general and am currently having trouble optimizing my recursive query. Here is a list of approaches I have tried:

Parallel Execution:

  • Since it is recursive and relies on previous data to generate the next set of results, MSSQL doesn't perform well with parallel execution.

Using a Temp Table and While Loop:

  • This approach proved to be slower than my current recursive query, which uses a CTE (Common Table Expression) with the WITH...AS...SELECT... syntax.

Using a View:

  • For some reason, my boss is opposed to using views.

Batch Processing:

  • This approach was somewhat successful, but I had to rely on an application to implement the async method.

Given that I may not be able to optimize the query any further, is there any other way to improve the performance of the recursive query? I have searched extensively on the internet but haven't found any additional solutions. I hope my description makes sense.

Thank you!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,567 questions
{count} votes

1 answer

Sort by: Most helpful
  1. PercyTang-MSFT 11,806 Reputation points Microsoft Vendor

    Hi @Yohan

    Despite the many ways you have come up with, there is still too little information for us.

    I can only make some general suggestions.

    Do you try using an index on the columns that are involved in the recursion?

    Or use a query hint such as OPTION (MAXRECURSION n) to specify the maximum number of recursions.

    If you want to delve further into how to optimize recursive queries, you can check out this paper.

    Best regards,

    Percy Tang

    0 comments No comments