I have a query in sql to get all recursive Children of Customer
ALTER PROCEDURE [dbo].[Nop_CustomersGet_AllChildren]
With ChildCustomer As
From Nop_Customer c
Where CustomerID = @CustomerID and storeID = @StoreID
From Nop_Customer c2
Inner Join ChildCustomer
On ChildCustomer.CustomerID = c2.[ParentCustomerID]
Select * from ChildCustomer
It constantly taking 1 minute 36 second to get 10,267 records(all children).which is pretty slow.I don't know how should I be able to speed up this.I have no idea other than this query.
Experts please help.I have been working on this all day long.