Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nested cursors Expand / Collapse
Author
Message
Posted Tuesday, January 18, 2005 9:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 26, 2005 8:05 PM
Points: 64, Visits: 1

I use SQL 2000.

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




Post #156149
Posted Tuesday, January 18, 2005 9:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 10, 2010 12:49 PM
Points: 123, 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.
Post #156150
Posted Tuesday, January 18, 2005 9:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 26, 2005 8:05 PM
Points: 64, 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.




Post #156160
Posted Tuesday, January 18, 2005 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 10, 2010 12:49 PM
Points: 123, 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.
Post #156221
Posted Tuesday, January 18, 2005 4:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 26, 2005 8:05 PM
Points: 64, 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:

 




Post #156254
Posted Tuesday, January 18, 2005 4:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 26, 2005 8:05 PM
Points: 64, 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.




Post #156256
Posted Tuesday, January 18, 2005 8:34 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 2,693, Visits: 1,215
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
Post #156265
Posted Wednesday, January 19, 2005 6:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 26, 2005 8:05 PM
Points: 64, 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 .




Post #156359
Posted Wednesday, January 19, 2005 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 6,961, Visits: 7,088

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.

Post #156392
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse