CURSOR - SQL Server Forums

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

Transact-SQL (2000)

CURSOR

Author

Topic

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 17:01:06

Most of you are probably going to ignore this becuase it has to do with cursors but I have to use it becuase I have to do row-oriented operations.

This is probably a stupid question which I think I now the answer but I want to clarify it with the guru's of this site.

Is a static cursor have a better performance gain than a dynamic cursor. I only need a snapshot of the database and am not really sure if it is beneficial to use a static cursor or a dynamic cursor. I am aware that a static cursor will create a temp table and use that. This is where I am unsure as to which is faster.

Any ideas??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 17:03:26

OK, how about posting some code?

You can't make a challenge like "I have to use it becuase I have to do row-oriented operations" and expect us to believe you!

Seriously, let's take a crack at it. You know we're not gonna advise on the static vs. dynamic issue unless we try a set-based solution!

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 17:21:18

To start with. I don't have the option to change the table structures.

What I need to do is take the default values from DefQuoteGrpFld, DefQuoteGrpSym, and DefQuoteGrps and "copy" them to QuoteFlds, QuoteSyms, and Quotes.

The values passed into the Stored Procedure are PID, UID and CID.

Hope this helps.

From what I've deduced I have to use a cursor to insert the values from DefQuoteGrps into Quotes so that I get the QuotesID to use when inserting values into QuoteSyms and QuoteFlds. (Have I lost anyone yet?).

The values retrieved from DefQuoteGrps will be based solely on CID.

Any Suggestions?


CREATE TABLE [dbo].[QuoteFlds] (
	[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[QID] [int] NOT NULL ,
	[Name] [varchar] (20) NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[QuoteSyms] (
	[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[QID] [int] NOT NULL ,
	[NAME] [varchar] (50) NOT NULL 
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Quotes] (
	[QID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[PID] [int] NOT NULL ,
	[NAME] [varchar] (25) NOT NULL ,
	[COUNT] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DefQuoteGrpFlds] (
	[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[QGID] [int] NOT NULL ,
	[NAME] [varchar] (255) NOT NULL 
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[DefQuoteGrpSyms] (
	[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[QGID] [int] NOT NULL ,
	[NAME] [varchar] (10) NOT NULL 
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[DefQuoteGrps] (
	[QGID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
	[CID] [int] NOT NULL ,
	[NAME] [varchar] (255) NOT NULL ,
	[COUNT] [int] NOT NULL 
) ON [PRIMARY]
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 17:33:58

Question: are the QuoteFlds, QuoteSyms, and Quotes tables already populated with rows? In other words, if they're empty, would the identity values from the Def... tables be OK to put into the Quotes tables?

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 17:36:05

Already populated. They will have IDs completely different than those in the def tables.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 17:52:28

Sorry, should've asked you this before. Does the Quotes table have to be continguous in its Identity values (QID)? Let's say that it had "random" values, is that a problem or is that acceptable? (all the QID values would match in the 3 related tables)

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 18:02:28

I believe they must be values generated back from the DB. I know we can do Identity insert but these tables are being replicated out to 5 other SQL boxes and the identity ranges are being controlled with Merge Replication.

I would use random numbers but values can be entered into the three tables at the other boxes and the values will be merged back to the master db.

Plus if we do random numbers wouldnt we have to check to make sure that those numbers werent being used elsewhere (at all 6 machines).

Does that answer your question?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 18:08:52

Yeah, I wasn't really gonna use random numbers, but try and generate them based on the original values.

Not fully up on replication, but when you merge the tables, if a replica had an identity value that was inserted on its end, wouldn't that cause a conflict with the original table if the same value exists in the original? Or are the replicas set up with their own identity ranges? Do you have these ranges (or at least the maximum range values)?

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-14 : 18:42:23

Each of the 6 servers have their own identity ranges but this is controlled by Merge replication and could change so I dont have true access to the current values at the servers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources