February 9, 2011 at 11:29 am
I agree that the UI layer should be passing the correct combination, and there is a problem if it is not. From a defensive programming standpoint, you have created a procedure that will not operate correctly if bad parameters are passed, which leaves the system open to problems. I suggest you have two choices.
Option 1 is to exclude the PersonID from the parameters. The procedure doesn't need the UI to pass it because the value can be looked up from the ToDoID. Without the parameter, there is no possibility that the UI will pass the incorrect value. You have eliminated a possible source of error.
Option 2 is to validate the PersonID against the value in the ToDoList table. If the UI passes the incorrect value for some reason, you want the database to pass that error back to the UI so the developer knows that incorrect values were passed and can fix the UI. A junior programmer may have made a mistake, but will never see it because he never gets feedback that the mistake was made. On top of that, the procedure will either not perform the intended action or will perform an incorrect action based on bad parameters.
I would choose Option 1, which eliminates a source of error and eliminiates the need to validate a parameter.
February 9, 2011 at 12:39 pm
Hi Jonathan,
Unfortunately the two systems that this is implemented in are seriously high volume OLTP systems and so the extra cost of defensive programming has been vetoed - plus we have no junior developers! I do take your point though and I would probably go for a option 3 (a new option similar to your option 1 but one that doesn't require an additional lookup): this would be to check the @@ROWCOUNT after the update and raise an error if it was zero - this would tell the caller that there had been some kind of issue without incurring too much extra overhead. Tables with sequences like this are generally very active, so the least time taken up with sequencing the better - hence the complicated "all in one hit" update statement!
This has given me food for thought though, as in future articles I will now include some defensive programming, with a comment that if performance is paramount then this can be removed.
Thanks again for taking the time to comment on this.
Cheers, James
James
MCM [@TheSQLPimp]
February 10, 2011 at 8:30 am
The main idea of keeping everything in sequence is ofcourse always a performance issue as changing one record implicitly means an update to all following records, which is implicitly expensive and more so for larger tables.
To minimize the cost, I see two options:
* Keep the sequence numbers in a separate table with a 1 to 1 relationship to the main table. So there is a second table with the same PK as for the original table, and it has one extra attribute, namely a sequence counter with a unique index on it.
* Keep a seprarate numbers with table the same number of records as the main table and let it have a foreign key to the main table. The primary key is the sequence number and it also has a unique index on the foreign key in this instance. You never have gaps this way and never delete records other then the last record(s) in the clustered index. Order changes affect only updates to the foreign key on existing pages.
The static nature of the last method should work particulary well combined with with view- or table-partitions.
I can think of more complex schemes that reduce things ever further by eliminating a lot of updates, and by localizing them. This is best pictured as working with offsets relative to a reference value. By updating the reference value(s), all following records are renumbered automatically without update. To reference the effective sequence number or perform sorts, you reference both the reference and the offeset to get the final sequence number/ordering. In such a scheme, moving a record to the top of the list only requires updating one or more reference records, and sometimes some of the offsets as well. But never all the offset records at once that exist for every records in the main table.
February 10, 2011 at 9:13 am
Interesting Peter but you still then have the overhead of maintaining the base table upon insert, update and delete. I think you'd have to test with both scenarios to ascertain the impact.
Nice to know the article is making people think!
Cheers, James
James
MCM [@TheSQLPimp]
June 17, 2013 at 7:12 am
Interesting article. However, the code in the article is basically re-numbering manually instead of taking advantage of the built in Row_Number() functionality.
This seems to be a lot simpler:
(Note: I changed the table to a temp table for easier testing)
-- create the sequence test table
create table #ToDoList
(
todoID int identity(1,1) not null primary key,
PersonID int not null,
todoText nvarchar(200) not null,
todoSequence smallint not null default(0)
)
go
-- populate the sequence test table
insert into #ToDoList(PersonID, todoText, todoSequence)
select 1, 'Task 0', 0 union all
select 1, 'Task 1', 1 union all
select 1, 'Task 2', 2 union all
select 1, 'Task 3', 3 union all
select 1, 'Task 4', 4 union all
select 1, 'Task 5', 5 union all
select 1, 'Task 6', 6 union all
select 1, 'Task 7', 7 union all
select 1, 'Task 8', 8 union all
select 1, 'Task 9', 9
go
SELECT *
FROM #ToDoList
-- resequence tasks
DECLARE @PersonIDBIGINT= 1
, @ToDoIDINT= 1
, @NewSeqINT= 9
-- figure out the direction we are moving in
-- down = when the new sequence variable is greater than the old sequence variable
-- up = when the new sequence variable is less than then the old sequence variable
DECLARE @DownBIT -- 1 means down, 0 means up
SELECT @Down = CASE WHEN todoSequence < @NewSeq -- move down
THEN 1
ELSE 0 -- move up
END
FROM #ToDoList
WHERE todoID = @todoID
;WITH NewSequences AS
(
SELECT *
, ROW_NUMBER() OVER(ORDER BY
CASE WHEN TDL.toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END
-- order by new sequence without ordering, two numbers will have the same seq
, CASE WHEN TDL.toDoID = @ToDoID THEN @Down ELSE ~@Down END)
-- pick with of the two will be first, based on wether we are moving up/down
- 1 -- for 0 based list
AS RowNum
FROM #ToDoList TDL
WHERE PersonID = @PersonID
)
UPDATE TDL
SET TDL.todoSequence = NS.RowNum
FROM #ToDoList TDL
JOIN NewSequences NS ON TDL.ToDoID = NS.ToDoID
SELECT *
FROM #ToDoList
ORDER BY todoSequence
-- clean up
DROP TABLE #ToDoList
June 17, 2013 at 9:14 am
good article; just a quick note on the insert statement; SQL 2008 allows multiple values in one INSERT statement and is quite faster than an "INSERT... SELECT... UNION ALL":
INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )...
July 2, 2013 at 1:44 am
Well, I was not as satisfied with rownumber over order-by ..
I needed to enumerate 22mio records and over 3h didn't know wheather I'm on half way or no-way.
So I used an alternative:
1. original table: T(n int, data char..) with 22mio records
2. copy top 0 into T' from T
3. alter table T': make n as autonumber
4. insert into T' from T
if 4th step fails because of transaction lenght, use some criteria on T to only get
part of data, i.e. year(date), etc.
Worked fine.
Grega
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply