Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Recursive Query(CTE) Performing very slow

Posted on 2013-06-20
19
Medium Priority
?
1,310 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 49

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 26

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 46

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 26

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 26

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 46

Expert Comment

by:Kent Olsen
ID: 39266092
How big is the nop_customer table?
0
 
LVL 26

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

927 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