Forum Discussion
Recursive query optimization in mssql
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!
