Solved

Recursive Query(CTE) Performing very slow

Posted on 2013-06-20
19
1,070 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 50
MSSQL join different row from other table 14 65
install sql server management studio 2008 express error 3 26
Not listening to where 1 13
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 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