Solved

Recursive Query(CTE) Performing very slow

Posted on 2013-06-20
19
1,104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Kent Olsen
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:Kent Olsen
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 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