May 15, 2009 at 6:38 pm
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
May 15, 2009 at 6:44 pm
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...
May 15, 2009 at 6:48 pm
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]
May 15, 2009 at 6:59 pm
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
May 15, 2009 at 7:08 pm
Hi Jeff Moden
allowed to change spDBSys_GetTableRowKey_sproc ... not many dependencies ...
May 15, 2009 at 7:19 pm
praveenvelumula (5/15/2009)
Hi Jeff Modenallowed 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
May 15, 2009 at 7:22 pm
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
May 15, 2009 at 7:25 pm
Thanks for advice .. Jeff ,
I will work on it .. I think i can do ....if not ...can i get back to you again ..........
May 15, 2009 at 7:34 pm
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 (
[font="Arial Black"]... and none of this should break ANY code.[/font]
--Jeff Moden
May 15, 2009 at 7:50 pm
Actually, I found a bug in spDBSys_GetTableRowKey_sproc... it will never produce an id of 000001...
--Jeff Moden
May 15, 2009 at 7:54 pm
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
May 15, 2009 at 8:45 pm
Nah... "my mistrake"... it's ok.
--Jeff Moden
May 15, 2009 at 9:41 pm
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
May 16, 2009 at 8:23 pm
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
May 16, 2009 at 9:22 pm
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
Viewing 15 posts - 241 through 255 (of 296 total)
You must be logged in to reply to this topic. Login to reply