Обработка нескольких записей в триггере SQL MS
public class Exercise {
public static void main(String[] r)
{
try{
long a = 600851475143L;
System.out.println("the largest prime factor of " + a+ " is " +largestPrimeFactor(a) );
}catch(Exception e)
{
e.printStackTrace();
}
}
public static int largestPrimeFactor(long number) {
int i;
long copyOfInput = number;
for (i = 2; i <= copyOfInput; i++) {
if (copyOfInput % i == 0) {
copyOfInput /= i;
i--;
}
}
return i;
}
}
Попробуйте это
5 ответов
Прием с этими видами ситуаций должен повернуться, последовательная операция (для каждой записи делают xyz) в основанную на наборе операцию (оператор UPDATE).
Я проанализировал Вашу хранимую процедуру и объединил Ваш разделять операторы UPDATE на единственный. Этот отдельный оператор может затем быть преобразован в версию, которая может быть применена ко всем вставленным записям сразу, избавив от необходимости хранимую процедуру и таким образом потребности в курсоре.
Править: Ниже код, что мы наконец получили работу. Время выполнения для целой операции прошло по сравнению с "фактически навсегда" (для исходного решения) к чему-то менее чем одна секунда, согласно обратной связи OP. Полный размер кода, также уменьшенный вполне заметно.
CREATE TRIGGER [dbo].[TR_STAT_INSERT]
ON [iqdev].[dbo].[Stat]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
-- insert all missing "CachedStats" rows
INSERT INTO
CachedStats ([Date], AdvertId, CustomerId, CampaignId, CampaignName)
SELECT DISTINCT
CONVERT(Date, i.[Date]), i.AdvertId, i.PublisherCustomerId, c.Id, c.Name
FROM
Inserted i
INNER JOIN Advert a ON a.Id = i.AdvertId
INNER JOIN Campaign c ON c.Id = a.CampaignId
WHERE
i.Approved = 1
AND NOT EXISTS (
SELECT 1
FROM CachedStats
WHERE Advertid = i.AdvertId AND
CustomerId = i.PublisherCustomerId AND
[Date] = CONVERT(DATE, i.[Date])
)
-- update all affected records at once
UPDATE
CachedStats
SET
Clicks = Clicks + i.AddedClicks,
UniqueClicks = UniqueClicks + i.AddedUniqueClicks,
[Views] = [Views] + i.AddedViews,
UniqueViews = UniqueViews + i.AddedUniqueViews,
Leads = Leads + i.AddedLeads,
PublisherEarning = PublisherEarning + ISNULL(i.AddedPublisherEarning, 0),
AdvertiserCost = AdvertiserCost + ISNULL(i.AddedAdvertiserCost, 0),
PublisherOrderValue = PublisherOrderValue + ISNULL(i.AddedPublisherOrderValue, 0),
AdvertiserOrderValue = AdvertiserOrderValue + ISNULL(i.AddedAdvertiserOrderValue, 0)
FROM
(
SELECT
AdvertId,
CONVERT(DATE, [Date]) [Date],
PublisherCustomerId,
COUNT(*) NumRows,
SUM(CASE WHEN Type IN (0) THEN 1 ELSE 0 END) AddedClicks,
SUM(CASE WHEN Type IN (0) AND [Unique] = 1 THEN 1 ELSE 0 END) AddedUniqueClicks,
SUM(CASE WHEN Type IN (2) THEN 1 ELSE 0 END) AddedViews,
SUM(CASE WHEN Type IN (2) AND [Unique] = 1 THEN 1 ELSE 0 END) AddedUniqueViews,
SUM(CASE WHEN Type IN (1,3,4) AND [Unique] = 1 THEN 1 ELSE 0 END) AddedLeads,
SUM(PublisherEarning) AddedPublisherEarning,
SUM(AdvertiserCost) AddedAdvertiserCost,
SUM(CASE WHEN Type IN (3) THEN PublisherOrderValue ELSE 0 END) AddedPublisherOrderValue,
SUM(CASE WHEN Type IN (3) THEN AdvertiserOrderValue ELSE 0 END) AddedAdvertiserOrderValue
FROM
Inserted
WHERE
Approved = 1
GROUP BY
AdvertId,
CONVERT(DATE, [Date]),
PublisherCustomerId
) i
INNER JOIN CachedStats cs ON
cs.Advertid = i.AdvertId AND
cs.CustomerId = i.PublisherCustomerId AND
cs.[Date] = i.[Date]
SET NOCOUNT OFF
END
Операции, включающие CachedStats таблица значительно извлечет выгоду из нескольких-индексов-столбца (Advertid, CustomerId, [Date]) (как подтверждено OP).
Ваш лучший выбор состоит в том, чтобы переместиться в основанную на наборе операцию в триггере. Я не иду, пишут это для Вас 100%, но позволяют мне запустить Вас, и мы видим, куда мы идем оттуда. Следует иметь в виду, что я пишу это без таблиц / схемы и таким образом, я не иду, проверяют. Ожидайте Typos:-),
Давайте посмотрим на Ваши операторы обновления сначала, Из того, что я могу сказать, что Вы обновляете ту же таблицу тем же, где пункт единственная разница является столбцами. Можно консолидировать это для сходства с:
UPDATE CachedStats SET
/* Basically we are going to set the counts based on the type inline in the update clause*/
Leads= CASE WHEN (@Type = 1 OR @Type = 4 OR @Type=3 ) THEN Leads + 1 ELSE LEADS END,
Clicks=CASE WHEN (@Type=0) THEN Clicks+1 ELSE Clicks END,
Views=CASE WHEN (@Type=4) THEN Views+1 ELSE Views END,
PublisherEarning = @PublisherEarning + PublisherEarning,
AdvertiserCost = @AdvertiserCost +AdvertiserCost,
FROM CachedStats CS
INNER JOIN Inserted INS
ON CS.Date=Inserted.Date AND CS.CustomerId=Ins.PublisherId AND CS.CampaignId=Ins.CampaignId
Я действительно соглашаюсь с Вами, что это могло стать ужасным, но это - решение, которое необходимо будет принять.
Что касается Вашего пункта вставки, я обработал бы это тот же способ, которым Вы уже, просто вставляют в таблицу от таблицы Inserted, вообще уже не существует.
Можно немного оптимизировать изменение курсора путем выполнения FAST_FORWARD, READ_ONLY и ЛОКАЛЬНЫХ опций на курсоре. Кроме того, Вы вытягиваете идентификатор в свой курсор и затем цикличное выполнение назад для получения значений. Или используйте CURRENT_OF или бросьте их всех в переменные. Но, я не ожидал бы, что эти изменения купят Вас очень.
Действительно необходимо переместиться в основанный на наборе подход. Это сохранило proc, является определенно выполнимым в основанной на наборе модели - хотя может потребоваться 3 или 4 различных оператора обновления. Но даже 3 или 4 различных триггера (1 для представлений, 1 для щелчков, и т.д.) были бы лучше, чем подход курсора.
Первая вещь, которую я сделал бы, использовать курсор FAST_FORWARD вместо этого. Поскольку Вы только идете от одной записи до следующего и не делаете любые обновления, это будет намного лучше для производительности.
В зависимости от того, какую версию MSSQL Вы выполняете, необходимо также рассмотреть использование Индексных представлений для этого также. Это могло очень хорошо быть более простым подходом, чем Ваши триггеры, в зависимости от того, на что похож запрос отчета. Посмотрите здесь для большего количества информации.
Кроме того, в Вашем триггере необходимо попытаться записать обновления осуществленной таблицы результатов как основанная на наборе операция, не курсор. Запись основанного на курсоре триггера могла потенциально просто перемещаться, Ваша проблема от запроса отчета до Вашей таблицы вставляет вместо этого.