I have a legacy stored procedure that I'm trying to modify.
我有一个遗留的存储过程,我正在尝试修改。
This is a dump of the data that's in the table that's being modified.
这是对正在修改的表中的数据的转储。
Each time the PayrollRateID (2nd column, example values shown here 25, 27, 28 etc) increments the 'Incrementer' column needs incrementing.
每次PayrollRateID(第2列,此处显示的示例值25,27,28等)递增时,'Incrementer'列需要递增。
The ID column is generated using Row_Number()
使用Row_Number()生成ID列
Whoever wrote the stored procedure is using a column to go down each row in the table, and set the value of the incrementer column each time the PayrollRateID value cahnges. This, unfortunately, is taking a couple of minutes to do 20,000 odd rows. That's just a few days data, if I run with a month's worth, well, you can imagine the performance issue I'm getting.
编写存储过程的人使用列向下移动表中的每一行,并在每次PayrollRateID值cahnges时设置incrementmenter列的值。不幸的是,这需要几分钟才能完成20,000多行。这只是几天的数据,如果我运行一个月的价值,那么,你可以想象我遇到的性能问题。
The logic of the sproc is to start off with an incrementer value of 0, and with the first PayrollRateID (in the example - 25), give all those 0's, when the PayrollRateID changes, then increment the count in the incrementer column, and give all the next value (in the example - 27's) a 1, when it changes to the next value (in my example 28) then give the incrementer column a 2, etc.
sproc的逻辑是以增量值0开始,并使用第一个PayrollRateID(在示例中为25),在PayrollRateID更改时给出所有那些0,然后在增量器列中递增计数,并给出所有下一个值(在示例中为27)a 1,当它变为下一个值时(在我的例子28中),然后给增量器列a 2,等等。
Date
PayrollRateID
DayNum
Variance
VarianceID
Incrementer
ID
2011-07-25 00:00:00.000 25 1 1.00 0 0 1
2011-07-26 00:00:00.000 25 2 1.00 0 0 2
2011-07-27 00:00:00.000 25 3 1.00 0 0 3
2011-07-28 00:00:00.000 25 4 1.00 0 0 4
2011-07-29 00:00:00.000 25 5 1.00 0 0 5
2011-07-30 00:00:00.000 25 6 1.00 0 0 6
2011-07-31 00:00:00.000 25 7 1.00 0 0 7
2011-08-01 00:00:00.000 25 1 1.00 0 0 8
2011-08-02 00:00:00.000 25 2 1.00 0 0 9
2011-08-03 00:00:00.000 25 3 1.00 0 0 10
2011-08-04 00:00:00.000 25 4 1.00 0 0 11
2011-08-05 00:00:00.000 25 5 1.00 0 0 12
2011-08-06 00:00:00.000 25 6 1.00 0 0 13
2011-08-07 00:00:00.000 25 7 1.00 0 0 14
2011-08-08 00:00:00.000 25 1 1.00 0 0 15
2011-08-09 00:00:00.000 25 2 1.00 0 0 16
2011-08-10 00:00:00.000 25 3 1.00 0 0 17
2011-08-11 00:00:00.000 25 4 1.00 0 0 18
2011-08-12 00:00:00.000 25 5 1.00 0 0 19
2011-08-13 00:00:00.000 25 6 1.00 0 0 20
2011-08-14 00:00:00.000 25 7 1.00 0 0 21
2011-07-25 00:00:00.000 27 1 1.00 0 1 22
2011-07-26 00:00:00.000 27 2 1.00 0 1 23
2011-07-27 00:00:00.000 27 3 1.00 0 1 24
2011-07-28 00:00:00.000 27 4 1.00 0 1 25
2011-07-29 00:00:00.000 27 5 1.00 0 1 26
2011-07-30 00:00:00.000 27 6 1.00 0 1 27
2011-07-31 00:00:00.000 27 7 1.00 0 1 28
2011-08-01 00:00:00.000 27 1 1.00 0 1 29
2011-08-02 00:00:00.000 27 2 1.00 0 1 30
2011-08-03 00:00:00.000 27 3 1.00 0 1 31
2011-08-04 00:00:00.000 27 4 1.00 0 1 32
2011-08-05 00:00:00.000 27 5 1.00 0 1 33
2011-08-06 00:00:00.000 27 6 1.00 0 1 34
2011-08-07 00:00:00.000 27 7 1.00 0 1 35
2011-08-08 00:00:00.000 27 1 1.00 0 1 36
2011-08-09 00:00:00.000 27 2 1.00 0 1 37
2011-08-10 00:00:00.000 27 3 1.00 0 1 38
2011-08-11 00:00:00.000 27 4 1.00 0 1 39
2011-08-12 00:00:00.000 27 5 1.00 0 1 40
2011-08-13 00:00:00.000 27 6 1.00 0 1 41
2011-08-14 00:00:00.000 27 7 1.00 0 1 42
2011-07-25 00:00:00.000 28 1 1.00 0 2 43
2011-07-26 00:00:00.000 28 2 1.00 0 2 44
2011-07-27 00:00:00.000 28 3 1.00 0 2 45
2011-07-28 00:00:00.000 28 4 1.00 0 2 46
2011-07-29 00:00:00.000 28 5 1.00 0 2 47
2011-07-30 00:00:00.000 28 6 1.00 0 2 48
2011-07-31 00:00:00.000 28 7 1.00 0 2 49
2011-08-01 00:00:00.000 28 1 1.00 0 2 50
2011-08-02 00:00:00.000 28 2 1.00 0 2 51
2011-08-03 00:00:00.000 28 3 1.00 0 2 52
2011-08-04 00:00:00.000 28 4 1.00 0 2 53
2011-08-05 00:00:00.000 28 5 1.00 0 2 54
2011-08-06 00:00:00.000 28 6 1.00 0 2 55
2011-08-07 00:00:00.000 28 7 1.00 0 2 56
2011-08-08 00:00:00.000 28 1 1.00 0 2 57
2011-08-09 00:00:00.000 28 2 1.00 0 2 58
2011-08-10 00:00:00.000 28 3 1.00 0 2 59
2011-08-11 00:00:00.000 28 4 1.00 0 2 60
2011-08-12 00:00:00.000 28 5 1.00 0 2 61
2011-08-13 00:00:00.000 28 6 1.00 0 2 62
2011-08-14 00:00:00.000 28 7 1.00 0 2 63
2011-07-25 00:00:00.000 34 1 1.00 0 3 64
2011-07-26 00:00:00.000 34 2 1.00 0 3 65
2011-07-27 00:00:00.000 34 3 1.00 0 3 66
2011-07-28 00:00:00.000 34 4 1.00 0 3 67
2011-07-29 00:00:00.000 34 5 1.00 0 3 68
2011-07-30 00:00:00.000 34 6 1.00 0 3 69
2011-07-31 00:00:00.000 34 7 1.00 0 3 70
2011-08-01 00:00:00.000 34 1 1.00 0 3 71
2011-08-02 00:00:00.000 34 2 1.00 0 3 72
2011-08-03 00:00:00.000 34 3 1.00 0 3 73
2011-08-04 00:00:00.000 34 4 1.00 0 3 74
2011-08-05 00:00:00.000 34 5 1.00 0 3 75
2011-08-06 00:00:00.000 34 6 1.00 0 3 76
2011-08-07 00:00:00.000 34 7 1.00 0 3 77
2011-08-08 00:00:00.000 34 1 1.00 0 3 78
2011-08-09 00:00:00.000 34 2 1.00 0 3 79
2011-08-10 00:00:00.000 34 3 1.00 0 3 80
2011-08-11 00:00:00.000 34 4 1.00 0 3 81
2011-08-12 00:00:00.000 34 5 1.00 0 3 82
2011-08-13 00:00:00.000 34 6 1.00 0 3 83
2011-08-14 00:00:00.000 34 7 1.00 0 3 84
2011-07-25 00:00:00.000 38 1 1.00 0 4 85
2011-07-26 00:00:00.000 38 2 1.00 0 4 86
2011-07-27 00:00:00.000 38 3 1.00 0 4 87
2011-07-28 00:00:00.000 38 4 1.00 0 4 88
2011-07-29 00:00:00.000 38 5 1.00 0 4 89
2011-07-30 00:00:00.000 38 6 1.00 0 4 90
2011-07-31 00:00:00.000 38 7 1.00 0 4 91
2011-08-01 00:00:00.000 38 1 1.00 0 4 92
2011-08-02 00:00:00.000 38 2 1.00 0 4 93
2011-08-03 00:00:00.000 38 3 1.00 0 4 94
2011-08-04 00:00:00.000 38 4 1.00 0 4 95
2011-08-05 00:00:00.000 38 5 1.00 0 4 96
2011-08-06 00:00:00.000 38 6 1.00 0 4 97
2011-08-07 00:00:00.000 38 7 1.00 0 4 98
2011-08-08 00:00:00.000 38 1 1.00 0 4 99
2011-08-09 00:00:00.000 38 2 1.00 0 4 100
2011-08-10 00:00:00.000 38 3 1.00 0 4 101
2011-08-11 00:00:00.000 38 4 1.00 0 4 102
2011-08-12 00:00:00.000 38 5 1.00 0 4 103
2011-08-13 00:00:00.000 38 6 1.00 0 4 104
2011-08-14 00:00:00.000 38 7 1.00 0 4 105
2011-07-25 00:00:00.000 40 1 1.00 0 5 106
2011-07-26 00:00:00.000 40 2 1.00 0 5 107
2011-07-27 00:00:00.000 40 3 1.00 0 5 108
2011-07-28 00:00:00.000 40 4 1.00 0 5 109
2011-07-29 00:00:00.000 40 5 1.00 0 5 110
2011-07-30 00:00:00.000 40 6 1.00 0 5 111
2011-07-31 00:00:00.000 40 7 1.00 0 5 112
2011-08-01 00:00:00.000 40 1 1.00 0 5 113
2011-08-02 00:00:00.000 40 2 1.00 0 5 114
2011-08-03 00:00:00.000 40 3 1.00 0 5 115
2011-08-04 00:00:00.000 40 4 1.00 0 5 116
2011-08-05 00:00:00.000 40 5 1.00 0 5 117
2011-08-06 00:00:00.000 40 6 1.00 0 5 118
2011-08-07 00:00:00.000 40 7 1.00 0 5 119
2011-08-08 00:00:00.000 40 1 1.00 0 5 120
2011-08-09 00:00:00.000 40 2 1.00 0 5 121
2011-08-10 00:00:00.000 40 3 1.00 0 5 122
2011-08-11 00:00:00.000 40 4 1.00 0 5 123
2011-08-12 00:00:00.000 40 5 1.00 0 5 124
2011-08-13 00:00:00.000 40 6 1.00 0 5 125
2011-08-14 00:00:00.000 40 7 1.00 0 5 126
2011-07-25 00:00:00.000 41 1 1.00 0 6 127
2011-07-26 00:00:00.000 41 2 1.00 0 6 128
2011-07-27 00:00:00.000 41 3 1.00 0 6 129
2011-07-28 00:00:00.000 41 4 1.00 0 6 130
2011-07-29 00:00:00.000 41 5 1.00 0 6 131
2011-07-30 00:00:00.000 41 6 1.00 0 6 132
2011-07-31 00:00:00.000 41 7 1.00 0 6 133
2011-08-01 00:00:00.000 41 1 1.00 0 6 134
2011-08-02 00:00:00.000 41 2 1.00 0 6 135
2011-08-03 00:00:00.000 41 3 1.00 0 6 136
2011-08-04 00:00:00.000 41 4 1.00 0 6 137
2011-08-05 00:00:00.000 41 5 1.00 0 6 138
2011-08-06 00:00:00.000 41 6 1.00 0 6 139
2011-08-07 00:00:00.000 41 7 1.00 0 6 140
2011-08-08 00:00:00.000 41 1 1.00 0 6 141
2011-08-09 00:00:00.000 41 2 1.00 0 6 142
2011-08-10 00:00:00.000 41 3 1.00 0 6 143
2011-08-11 00:00:00.000 41 4 1.00 0 6 144
2011-08-12 00:00:00.000 41 5 1.00 0 6 145
2011-08-13 00:00:00.000 41 6 1.00 0 6 146
2011-08-14 00:00:00.000 41 7 1.00 0 6 147
2011-07-25 00:00:00.000 42 1 1.00 0 7 148
2011-07-26 00:00:00.000 42 2 1.00 0 7 149
2011-07-27 00:00:00.000 42 3 1.00 0 7 150
2011-07-28 00:00:00.000 42 4 1.00 0 7 151
2011-07-29 00:00:00.000 42 5 1.00 0 7 152
2011-07-30 00:00:00.000 42 6 1.00 0 7 153
2011-07-31 00:00:00.000 42 7 1.00 0 7 154
2011-08-01 00:00:00.000 42 1 1.00 0 7 155
2011-08-02 00:00:00.000 42 2 1.00 0 7 156
2011-08-03 00:00:00.000 42 3 1.00 0 7 157
2011-08-04 00:00:00.000 42 4 1.00 0 7 158
2011-08-05 00:00:00.000 42 5 1.00 0 7 159
2011-08-06 00:00:00.000 42 6 1.00 0 7 160
2011-08-07 00:00:00.000 42 7 1.00 0 7 161
2011-08-08 00:00:00.000 42 1 1.00 0 7 162
2011-08-09 00:00:00.000 42 2 1.00 0 7 163
2011-08-10 00:00:00.000 42 3 1.00 0 7 164
2011-08-11 00:00:00.000 42 4 1.00 0 7 165
2011-08-12 00:00:00.000 42 5 1.00 0 7 166
2011-08-13 00:00:00.000 42 6 1.00 0 7 167
2011-08-14 00:00:00.000 42 7 1.00 0 7 168
2011-07-25 00:00:00.000 44 1 1.00 0 8 169
2011-07-26 00:00:00.000 44 2 1.00 0 8 170
2011-07-27 00:00:00.000 44 3 1.00 0 8 171
2011-07-28 00:00:00.000 44 4 1.00 0 8 172
2011-07-29 00:00:00.000 44 5 1.00 0 8 173
I'm going to try and group on PayrollRateID, and use Row_Number() to generate the value for the Incrementer column.
我将尝试在PayrollRateID上进行分组,并使用Row_Number()生成Incrementer列的值。
Any better suggestions would be very much appreciated.
任何更好的建议将非常感谢。
SQL Server 2008.
SQL Server 2008。
3 个解决方案
#1
2
What about this:
那这个呢:
Update a
Set Incrementer = b.Incrementer
From dbo.YourTable a
Join
(
Select PayRollId,
Row_Number() Over(Order By PayRollId) as [Incrementer]
From dbo.YourTable
Group By PayRollId
)b on a.PayRollId = b.PayRollId
#2
1
Not sure if I understand the logic in the stored proc, but, could you create another table say [PayrollIncrementer] that has an ID identity seed value and inserting into the table when you need to increment the ID.
不确定我是否理解存储过程中的逻辑,但是,您是否可以创建另一个表[PayrollIncrementer],该表具有ID标识种子值并在需要增加ID时插入表中。
CREATE TABLE [dbo].[PayrollIncrementer]
(
[Id] INT NOT NULL IDENTITY(1),
[DateAdded] DATETIME NOT NULL
)
Your stored proc would use this table something like this
你的存储过程会像这样使用这个表
BEGIN
-- PROC BEGIN
DECLARE @id INT
INSERT INTO [dbo].[PayrollIncrementer] ([DateAdded]) VALUES (GETDATE())
SET @id = SCOPE_IDENTITY()
-- remainder of proc & use the identity
-- PROC END
END
Sorry if you have already thought of this, but I don't really understand the expected outcome.
对不起,如果你已经想到这一点,但我真的不明白预期的结果。
#3
0
How about just using DENSE_RANK instead of ROW_NUMBER? That will do everything at once.
如何使用DENSE_RANK而不是ROW_NUMBER?这将一下子完成所有事情。
#1
2
What about this:
那这个呢:
Update a
Set Incrementer = b.Incrementer
From dbo.YourTable a
Join
(
Select PayRollId,
Row_Number() Over(Order By PayRollId) as [Incrementer]
From dbo.YourTable
Group By PayRollId
)b on a.PayRollId = b.PayRollId
#2
1
Not sure if I understand the logic in the stored proc, but, could you create another table say [PayrollIncrementer] that has an ID identity seed value and inserting into the table when you need to increment the ID.
不确定我是否理解存储过程中的逻辑,但是,您是否可以创建另一个表[PayrollIncrementer],该表具有ID标识种子值并在需要增加ID时插入表中。
CREATE TABLE [dbo].[PayrollIncrementer]
(
[Id] INT NOT NULL IDENTITY(1),
[DateAdded] DATETIME NOT NULL
)
Your stored proc would use this table something like this
你的存储过程会像这样使用这个表
BEGIN
-- PROC BEGIN
DECLARE @id INT
INSERT INTO [dbo].[PayrollIncrementer] ([DateAdded]) VALUES (GETDATE())
SET @id = SCOPE_IDENTITY()
-- remainder of proc & use the identity
-- PROC END
END
Sorry if you have already thought of this, but I don't really understand the expected outcome.
对不起,如果你已经想到这一点,但我真的不明白预期的结果。
#3
0
How about just using DENSE_RANK instead of ROW_NUMBER? That will do everything at once.
如何使用DENSE_RANK而不是ROW_NUMBER?这将一下子完成所有事情。