Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-09 : 09:43:20
|
Cory writes "Can you supply us lowly SQL novices with some techniques on how to optimize stored procedures that use cursors? I've been working on a stored procedure in SQL Server 6.5 for a while now, and the only solution that I've been able to find is to use a cursor. I've heard that cursors are bad for performance, and yes I do find performance is slow in my web application because of the stored procedure.
Just to give you an idea of what I'm doing, I'm trying to retrieve a list of categories that have content underneath them that is scheduled between two dates. My stored procedure is meant to report only the categories that have content scheduled. Also, the stored procedure works with a heirarchy of categories as well so that categories are only returned if children categories have content.
Here's the stored procedure:
CREATE PROCEDURE cat_get_child_w_content2
(@passed_catid INT, @pid varchar(10), @currdate datetime)
AS
SET NOCOUNT ON
declare @lookup_row int
declare @tempcatid int
declare @parent_id int
CREATE TABLE #Temp2
(category_id int, category_name varchar(10))
CREATE TABLE #Temp3
(category_id int)
INSERT INTO #Temp3 (category_id)
SELECT DISTINCT category_id
FROM im_schedule
WHERE pid = @pid
AND (@currdate BETWEEN start_date AND end_date)
declare current_get_cur CURSOR FOR
SELECT category_id FROM #Temp3
open current_get_cur
FETCH NEXT FROM current_get_cur into @tempcatid
while (@@fetch_status = 0)
begin
SELECT @parent_id = parent_id FROM im_categories_structure WHERE catid = @tempcatid
if Exists(SELECT category_id FROM #Temp3 WHERE category_id = @parent_id)
FETCH NEXT FROM current_get_cur INTO @tempcatid
else
begin
INSERT INTO #Temp3 (category_id)
VALUES (@parent_id)
FETCH NEXT FROM current_get_cur INTO @tempcatid
end
end
SET NOCOUNT OFF
SELECT DISTINCT im_categories.catid category_id, im_categories.name category_name
FROM im_categories, im_categories_structure, #Temp3
WHERE im_categories_structure.parent_id = @passed_catid
AND im_categories_structure.catid = im_categories.catid
AND im_categories_structure.catid = #Temp3.category_id
ORDER BY category_name ASC
close current_get_cur
deallocate current_get_cur
return Thanks guru!
Cory" |
|
|
|
|
|
Techniques on optimizing stored procedures with cursors