Проблема с памятью временной таблицы хранимой процедуры SQL

Глядя на приведенный выше код, зачем вам временная таблица?

DELETE FROM MatchEvent WHERE
MatchId IN (SELECT MatchId FROM MatchResult)


DELETE FROM MatchResult
-- OR Truncate can help here, if all the records are to be deleted anyways.

Вы, вероятно, захотите каким-то образом обработать это кусочно. (Я предполагаю, что запросы намного сложнее, чем вы показали?) В этом случае вам нужно попробовать одно из следующих:

• Напишите свою хранимую процедуру для перебора результатов. (Может все еще блокироваться во время обработки.)
• Несколько раз выберите N первых совпадений, например LIMIT 100, и обработайте их.
• Разделите работу, сканируя области таблицы отдельно, используя что-то вроде WHERE M <= x AND x <N.
• Чаще выполняйте «полуночную работу». Серьезно, такие занятия каждые 5 минут могут творить чудеса, особенно если объем работы увеличивается нелинейно. (Если нет, вы все равно можете просто распределить работу по часам дня.)

В Postgres я добился определенных успехов в использовании условных индексов. Они творит чудеса, применяя индекс при соблюдении определенных условий. Это означает, что вы можете сохранить многие «разрешенные» и несколько неразрешенных строк в одной и той же таблице, но при этом получить этот специальный индекс только по неразрешенным. Ymmv.

Следует отметить, что именно здесь при использовании баз данных получается интересно. Вам нужно внимательно следить за своими индексами и часто использовать EXPLAIN в своих запросах.

(Да, и помните, интересно - это хорошо для ваших хобби, но не для работы.)

Вероятно, здесь происходит много всего, и это еще не все, что вам нужно.

Во-первых, я согласен с другими плакатами. Попытайтесь переписать это без временной таблицы, если это вообще возможно.

Но если предположить, что здесь вам нужна временная таблица, у вас есть БОЛЬШАЯ проблема в том, что для нее не определен PK. Это значительно увеличит время выполнения ваших запросов. Вместо этого создайте свою таблицу так:

CREATE TABLE #tempMatchResult (
    matchId VARCHAR(50) NOT NULL PRIMARY KEY /* NOT NULL if at all possible */
);

INSERT INTO #tempMatchResult
SELECT DISTINCT MatchId FROM MatchResult;

Также убедитесь, что ваш TempDB имеет правильный размер. Ваш SQL-сервер вполне может динамически расширять файл базы данных на вас, заставляя ваш запрос отнимать процессорное время и дисковое время. Кроме того, убедитесь, что ваш журнал транзакций имеет правильный размер и не увеличивается автоматически. Удачи.

Во-первых, здесь ОБЯЗАТЕЛЬНЫ индексы, см. Ответ Дэйва М.

Другой подход, который я когда-нибудь буду использовать при удалении очень больших наборов данных, - это создание теневой таблицы со всеми данными, воссоздание индексов и последующее использование процедуры sp_rename для ее включения. Здесь вы должны быть осторожны с транзакциями, но в зависимости от количества данные удаляются это может быть быстрее.

Примечание Если есть давление на tempdb, рассмотрите возможность использования объединений, а не копирования всех данных во временную таблицу.

Так например

CREATE TABLE #tempMatchResult (
    matchId VARCHAR(50) NOT NULL PRIMARY KEY /* NOT NULL if at all possible */
);

INSERT INTO #tempMatchResult
SELECT DISTINCT MatchId FROM MatchResult;

set transaction isolation level serializable
begin transaction 

create table MatchEventT(columns... here)

insert into MatchEventT
select * from MatchEvent m
left join #tempMatchResult t on t.MatchId  = m.MatchId 
where t.MatchId is null 

-- create all the indexes for MatchEvent

drop table MatchEvent
exec sp_rename 'MatchEventT', 'MatchEvent'

-- similar code for MatchResult

commit transaction 


DROP TABLE #tempMatchResult

По возможности избегайте временной таблицы

Он только использует память. Вы можете попробовать это:

DELETE MatchEvent
FROM MatchEvent  e , 
     MatchResult r
WHERE e.MatchId = r.MatchId 

Если вы не можете избежать временного стола

Я собираюсь высунуть здесь шею и сказать: вам не нужен индекс для вашей временной таблицы, потому что вы хотите, чтобы временная таблица была наименьшей таблицей в уравнении, и вы хотите сканировать ее (потому что все строки релевантны). Индекс здесь не поможет.

Делайте небольшие кусочки работы

Работайте над несколькими рядами за раз. Это, вероятно, замедлит выполнение, но должно высвободить ресурсы.

SELECT @MatchId = min(MatchId) FROM MatchResult

WHILE @MatchId IS NOT NULL
BEGIN
    DELETE MatchEvent 
    WHERE  Match_Id = @MatchId 

    SELECT @MatchId = min(MatchId) FROM MatchResult WHERE MatchId > @MatchId 
END

CREATE TABLE #tmp ( MatchId Varchar(50) ) 

/* get list of lowest 1000 MatchIds: */ 
INSERT #tmp 
SELECT TOP (1000) MatchId 
FROM MatchResult 
ORDER BY MatchId 

SELECT @MatchId = min(MatchId) FROM MatchResult

WHILE @MatchId IS NOT NULL
BEGIN
    DELETE MatchEvent
    FROM MatchEvent e , 
         #tmp       t
    WHERE e.MatchId = t.MatchId 

    /* get highest MatchId we've procesed: */  
    SELECT @MinMatchId = MAX( MatchId ) FROM #tmp  

    /* get next 1000 MatchIds: */  
    INSERT #tmp 
    SELECT TOP (1000) MatchId 
    FROM MatchResult 
    WHERE MatchId > @MinMatchId
    ORDER BY MatchId 

END

Он удаляет до 1000 строк за раз. Чем больше строк вы удаляете за раз, тем больше ресурсов вы будете использовать, но тем быстрее он будет работать (пока у вас не закончатся ресурсы!). Вы можете поэкспериментировать, чтобы найти более оптимальное значение, чем 1000.

DELETE FROM MatchResult WHERE
MatchId In (SELECT MatchId FROM #tempMatchResult)

можно заменить на

DELETE FROM MatchResult WHERE SOME_CONDITION

Можете ли вы просто включить каскадное удаление между matchresult и matchevent? Тогда вам нужно будет только позаботиться об идентификации одного набора данных для удаления, а SQL позаботится о другом.

Альтернативой было бы использование предложения OUTPUT, но это определенно более сложная задача.

Оба они позволят вам удалить из обеих таблиц, но вам нужно будет только один раз указать (и выполнить) свой предикат фильтра. Это может быть по-прежнему не таким эффективным, как пакетный подход, как предлагается другими плакатами, но стоит рассмотреть. YMMV