Eliminating Cursors

Question

Post reply

Eliminating Cursors

Viewing 15 posts - 241 through 255 (of 296 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Sorry, Praveen... I have another question....

Are you allowed to change spDBSys_GetTableRowKey_sproc so long as it doesn't break any code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 2

I din't mean you to rewrite the store proceudre ... Just i need advice ..

to replace the Cursor ...........which i need to implement for better performance

i need advice and suggestions .. that's it ..

can i implement @table var and While Loop .... to replace the cursor...

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Jeff Moden (5/15/2009)
Praveen,
What version of SQL Server are you using? 2k, 2k5, 2k8?

Oops! I forgot to ask. Dang, I would have fallen hard on that one!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

praveenvelumula (5/15/2009)
I din't mean you to rewrite the store proceudre ... Just i need advice ..
to replace the Cursor ...........which i need to implement for better performance
i need advice and suggestions .. that's it ..
can i implement @table var and While Loop .... to replace the cursor...

Sorry... most folks expect a full rewrite and I wanted to make sure you understood that wasn't going to happen.

Unless you can rewrite spDBSys_GetTableRowKey_sproc to take an "Increment" value, there's no sense in even making any suggestions because that's the driving factor in this whole shabang. I don't mind rewriting that because most people just don't know the "secret" to writing such a proc to prevent deadlocks and correctly use an increment.

Then, I was going to rewrite a section of the big proc to show you how to use that new procedure.

If all you want is advise, then....

1. Rewrite spDBSys_GetTableRowKey_sproc to take an increment or default to "1" if none is provided. It should contain an update that simultaneously updates the counter value in the ID table and the final next ID variable which will then have the increment immediately subtracted to give you the true "next ID". This method will keep the update from causing deadlocks especially when two folks hit "enter" at the same time.

2. Rewrite the main proc to store the same things the cursors are storing in temp tables (you can use "ugh" table variables if you want, but there're a thousand reasons why I don't not the least of which is ease of troubleshooting and testing modifications). In any case, these tables should have an IDENTITY column starting at ZERO and a separate ID column. Remember the rowcount.

3. Call the new next ID proc using the new increment for the number of rows from 2 above. Update the ID column by adding the returned "next ID" to the IDENTITY column. This will give you ALL the ID's for the new rows.

4. Continue the same process for any other tables you may have to insert to... one temp table for each table you need to insert new rows into.

5. Notice that nowhere in the above did I say to start a transaction, yet. You must complete STEP 4 above and ensure that all the data in your temp tables are correctly populated and, NO, there should be ABSOLUTELY NO WHILE LOOPs involved up to this point.

6. Start a transaction, do all the inserts from the temp tables to the final tables, check for errors (rollback if you find any), and commit.

Just to emphasize... NO WHILE LOOPS and you MUST NOT include the "next ID" table in a transaction, ever, or you will get deadlocks someday. On a similar system at work before I fixed things, we had an average of 640 deadlocks per day with spikes to 4000 because of a "next ID" table like this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 5

Hi Jeff Moden

allowed to change spDBSys_GetTableRowKey_sproc ... not many dependencies ...

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

praveenvelumula (5/15/2009)
Hi Jeff Moden
allowed to change spDBSys_GetTableRowKey_sproc ... not many dependencies ...
Praveen

Perfect. I didn't mean to post just a quote and we can't delete posts anymore. SO, take a look at what I wrote in my previous post above. That's where I'm headed with this. Is that ok? I ask because there's really no way around using cursors on this unless we follow that type of plan. As a side benefit, we going to make the possibility of deadlocks simply go away.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

RBarryYoung (5/15/2009)
Jeff Moden (5/15/2009)
Praveen,
What version of SQL Server are you using? 2k, 2k5, 2k8?
Oops! I forgot to ask. Dang, I would have fallen hard on that one!

Heh... "Must look eye."

I was considering using OUTPUT and that's the reason I asked.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 8

Thanks for advice .. Jeff ,

I will work on it .. I think i can do ....if not ...can i get back to you again ..........

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

praveenvelumula (5/15/2009)
Thanks for advice .. Jeff ,
I will work on it .. I think i can do ....if not ...can i get back to you again ..........
Praveen

Absolutely. Let me get you started on the spDBSys_GetTableRowKey_sproc. Add an @Increment INT = 1 variable just after the @LocCode variable in the proc. Then, take special note of the following from Books Online... it's about the only way to update a column in the table AND a variable at the same virtual instant...

UPDATE

{

table_name WITH (

[ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| [font="Arial Black"]@variable = column = expression [/font]} [ ,...n ]
{ { [ FROM {

} [ ,...n ] ]
[ WHERE
] }

[font="Arial Black"]... and none of this should break ANY code.[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Actually, I found a bug in spDBSys_GetTableRowKey_sproc... it will never produce an id of 000001...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Jeff Moden (5/15/2009)
Actually, I found a bug in spDBSys_GetTableRowKey_sproc... it will never produce an id of 000001...

In fact, it never produces a new id of other than 000000 even though next count increments sucessfully.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Nah... "my mistrake"... it's ok.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Since the sproc that updates the sequence table is so very important to eliminating the cursors in other procs, the fact that it has to be super fast, and the fact that we need to write it in such a fashion as to avoid deadlocks in any of the legacy code, I thought I'd throw in a helping hand.

Assuming that your sequence table looks like this...

[font="Courier New"] CREATE TABLE dbo.DBSys_Table_IDs

(

Table_Code CHAR(4),

Location_Code VARCHAR(4),

Year CHAR(4),

Next_Counter INT

)[/font]

... then, we can make a much more streamlined procedure to update the sequence table all while return more possibilities to help set based code AND while allowing current code to operate with NO CHANGES to the calling code...

[font="Courier New"] CREATE PROCEDURE dbo.spDBSys_GetTableRowKey_sproc

@TableCode CHAR(4),

@LocCode VARCHAR(4) = NULL,

@Increment INT = 1,

@TheYear CHAR(4) = NULL OUTPUT,

@LocationCode VARCHAR(4) = NULL OUTPUT,

@FirstCount INT = NULL OUTPUT,

@TableLocCodes VARCHAR(8) = NULL OUTPUT,

@TheNewID CHAR(16) OUTPUT

AS

--===== Supress the auto-display of rowcounts so as not to

-- cause any false errors for GUI's

SET NOCOUNT ON

--===== Presets

SELECT @TheYear = DATENAME(yy, GETDATE()),

@LocationCode = ID

FROM dbo.Locations WHERE Machine_Location = 'Y'

--===== Updating will be the most common thing. Try that first.

-- This will also set the "First count" variable if it works.

UPDATE dbo.DBSys_Table_IDs

SET @FirstCount = Next_Counter = Next_Counter + @Increment

WHERE Table_Code = @TableCode

AND Location_Code = @LocationCode

AND Year = @TheYear

--===== If the update didn't work, it's because we need a new row.

-- This will build a new row and set the first count variable

-- if the update above didn't work.

IF @FirstCount IS NULL

BEGIN

SELECT @FirstCount = @Increment

INSERT INTO dbo.DBSys_Table_IDs

(Table_Code, Location_Code, Year, Next_Counter)

SELECT @TableCode, @LocationCode, @TheYear, @FirstCount

END

--===== This sets up the rest of the output variables

SELECT @FirstCount = @FirstCount - @Increment,

@TableLocCodes = @LocationCode + @TheYear,

@TheNewID = @TableLocCodes + RIGHT('00000000' + CAST(@FirstCount AS VARCHAR(8)),8)[/font]

... and, we can demo both the "old way" and the "new way with an increment" of calling the proc...

[font="Courier New"]--===== Demonstrate that the "old way" still works

DECLARE @TheNewID CHAR(16)

EXEC dbo.spDBSys_GetTableRowKey_sproc @TableCode = 'SYEL', @TheNewID = @TheNewID OUTPUT

SELECT @TheNewID AS TheNewID

GO

--===== Demonstrate that an increment works and that we have more to play with now.

DECLARE @TableCode CHAR(4),

@Increment INT,

@TheYear CHAR(4),

@LocationCode VARCHAR(4),

@FirstCount INT,

@TableLocCodes VARCHAR(8),

@TheNewID CHAR(16)

EXEC dbo.spDBSys_GetTableRowKey_sproc

@TableCode = 'SYEL',

@Increment = 10,

@TheYear = @TheYear OUTPUT,

@LocationCode = @LocationCode OUTPUT,

@FirstCount = @FirstCount OUTPUT,

@TableLocCodes = @TableLocCodes OUTPUT,

@TheNewID = @TheNewID OUTPUT

SELECT @TheYear AS TheYear,

@LocationCode AS LocationCode,

@FirstCount AS FirstCount,

@TableLocCodes AS TableLocCodes,

@TheNewID AS TheNewID

[/font]

Let us know if you run into anything else.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 14

praveenvelumula (5/15/2009)
Here is the Code .. Please Advise ...

I guess it would be easier if you'd provide the requirements to this code. Describe what this code actually should do.

Such a long code almost always indicates errors in database structure and the solution design.

It's more effective revise not the code but the approach.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 15

Heh... with what Sergiy said in mind... I've always been distraught over the idea of restarting serialization every year and the whole idea of using a sequence table to begin with. It just doesn't seem to be necessary even in the face of replication especially considering the range of values available using things like BIGINT. I also believe that composite serialization should be done using separate columns instead of storing 3 different references in the same column which pretty much blows away the idea of normalized data which also forces people into the bad choice of having to use cursors to get around a problem which should not have existed to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)