As the parameters are passed as nvarchar, an SQL query is generated like this:
Now the query can not use the optimal index because the parameter defined as nvarchar
If you change a parameter by hierarchyid get the following:
On a table of 87,371 records in the first query takes 1620 ms and the second query takes 17 ms (whereas a cluster index for hierarchyid field).
For my use, just change the class IsDescendantOfExpression.
And create the class
Thanks for your work helped me a lot, I hope I help my recommendation.
As the parameters are passed as nvarchar, an SQL query is generated like this:
Now the query can not use the optimal index because the parameter defined as nvarchar
If you change a parameter by hierarchyid get the following:
On a table of 87,371 records in the first query takes 1620 ms and the second query takes 17 ms (whereas a cluster index for hierarchyid field).
For my use, just change the class IsDescendantOfExpression.
And create the class
Thanks for your work helped me a lot, I hope I help my recommendation.