SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested cursors


Nested cursors

Author
Message
thriveni
thriveni
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1

I use SQL 2000.

Is there any restriction in using nested cursors(3 levels) on the same base table ?





Charles Otten
Charles Otten
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 10

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
thriveni
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1

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
Charles Otten
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 10

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
thriveni
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1

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]
  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
thriveni
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1

..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
philcart
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4260 Visits: 1436
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.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
thriveni
thriveni
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 1

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
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9733 Visits: 9764

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search