Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Update Records in a Specific Sequence

    Based on this article, I wrote the following stored procedure. The idea is that when the PRIORITY field of a record is updated via a ASP.NET page adjust the values of its siblings accordingly. If there are 10 records and the 6th record's priority is changed to 2 then all of the priority of the subsequent records are automatically adjusted. The old 2 becomes 3, 3 becomes 4, and so on. The idea behind using the technique in the article is to speed up the process.

    For this to work, I obviously need SQL Server to SELECT the records for the UPDATE in a specific order. I did create an INDEX but the INDEX isn't being used. If understand the documentation correctly, an INDEX can't be specified for an UPDATE query.

    Is it possible to write an UPDATE statement wherein records are returned in a specific order?

    Code:
    CREATE PROCEDURE [dbo].[sp_updateDelegatePriority]
    (
    	@contactId integer
    )
    AS
    BEGIN
    
    DECLARE @id_start decimal(7,1)
    SET @id_start = 0
    SET @newPriority = @newPriority * - 1
    
    PRINT 'Executing Query 1'
    UPDATE Delegates SET @id_start = Priority = @id_start + 1 WHERE ContactId = @contactId AND Priority >= @newPriority 
    Print 'Complete Query 1'
    
    PRINT 'Executing Query 2'
    UPDATE Delegates SET Priority = Priority * -1 WHERE ContactId = @contactId and Priority = @newPriority * -1
    PRINT 'Complete Query 2'
    SET @id_start = 0
    
    Print 'Excecuting Query 3'
    UPDATE Delegates SET @id_start = Priority = @id_start + 1 WHERE ContactId = @contactId
    Print 'Complete Query 3'
    END
    
    GO

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Cannot read the Article as I won't register with SQLServerCentral to READ information from Posters who provide there own expertise Free of Charge.

    The fact that your trying to do updates in a priority order to try and speed the operation up makes me shudder.

    can you give us a little Overall background of what/why your trying to speed up updates.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by GWilliy
    Cannot read the Article as I won't register with SQLServerCentral to READ information from Posters who provide there own expertise Free of Charge.

    The fact that your trying to do updates in a priority order to try and speed the operation up makes me shudder.

    can you give us a little Overall background of what/why your trying to speed up updates.
    Yes, I know what it sounds like, but there is a very good reason behind the why. The project is a company telephone directory which has a feature by which a person can indicate who his/her assistant(s) or backups are. The idea is that when you look up the person's name, you can see alternate names of people that might be able to field the question if that person isn't available. The PRIORITY field controls the sorting of the delegate names indicating the pecking order in which the delegates should be contacted. For example, the person's assistant and coordinator would be listed as #1 and #2, however if the person is out on vacation a peer might designated as their backup. So the backup would be added and placed at #1.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You don't need to update "in order" (something which isn't really supported anyway). Think set-based instead of thinking procedurally. All you should need is a CASE expression to specify how the priority should change. Something like this:

    UPDATE Delegates SET Priority =
    CASE
    WHEN Priority = @OldPriority THEN @NewPriority
    WHEN Priority >= @NewPriority AND Priority < @OldPriority THEN Priority + 1
    WHEN Priority <= @NewPriority AND Priority > @OldPriority THEN Priority - 1
    ELSE Priority
    END
    WHERE Priority BETWEEN @OldPriority AND @NewPriority
    OR Priority BETWEEN @NewPriority AND @OldPriority;

    Hope this helps.

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by dportas
    You don't need to update "in order" (something which isn't really supported anyway). Think set-based instead of thinking procedurally. All you should need is a CASE expression to specify how the priority should change. Something like this:

    UPDATE Delegates SET Priority =
    CASE
    WHEN Priority = @OldPriority THEN @NewPriority
    WHEN Priority >= @NewPriority AND Priority < @OldPriority THEN Priority + 1
    WHEN Priority <= @NewPriority AND Priority > @OldPriority THEN Priority - 1
    ELSE Priority
    END
    WHERE Priority BETWEEN @OldPriority AND @NewPriority
    OR Priority BETWEEN @NewPriority AND @OldPriority;

    Hope this helps.
    The thought though was to approach it from a standpoint that builds in a contingency that would correctly order the priority 1 to n in the event that something should happen where the sequence gets out of order. In other words, if the records contain values that are 1, 2, 4, 5, 7, 9 (regards of the order in the table) then incrementing the value persists the gaps.

    Keeping in mind, that this is not to (EVER) have the records appear in the database in a particular sequence, but rather to ensure that the values in the subset can be ordered 1 to [n] without any gaps.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've not read the article, but if you are using the priority as I imagine you are then it matters not if there are gaps, no? In you example, 4 follows 2 just as surely as 3 does.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You can use ROW_NUMBER to generate the sequence without gaps. I haven't tested the following but hopefully it will give you the idea.

    WITH d AS
    (SELECT Priority,
    ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY Priority) NewPriority
    FROM Delegates
    WHERE ContactID = @ContactID)
    UPDATE d SET Priority =
    CASE
    WHEN NewPriority = @OldPriority THEN @NewPriority
    WHEN NewPriority >= @NewPriority AND Priority < @OldPriority THEN Priority + 1
    WHEN NewPriority <= @NewPriority AND Priority > @OldPriority THEN Priority - 1
    ELSE NewPriority
    END
    WHERE NewPriority BETWEEN @OldPriority AND @NewPriority
    OR NewPriority BETWEEN @NewPriority AND @OldPriority ;

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by pootle flump
    I've not read the article, but if you are using the priority as I imagine you are then it matters not if there are gaps, no? In you example, 4 follows 2 just as surely as 3 does.
    The value is presented to the user on an ASP.NET page using a drop down that is dynamically populated using an incremented variable that runs from 1 to [n]. While it could certainly be adjusted to accommodate gaps, there is a very strong commitment to present a very polished look and feel. Hence gaps would be a no-no.

  9. #9
    Join Date
    Jul 2009
    Posts
    7
    Quite close...would be closer if I didn't need to grab some lunch. Note the INDEX hint in the final UPDATE.

    Code:
    BEGIN
    
    DECLARE @id_start decimal(7,1)
    SET @id_start = 0
    
    BEGIN
    	WITH RecordSet AS (Select TOP 100 PERCENT ContactId, Priority FROM Delegates WHERE ContactId = @ContactId)
    	UPDATE RecordSet SET @id_start = Priority = @id_start + 1 WHERE ContactId = @ContactId AND Priority >= 0;
    END
    
    BEGIN
    	UPDATE Delegates SET Priority = Priority * -1 WHERE ContactId = @contactId and Priority < 0;
    END
    
    SET @id_start = 0
    
    BEGIN
    	WITH RecordSet AS (Select TOP 100 PERCENT ContactId, Priority FROM Delegates WITH (INDEX(idx_Delegates_ContactId_Priority)) WHERE ContactId = @ContactId AND Priority > 0 )
    	UPDATE RecordSet SET @id_start = Priority = @id_start + 1 WHERE ContactId = @ContactId;
    END
    
    END

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    There's no need to use index hints. Did you try the ROW_NUMBER() version I posted above?

    The problem with trying to kludge it using an index is that UPDATEs are still logically unordered. There's no guarantee that the optimiser will always continue to execute it the way you expect. Also, even if an index is used index scans can be unordered (allocation order scans). To my knowledge that doesn't happen with UPDATEs today but perhaps it could do in future.

  11. #11
    Join Date
    Jul 2009
    Posts
    7
    This looks like its works. Still tweaking it a bit. The .1 increment allows the record that's been updated to be placed into its correct position. Without the initial update, the updated record could appear one notch below its intended position. The index is on ContactId, Priority.

    Code:
    BEGIN
    
    DECLARE @id_start decimal(7,1)
    SET @id_start = .1
    
    BEGIN
    	WITH RecordSet AS (Select TOP 100 PERCENT ContactId, Priority, Id FROM Delegates WHERE ContactId = @ContactId)
    	UPDATE RecordSet SET @id_start = Priority = @id_start + 1 WHERE ContactId = @ContactId AND Priority > 0 And Id <> @updatedRecord;
    END
    
    SET @id_start = 0
    
    BEGIN
    	WITH RecordSet AS (Select TOP 100 PERCENT ContactId, Priority FROM Delegates WITH (INDEX(idx_Delegates_ContactId_Priority)) WHERE ContactId = @ContactId )
    	UPDATE RecordSet SET @id_start = Priority = @id_start + 1 WHERE ContactId = @ContactId;
    END
    
    END
    Last edited by WillMadison; 07-30-09 at 16:07.

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    No it won't "work" because the ORDER BY clause will be ignored (at least it will be in SQL Server 2008 and later).

    Will, I appreciate you chose to go your own way rather than my suggestion but I'm posting this comment primarily in case anyone else is tempted to take up your suggestion.

  13. #13
    Join Date
    Jul 2009
    Posts
    7
    Quote Originally Posted by dportas
    No it won't "work" because the ORDER BY clause will be ignored (at least it will be in SQL Server 2008 and later).

    Will, I appreciate you chose to go your own way rather than my suggestion but I'm posting this comment primarily in case anyone else is tempted to take up your suggestion.
    Did you read the latest? There's no ORDER BY and based on my testing it does appear to work.

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    As I said before, index scans are not necessarily ordered. You don't need testing to prove this (and testing it and getting a sorted result certainly doesn't prove anything), you just need to know how SQL Server works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •