July 30, 2009 at 8:31 pm
I'm developing an online telephone directory in which a person can designate delegates - alternate people to call if the primary person isn't available. The child records have a field PRIORITY which indicates the order in which the delegates should be displayed underneath the contact. The stored procedure below appears to correctly manage the records to ensure that the ALL of the child records are updated when a single record is updated closing up any gaps. For example, if there are five records and the record with the PRIORITY of '4' is updated to '2' then 2 is updated to 3, 3 is updated to 4. Like wise if the record is deleted, 3 is updated to 2, and you get the idea.
The index referenced below is on ContactId, Priority.
The code appears to work, but on another site someone noted that the INDEX isn't guaranteed to return the records in the order needed for the records to be incremented in the final UPDATE in the sp. I was under the impression that a non-clustered INDEX would do just that.
Q: Will an INDEX always return records ordered by the fields in the index?
Q: Is the logic of the SP below sound? Is it efficient?
The code was based on an article here which I can post a link if needed. Let me know if you need to see anything else.
CREATE PROCEDURE [dbo].[sp_updateDelegatePriority]
(
@contactId integer,
@updatedRecord integer
)
AS
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
July 31, 2009 at 5:50 am
No, an index hint doesn't change the order. The only way to affect the order of the data returned is to use an ORDER BY clause. That will take advantage of an index, but it is the only way to guarantee the order of data returned.
I wouldn't recommend using index hints in general. They're meant as a dead last, nothing else works, I've found a situation where the optimizer can't handle my code, resort. Usually restructuring the tables, indexes or code is the better solution.
In this case, I'd drop the index hint and put an ORDER BY in there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2009 at 6:42 am
So other than switching the INDEX to an ORDER BY, things look pretty on target? My experience with SQLServer is along the lines of advanced beginner.
July 31, 2009 at 7:34 am
It looks OK. You don't need to use a CTE to do the UPDATE, you can simply UPDATE... FROM and select what you need, but it won't change the overall functionality much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2009 at 8:30 am
Uhh....CTE?
Also, I replaced the INDEX hint with an ORDER BY but it didn't work properly.
BEGIN
DECLARE @id_start decimal(7,1)
SET @id_start = .1
BEGIN
WITH RecordSet AS (Select TOP 100 Percent Row_number() Over(Order By ContactId, Priority) as RowNum, 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 Row_number() Over(Order By ContactId, Priority) as RowNum, ContactId, Priority FROM Delegates WHERE ContactId = @ContactId )
UPDATE RecordSet SET @id_start = Priority = @id_start + 1 WHERE ContactId = @ContactId;
END
END
July 31, 2009 at 8:39 am
Sorry, WITH X AS (SELECT), is the syntax for a common table expression, CTE.
Can you post the structure & some sample data. I can play with the proc then. Index hints won't guarantee order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2009 at 9:57 am
Table Structure. Note the default.
[Code]
CREATE TABLE [dbo].[Delegates](
[Id] [int] IDENTITY(1000,1) NOT NULL,
[ContactId] [int] NOT NULL,
[DelegateContactId] [int] NOT NULL,
[ExpirationDate] [date] NULL,
[Priority] [decimal](7, 1) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Delegates] ADD CONSTRAINT [DF_Delegates_PriorityDec] DEFAULT ((0)) FOR [Priority]
GO
[/Code]
Sample records. My thought was to load a table with a large number of records and then randomly select 10 records to work. You'll obviously need to change the priority of those specific records.
[Code]
CREATE PROCEDURE [dbo].[sp_loadTable]
AS
BEGIN
DECLARE @count integer, @contactId integer
SET @count = 1
WHILE @count < 1000
BEGIN
SET @contactId = '1000'
IF @count = 1 SET @contactId = '5000'
IF @count = 223 SET @contactId = '5000'
IF @count = 224 SET @contactId = '5000'
IF @count = 346 SET @contactId = '5000'
IF @count = 412 SET @contactId = '5000'
IF @count = 552 SET @contactId = '5000'
IF @count = 583 SET @contactId = '5000'
IF @count = 700 SET @contactId = '5000'
IF @count = 732 SET @contactId = '5000'
IF @count = 927 SET @contactId = '5000'
INSERT INTO Delegates (ContactId, DelegateContactId, Priority) SELECT @contactId as expr1, '3710' as expr2, '1' as expr3
SET @count = @count + 1
END
END
[/Code]
July 31, 2009 at 1:07 pm
Working on it. Sorry. Work interfered.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2009 at 3:27 pm
Oh yeah, that's fine. I won't complain. But you do have to admit that it will significantly contribute to the general knowledge base.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply