Solved

Best way to optimize recursive CTEs?  View or Stored Procedure.

Posted on 2007-11-20
11
743 Views
Last Modified: 2012-06-07
I have a number of queries occuring on an ASP page which use CTE's to return and categorize a category structure.  The categories have sub categories and so on.

Question is - doing this through regular recordsets each time the page is hit (often) is slow and making the processor bounce pretty high on the DB server. Would putting this logic into an SP or view improve the speed significantly?

Please advise.
DECLARE @hr_job_category_id INT;

SET @hr_job_category_id = 0;
 

;WITH SubCategories(hr_job_category_id, hr_job_category_name) AS ( 

  SELECT hr_job_category_id, hr_job_category_name  

  FROM hr_job_categories  

  WHERE hr_job_category_sub_of = @hr_job_category_id 

  UNION ALL  

  SELECT subs.hr_job_category_id, subs.hr_job_category_name  

  FROM hr_job_categories subs  

    JOIN SubCategories parents  

      ON subs.hr_job_category_sub_of = parents.hr_job_category_id  

)

SELECT hr_job_category_id, hr_job_category_name, (

  SELECT COUNT(*)

  FROM hr_job_category_members

  WHERE hr_job_category_members.hr_job_category_id = SubCategories.hr_job_category_id 

) AS job_count

FROM SubCategories;

Open in new window

0
Comment
Question by:ccleebelt
  • 6
  • 5
11 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20324081
My guess - note ! no rock solid proof for this ! - would be that it would make no difference whether you return this data by means of a CTE, View or Stored Procedure.
Problem is probably somewhere in the structure of the table and some investigation of the execution plans might lead to the implementation of some smart indexes that could speed up the whole thing.

So not the form, but the underlying database model might be of interest to you ...

Hope this helps ...
0
 

Author Comment

by:ccleebelt
ID: 20324107
I've read conflicting opinions that putting the query in an SP compliles/saves the execution plan and can improve performance - is that not the case?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20324242
Yes it will ... but that will not be noticeable by one single user hitting that page once.
But true, best way to do most database related actions in of course the stored procedure !

Don't expect any lightning changes in the performance if you go for the stored procedure ..

Hope this helps ...
0
 

Author Comment

by:ccleebelt
ID: 20324256
I m looking for a performance boost on a page which calls this query 20-30 time per second.  Not a single user hitting the single page once....
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20324363
Than you should really go for a stored procedure !
But I still would give the indexes a better chance of giving you the boost you are looking for ...

Good index candidates are:
    hr_job_categories.hr_job_category_sub_of
    SubCategories.hr_job_category_id
    hr_job_category_members.hr_job_category_id

Hope this helps ...
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

Author Comment

by:ccleebelt
ID: 20324400
I already have a PK on SubCategories.hr_job_category_id
 
How do I add an index on hr_job_categories.hr_job_category_sub_of if it is not an identity column or linked to any sort of FK relationship.  

How do I put an index on the SubCategories.hr_job_category_id if it is a CTE?
0
 

Author Comment

by:ccleebelt
ID: 20324535
Let me clarify - I have a PK on hr_job_categories.hr_job_category_id - NOT on SubCategories.hr_job_category_id

If I were to create the indexes listed above, what type of index should I use?  
0
 
LVL 18

Accepted Solution

by:
Yveau earned 500 total points
ID: 20326035
:-) can't put an index on a CTE ... sorry for that.
You can put an index on any (combination of) columns you like, create index like this:

Hope this helps ...

create index ixHR_Job_Categories on dbo.hr_job_categories (hr_job_category_sub_of)

Open in new window

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20326040
Since these are all non-range queries, use nonclustered indexes.
You can create up to ... a lot ! (I thought 250, but it might be that they raised that for SQL 2005) non-clustered indexes on one table. Only one clustered index can be used (that is the physical order of the records)

Hope this helps ...
0
 

Author Closing Comment

by:ccleebelt
ID: 31410210
Thanks!  I added an index or two and the performance did get better.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20330296
Glad I could be of any help and thanks for the grade !
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to identify differences between 2 backup files? 7 31
Add total to a tablix with ReportItem values 15 39
SQL Query stumper 3 37
SQL Server - Slabs 9 38
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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now