|
Страници по тази тема: 1 | 2 | >> (покажи всички)
Тема
|
SQL Server - номериране в групи
|
|
Автор |
Pride of The North () |
Публикувано | 08.08.05 17:17 |
|
Не знам какъв е шанса някой да го прочете навереме и да има идея...
номерирането е от 1 до Н но в рамките на групиране. Прост пример
ако имаме категории 1,2,3,...
то трябва да номерираме от 1 до Н продуктите в категория 1 после в категория 2 и т.н.
Първото решение използва курсор , чудесно докато продуктите са по-малко от 1 милион, но после стана зле. клиента има само 1 час прозорец да пусне заявката а тя прави и други неща така че сме замомента загазили.
измерих времената за изпълнение и това е най-бавната част. ако някой се сеща дори и за малка оптимизация ще съм благодарен ;)
Долу има примерен код на това което се прави с курсор.
DECLARE @Tmp table(
DIN int,
RSN int,
AuditId int,
ASN int)
set nocount on
insert into @Tmp values (1,1,1,0)
insert into @Tmp values (1,2,2,0)
insert into @Tmp values (1,3,3,0)
insert into @Tmp values (1,4,4,0)
insert into @Tmp values (1,5,5,0)
insert into @Tmp values (2,1,1,0)
insert into @Tmp values (2,2,2,0)
insert into @Tmp values (2,3,3,0)
set nocount off
declare @DIN int, @RSN int, @AuditID int, @PrevDin int, @CalculatedASN int
DECLARE saiCursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT DIN,RSN, AuditId FROM @Tmp
ORDER BY DIN, RSN, AuditId
OPEN saiCursor
FETCH NEXT FROM saiCursor INTO @DIN, @RSN, @AuditID
SET @PrevDIN = 0
SET @CalculatedASN = 0
WHILE @@FETCH_STATUS = 0 BEGIN
IF @PrevDIN <> @DIN
BEGIN
SET @CalculatedASN = 1
SET @PrevDIN = @DIN
END
ELSE
BEGIN
IF @CalculatedASN = 9
BEGIN
SET @CalculatedASN = 1
END
ELSE
BEGIN
SET @CalculatedASN = @CalculatedASN + 1
END
END
UPDATE @Tmp
SET ASN = @CalculatedASN
WHERE DIN = @DIN AND RSN = @RSN AND AuditId = @AuditId
FETCH NEXT FROM saiCursor INTO @DIN, @RSN, @AuditID
END -- WHILE
CLOSE saiCursor
DEALLOCATE saiCursor
select * from @tmp
| |
|
всъщност измислих една оптимизация:
избираме с дистинкт само полето/тата по което се групира след което правим всеизвестното номериране само за тази група във временна таблица и после ъпдейт на основната с джойн на временната сетваме полето както се полага.
в тоз случай ще имаме за всяка група по 1 криейт тейбъл 1 селект/инсърт и 1 ъпдейт с джойн. това земества потенциално до 1..Н итерации в уайл като всяка има 1 ъпдейт.
звучеше много добре докато не установих че Н с данните дето има в много редки случаи е 2, още по рядко 3, никога 4 и най-вече 1. т.е. този вариант ще влоши нещата.
| |
|
Може да пробваш да сложиш и индекси на @tmp, за да е по бързо сканирането и сортирането (в момента отнемат съответно 50% и 17%), за това което съм написал. Кажи дали върши работа
DECLARE @Tmp table(
DIN int,
RSN int,
AuditId int,
ASN int)
DECLARE @Tmp2 table(
DIN int,
RSN int,
AuditId int,
ASN int IDENTITY(1,1))
set nocount on
insert into @Tmp values (1,1,1,0)
insert into @Tmp values (1,2,2,0)
insert into @Tmp values (1,3,3,0)
insert into @Tmp values (1,4,4,0)
insert into @Tmp values (1,5,5,0)
insert into @Tmp values (2,1,1,0)
insert into @Tmp values (2,2,2,0)
insert into @Tmp values (2,3,3,0)
INSERT INTO @Tmp2 (
DIN ,
RSN ,
AuditId
)
SELECT DIN,RSN, AuditId FROM @Tmp
ORDER BY DIN, RSN, AuditId
select * from @tmp2
| |
|
Мерси за отделеното време ;)
обаче твоя код номерира от 1 до 8 в случая а мен ми трябва да номерира от 1 до 5 за Дин 1 и от 1 до 3 за Дин 2.
всъщност не съм казал всичко щото преправям тука като луд да не ме обвини някой че разпространявам таен код.
тмп има индекс по Дин РСН и ОдитИД
| |
|
DECLARE @Tmp table(
DIN int,
RSN int,
AuditId int,
ASN int)
CREATE TABLE #TmpTable2 (
DIN int,
RSN int,
AuditId int,
ASN int IDENTITY(1,1))
DECLARE @maxDin int
DECLARE @whileDin int
set nocount on
insert into @Tmp values (1,1,1,0)
insert into @Tmp values (1,2,2,0)
insert into @Tmp values (1,3,3,0)
insert into @Tmp values (1,4,4,0)
insert into @Tmp values (1,5,5,0)
insert into @Tmp values (2,1,1,0)
insert into @Tmp values (2,2,2,0)
insert into @Tmp values (2,3,3,0)
SET @maxDin = (
SELECT TOP 1 t.Din
FROM @Tmp t
ORDER BY t.Din DESC
)
SET @whileDin= 1
WHILE @whileDin <= @maxDin
BEGIN
INSERT INTO #TmpTable2 (
DIN ,
RSN ,
AuditId
)
SELECT DIN,RSN, AuditId
FROM @Tmp
WHERE DIN = @whileDin
ORDER BY RSN, AuditId
DBCC CHECKIDENT (#TmpTable2, RESEED, 0)
SET @whileDin= @whileDin +1
END
select * from #TmpTable2
DROP TABLE #TmpTable2
| |
|
да сори, аз съм виновен, не съм обяснил достатъчно подробно.
тук има 2 неща
1-во Дин не е пореден номер. това лесно може да се преодолее като се направи дистинкт както предложих във второто мнение
освен това правиш същия курсор/цикъл който вече съществува и който е ужасно бавен щото има 800000 уникални дин-а и част от тях имат по 2 и 3 повторения. Всъщност цикъла ти по прилича на втория който написах с ходене само по уникалните динове с тази разлика че ти ходиш подред щото не ти казах че не са подред ;)
мерси за DBCC CHECKIDENT (#TmpTable2, RESEED, 0) не се бях сетил. ще пробвам но сигурно е по-бързо от дроп/криейт.
и последно в това твое решение накрая трябва да има един ъпдейт на АСН към тмп защото тмп всъщост има много повече полета и се ползва за други цели.
е ще пробвам. Мерси;)
така или иначе с тея данни ми отиват към 4 часа за 1 тест така че има време ;)
| |
|
малко се светнах но още ми е мътно какво точно се иска , не ходи само по уникалните Din а селектва по тях, т-е връща подреден пореден селект за всички редове които имат такъв Din. Ако няма, не връща нищо.
като си напишеш сторед прок я пусни през index tuning wizard, той може да каже нещо интересно. може да си ребилднеш индексите преди да почнеш да мачкаш, да видиш как е.
Имам идея : предполагам че всеки от редовете ти има ПК (примерно PkID). Можеш да направиш нещо от рода на
UPDATE @table
SET ASN= 1
WHERE PkID IN (
SELECT PkID
FROM @table
WHERE -- по някакъв начин да се отделят тези които са за ASN = 1
)
и така за до ASN=4 маи това каза че ти е максималното.
За съжеление в момента не мога да се усетя за селекта
Редактирано от AzSumZmei на 08.08.05 19:19.
| |
|
малко инфо за селекта
SELECT Din, Min(RSN), Min(AuditId)
FROM @Tmp
GROUP BY Din
утре ще има решение
| |
|
от тук вече ще си играеш с индексите (може да пуснеш 1 скрипт на таблицата поне да я видим)
BEGIN TRANSACTION
CREATE TABLE #tmpTable (
IDtmpTable int IDENTITY(1,1) ,
DIN int ,
RSN int ,
AuditId int ,
ASN int
)
set nocount on
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,2,2,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,1,1,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,3,3,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,4,4,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (1,5,5,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (2,1,1,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (2,2,2,0)
insert into #tmpTable (DIN, RSN, AuditId, ASN) values (2,3,3,0)
set nocount off
UPDATE t
SET ASN = 1 +
IsNull ((
SELECT COUNT (IDtmpTable)
FROM #tmpTable t1
WHERE t1.DIN = t.DIN AND
(
t1.RSN < t.RSN or
t1.RSN = t.RSN and
t1.AuditId < t.AuditId
)
), 0)
from #tmpTable t
SELECT *
FROM #tmpTable
ORDER BY Din, RSN, AuditId
DROP TABLE #tmpTable
ROLLBACK TRANSACTION
| |
Тема
|
решението ти е идеално
[re: AzSumZmei]
|
|
Автор | darklord (Нерегистриран) |
Публикувано | 11.08.05 16:36 |
|
за човека, който питаше - това решение, в случай че имаш индекс по полетата е перфектно. На мен често ми се налага да използвам подобно номериране в таблици с доста редове и то работи много бързо.
Успех!
| |
|
Страници по тази тема: 1 | 2 | >> (покажи всички)
|
|
|