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.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Cursor performance drop from 6.5 to 7.0, 2000

Author  Topic  pablofm Starting Member 4 Posts Posted - 2001-03-15 : 07:58:59 Hello. We are currently trying to move an application from SQL Server 6.5 to SQL Server 2000 and we have a problem with the performance of the cursors. The application is a front end to retrieve data from the processes and uses stored procedures and cursors. For example: CREATE PROCEDURE P1 as SET NOCOUNT ON DECLARE C1 CURSOR FOR SELECT a.au_id FROM titleauthor a, sales b WHERE a.title_id = b.title_id GROUP BY a.au_id ORDER BY SUM(qty) DESC DECLARE @author_id varchar(11) DECLARE @name varchar (200) declare @title varchar(100) declare @qty int OPEN C1 FETCH NEXT FROM C1 INTO @author_id WHILE @@FETCH_STATUS = 0 BEGIN DECLARE C2 CURSOR FOR SELECT au_lname + ', ' + au_fname, title, SUM(qty) FROM sales a, titleauthor b, titles c, authors d WHERE a.title_id = b.title_id AND b.title_id = c.title_id AND b.au_id = d.au_id AND b.au_id = @author_id GROUP BY au_lname ,au_fname, title ORDER BY SUM(qty) DESC OPEN C2 FETCH NEXT FROM C2 INTO @name, @title, @qty WHILE @@FETCH_STATUS = 0 BEGIN SELECT @name, @title, @qty FETCH NEXT FROM C2 INTO @name, @title, @qty END CLOSE C2 DEALLOCATE C2 FETCH NEXT FROM C1 INTO @author_id END CLOSE C1 DEALLOCATE C1 ( this is just an example of my procedure) I know it's not the best way to do it (it can be done in one sentence) but the real problem is that in SQL Server 7 or 2000 that procedure having 30000 rows in the table sales executes during 2 minutes and in an equally machine-sized SQL 6.5 it lasts only 10 seconds. Changing the two cursors for a sentence makes improves the response time to 4 seconds in SQL Server 7 and 8 or 9 in SQL Server 6.5. My question is: has anybody seen such a performance drop working with cursors in SQL Server 7 or 2000. Thanks for reading this question and for my bad English. Saludos.