Recursive Query(CTE) Performing very slow

I have a query in sql to get  all recursive Children of Customer

ALTER PROCEDURE [dbo].[Nop_CustomersGet_AllChildren]
(
	@CustomerID int,
	@StoreID uniqueidentifier
)
AS
BEGIN

	With ChildCustomer As 
( 
Select c.*
From   Nop_Customer c
Where CustomerID = @CustomerID and storeID = @StoreID
Union ALL
Select c2.*
From Nop_Customer c2
   Inner Join ChildCustomer 
        On ChildCustomer.CustomerID = c2.[ParentCustomerID] 
) 
Select * from ChildCustomer 
 
 END

Open in new window



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.
itgirl777Asked:
Who is Participating?
 
jogosCommented:
Thats wath i suggested don't use * where you don't have to.... and you responded it didn't work.
Even in this result when you probably need  only halve of the columns you better take the habbit of not  writing *.
0
 
PortletPaulfreelancerCommented:
what indexes exist on those tables?

what are the table definitions?
can we see some sample data?
0
 
rpkhareCommented:
Check the Execution Plan of the inner queries used in CTE. Create missing indexes.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
jogosCommented:
Not having the * but having a select list with only the columns that your realy need.
0
 
Tomas Helgi JohannssonCommented:
Hi!

Create these three indexes on your tables if you don't already have them

create index custstore_ix on Nop_Customer (CustomerID, storeID);
and
create index custparent_ix on Nop_Customer (ParentCustomerID, CustomerID);
and
create index custid_ix on ChildCustomer (CustomerID) ;

These indexs should speed up your query.

Regards,
    Tomas Helgi
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi itgirl,

Welcome to recursive SQL.  :)

As others have pointed out, make sure that you have the correct indexes on your customer table.  Another concern is the data.  Is CustomerID unique?  Is it unique with a store?  If not, you may want to limit the upper sub-query by filtering out rows where ParentId is non-null, otherwise you'll have duplicates.  The database design and your business rules may also require that the join in the lower sub-query use StoreId as another join key.

You may want to see how deep the recursion goes.

With ChildCustomer As 
( 
  Select c.CustomerID, C.StoreId, C.ParentCustomerId, 1 as Depth
  From   Nop_Customer c
  Where CustomerID = {CustomerID} and storeID = {StoreID}

  Union ALL

  Select c2.CustomerID, C2.StoreId, C2.ParentCustomerId, Depth+1 as Depth
  From Nop_Customer c2
  Inner Join ChildCustomer 
     On ChildCustomer.CustomerID = c2.[ParentCustomerID] 
) 
Select max(Depth) from ChildCustomer;

Open in new window




Kent
0
 
itgirl777Author Commented:
I don't have indexes in my table.I tried to apply index but still it did not speed up the process.Even instead of * I tried to add column name which also did not work.I don't know what's next I should do.
0
 
Tomas Helgi JohannssonCommented:
After creating the indexes did you run runstats on the tables ?
That is crucial to any query to have updated runstats on tables specially if
the tables are highly modified ( many insert,updates and deletes).
Also if your query is in a package you will need to rebind the package after index creation.

Regards,
    Tomas Helgi
0
 
itgirl777Author Commented:
no I did not.What is runstats?What I should run after creating indexs?
0
 
Tomas Helgi JohannssonCommented:
You will need to update the table and index statistics

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Regards,
   Tomas Helgi
0
 
Kent OlsenData Warehouse Architect / DBACommented:
How big is the nop_customer table?
0
 
Tomas Helgi JohannssonCommented:
Try to force the recompile of the stored procedure next time you execute it.
See the manual for more info
http://msdn.microsoft.com/en-us/library/ms181647.aspx

and these links for more info

http://stackoverflow.com/questions/840719/add-sql-server-index-but-how-to-recompile-only-affected-stored-procedures

http://technet.microsoft.com/en-us/library/ms181055%28SQL.90%29.aspx

Regards,
    Tomas Helgi
0
 
itgirl777Author Commented:
@Tomas I did following steps

create index Nop_Customer_ix on Nop_Customer (CustomerID, storeID);
create index  Nop_CustomerParent_ix on Nop_Customer (ParentCustomerID, CustomerID);


UPDATE STATISTICS Nop_Customer

EXEC      @return_value = [dbo].[Nop_CustomersGet_AllChildren]
            @CustomerID = 12345,
            @StoreID = '6789' WITH RECOMPILE

It still take same amount of time.
0
 
itgirl777Author Commented:
@kdo Nop_Customer table having 30976 records.
0
 
jogosCommented:
<<I don't have indexes in my table.I tried to apply index but still it did not speed up the process.Even instead of * I tried to add column name which also did not work.I don't know what's next I should do.>>
1.
The indexes only come to their real value if you also include the columns you need as included columns
2.
What is the maximum recursion dept? (see sql of KDO)
3.
Post the execution plan and we have more grip on where it is spending it's time
0
 
itgirl777Author Commented:
ok finally it executing in 0 second with below query and without index.

ALTER PROCEDURE [dbo].[Nop_CustomersGet_AllChildren]
(
      @CustomerID int,
      @StoreID uniqueidentifier
)
AS
BEGIN

      With ChildCustomer As
(
Select c.CustomerID
From   Nop_Customer c
Where CustomerID = @CustomerID and storeID = @StoreID
Union All
Select c2.CustomerID
From Nop_Customer c2
    Join ChildCustomer
        On ChildCustomer.CustomerID = c2.[ParentCustomerID]
            
)
Select * From Nop_Customer c
 Inner join   ChildCustomer CC ON c.CustomerID = cc.CustomerID

END
0
 
itgirl777Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for itgirl777's comment #a39266557

for the following reason:

this solution worked for me.
0
 
itgirl777Author Commented:
yes this comment help me
0
 
itgirl777Author Commented:
@jogos  I used column names but I added all column names ,than second time I just used only one column name (CustomerID) and it took not even 1 second to execute query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.