Nested cursors

Question

Nested cursors

thriveni

Ten Centuries

Points: 1104
More actions
January 18, 2005 at 9:21 am

#60046

I use SQL 2000.
Is there any restriction in using nested cursors(3 levels) on the same base table ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Charles Otten Default port Points: 1475 More actions · Answer 1

There isn't a limitation, but why on earth would you do it? What are you trying to achieve? This would be a performance killer. Also, be aware your cursors will probably need to be static or else you'll end up stuck in the fetching loop.

Seriously though, cursors are bad. I rewrote our MRP routine (cursor based) to a VB 6 application and the 50 minute routine now takes approximately 8 minutes.

If the phone doesn't ring...It's me.

thriveni Ten Centuries Points: 1104 More actions · Answer 2

I have a front end ACCESS program that transfers a table to SQL server and then based on the table values it has to update multiple tables. The multiple tables' names are based off of a field value in this transferred table , hence the need for a cursor to locate the table names and then a nested cursor to actually update that table. Does that make sense ? Is there any better way to do this ?

Any help will be appreciated.

Charles Otten Default port Points: 1475 More actions · Answer 3

To provide real assistance I'll need a general idea of the table structure transferred from Access and I have a feeling the solution that will work in the end is a rewrite of the Access front end process, but I'll hold off on that.

If the phone doesn't ring...It's me.

thriveni Ten Centuries Points: 1104 More actions · Answer 4

Hello Charles:

Thanks for your offer to help.

My table structure is :

CREATE TABLE [tbl_group] (

[StaffID] [int] NULL ,

[TrackOrderID] [int] NULL ,

[Qualifier] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[LSeries] [int] NOT NULL ,

[LSeriesunit] [int] NOT NULL ,

[Image_Filename] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Display_Image] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Page] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Sort_Order] [int] NULL ,

[URL_Path] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[File_Size_Bytes] [int] NULL ,

[Daily_RHD_No] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Daily_Date_Transferred] [smalldatetime] NULL ,

[Archival_RHD_No] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Archival_Date_Transferred] [smalldatetime] NULL ,

[Storage_Array_Transfer] [smalldatetime] NULL ,

CONSTRAINT [PK_tbl_group] PRIMARY KEY CLUSTERED

(

[Qualifier],

[LSeries],

[LSeriesunit],

[Image_Filename]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]

The ACCESS program does the following:

1. The data in this table is tranferred to SQL server .

2. a stored procedure from SQL server is called that does the following:

thriveni Ten Centuries Points: 1104 More actions · Answer 5

..continued ( sorry pressed the wrong key )

A stored procedure from SQL server is called that does the following:

a. creates a cursor that determines the distinct values for field LSeries in tbl_group

b. if we call the distinct value as YYY, the procedure looks up table tblfilename_YYY and update all the matching rows from tbl_group to this table.

The problem arises when there is more than one distinct value of Lseries, which means when more than one table has to be update, the procedure times out even of there are 10,000 rows. But if the procedure has only one discitnct value of Lseries it can go on for even 150,000 records.

Hence I am trying to re-write to code diferently !!

Thanks again for your help.

philcart SSC-Forever Points: 47794 More actions · Answer 6

Ugg ... "The problem arises when there is more than one distinct value" ... then this wouldn't be a distinct value...

Semantics aside, if you have to nest cursors then I'd be saying go back to the drawing board and re-think the design. Sounds like someone went off on the wrong tangent.

If thats not likely then I wouldn't be expecting too much from your nested cursor routine.

--------------------
Colt 45 - the original point and click interface

thriveni Ten Centuries Points: 1104 More actions · Answer 7

Please refer to point 'a' of my last reply.

The table can have more than one distinct value of Lseries, and that is allowed. For eg, in a group of 10,000 records in the table there could be 4 distinct values of Lseries.

I guess, I defintely will have to re-design .

David Burrows SSC Guru Points: 65142 More actions · Answer 8

I presume that LSeries can be any value or is it as per your example limited to 999?

But why a nested cursor!

One cursor to get distinct LSeries and do a set based update to update target table. Unfortunately this would involve using dynamic sql ( yes I know to be avoided at all costs but... )

if the LSeries is limited to 999 say then create 999 procs to do each table update and then one cursor to get distinct LSeries and EXEC the relevant proc

to do similar to above with cursor you could put distinct LSeries into temp table, select top one, do update, delete the one selected and loop back round until no rows left

Far away is close at hand in the images of elsewhere.
Anon.