Recursive query optimization in mssql

Question

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!

Answer 1

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.

https://www2.cs.uh.edu/~ordonez/pdfwww/w-2010-TKDE-lrq.pdf

Best regards,

Percy Tang