Solved

Recursive Query(CTE) Performing very slow

Posted on 2013-06-20
19
1,028 Views
Last Modified: 2013-06-23
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.
0
Comment
Question by:itgirl777
  • 8
  • 4
  • 3
  • +3
19 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39264970
what indexes exist on those tables?

what are the table definitions?
can we see some sample data?
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 39264981
Check the Execution Plan of the inner queries used in CTE. Create missing indexes.
0
 
LVL 25

Expert Comment

by:jogos
ID: 39265010
Not having the * but having a select list with only the columns that your realy need.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39265162
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
 
LVL 45

Expert Comment

by:Kdo
ID: 39266002
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
 

Author Comment

by:itgirl777
ID: 39266007
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39266050
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
 

Author Comment

by:itgirl777
ID: 39266059
no I did not.What is runstats?What I should run after creating indexs?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39266067
You will need to update the table and index statistics

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

Regards,
   Tomas Helgi
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39266092
How big is the nop_customer table?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39266094
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
 

Author Comment

by:itgirl777
ID: 39266122
@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
 

Author Comment

by:itgirl777
ID: 39266123
@kdo Nop_Customer table having 30976 records.
0
 
LVL 25

Expert Comment

by:jogos
ID: 39266150
<<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
 

Author Comment

by:itgirl777
ID: 39266557
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
 

Author Comment

by:itgirl777
ID: 39270298
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
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 39267364
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
 

Author Closing Comment

by:itgirl777
ID: 39270299
yes this comment help me
0
 

Author Comment

by:itgirl777
ID: 39270303
@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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql date time 14 30
SSIS with VPN COnnection 2 71
INDEX does not make a difference, why? 10 47
T-SQL: New to using transactions 9 23
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question