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?
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
WHERE hr_job_category_sub_of = @hr_job_category_id
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, (
WHERE hr_job_category_members.hr_job_category_id = SubCategories.hr_job_category_id
) AS job_count