ccleebelt
asked on
Best way to optimize recursive CTEs? View or Stored Procedure.
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.
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;
ASKER
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?
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 ...
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 ...
ASKER
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....
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_c ategory_su b_of
SubCategories.hr_job_categ ory_id
hr_job_category_members.hr _job_categ ory_id
Hope this helps ...
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_c
SubCategories.hr_job_categ
hr_job_category_members.hr
Hope this helps ...
ASKER
I already have a PK on SubCategories.hr_job_categ ory_id
How do I add an index on hr_job_categories.hr_job_c ategory_su b_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_categ ory_id if it is a CTE?
How do I add an index on hr_job_categories.hr_job_c
How do I put an index on the SubCategories.hr_job_categ
ASKER
Let me clarify - I have a PK on hr_job_categories.hr_job_c ategory_id - NOT on SubCategories.hr_job_categ ory_id
If I were to create the indexes listed above, what type of index should I use?
If I were to create the indexes listed above, what type of index should I use?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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 ...
ASKER
Thanks! I added an index or two and the performance did get better.
Glad I could be of any help and thanks for the grade !
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 ...