Recursive query optimization in mssql

Copper Contributor

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:

  1. 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.
  2. 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.
  3. Using a View:

    • For some reason, my boss is opposed to using views.
  4. 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!

1 Reply