#sql-server #tsql #data-warehouse #sql-data-warehouse
#sql-сервер #tsql #хранилище данных #sql-хранилище данных
Вопрос:
В моей базе данных есть таблица измерений, которая стала слишком большой. При этом я имею в виду, что в нем слишком много записей — более миллиона — потому что он рос с той же скоростью, что и связанные факты. В основном это связано с плохим дизайном, и я пытаюсь его очистить.
Одна из вещей, которые я пытаюсь сделать, — это удалить записи измерений, которые больше не используются. Таблицы фактов регулярно поддерживаются, а старые снимки удаляются. Поскольку измерения не поддерживались таким образом, в таблице есть много строк, значение первичного ключа которых больше не отображается ни в одной из связанных таблиц фактов. Все таблицы фактов имеют ограничения внешнего ключа.
Есть ли способ найти строки таблицы, значение первичного ключа которых больше не отображается ни в одной из таблиц, связанных с ограничением внешнего ключа?
Я попытался написать скрипт для отслеживания этого. В основном это:
select key from dimension
where not exists (select 1 from fact1 where fk = pk)
and not exists (select 1 from fact2 where fk = pk)
and not exists (select 1 from fact3 where fk = pk)
Но при большом количестве связанных таблиц этот запрос умирает через некоторое время — по крайней мере, моя management Studio потерпела крах. Поэтому я не уверен, есть ли какие-либо другие варианты.
Комментарии:
1. Пожалуйста, предоставьте образцы данных и желаемые выходные данные в виде форматированного текста , а не изображений.
2. Это никогда не будет быстро. Наиболее удобный способ — генерировать запросы, которые перемещаются по всем таблицам фактов, чтобы выбрать
DISTINCTзначения для каждого столбца измерения во временной таблице, а затем удалить строки измерения, которые не встречаются в этой таблице. (Некластеризованный) индекс columnstore для этих столбцов значительно ускорил бы это (и, возможно, было бы неплохо иметь его вообще на складе).3. Да, я немного боялся, что так и будет. На самом деле я уже создал код для генерации инструкций для этого. Но, по крайней мере, теперь я знаю, что не упустил лучшего способа сделать это.
Ответ №1:
мы должны были сделать что-то похожее на это у одного из моих клиентов. Запрос, подобный вашему с «не существует …. и не существует …. и не существует ….», выполнялся ~ 22 часа, прежде чем мы изменили нашу стратегию, чтобы справиться с этим за ~ 20 минут.
Как предлагает Nsousa, вам нужно разделить запрос, чтобы SQL Server не обрабатывал все данные за один раз, без необходимости используя tempdb и все другие вещи.
Сначала создайте новую таблицу со всеми ключами в ней. Причина создания этой таблицы заключается в том, чтобы не читать полное сканирование таблицы для каждого запроса, имея больше ключей на странице размером 8 кб, и иметь дело со все меньшим набором ключей после каждого удаления.
create table DimensionkeysToDelete (Dimkey char(32) primary key nonclustered);
insert into DimensionkeysToDelete
select key from dimension order by key;
Затем, вместо удаления неиспользуемого ключа, удалите ключи, существующие в таблице фактов, начиная с таблицы фактов, в которой наименьшее количество строк.
Убедитесь, что таблица фактов имеет надлежащую индексацию для повышения производительности.
delete from DimensionkeysToDelete
from DimensionkeysToDelete d
inner join fact1 on f.fk = d.Dimkey;
delete from DimensionkeysToDelete
from DimensionkeysToDelete d
inner join fact2 on f.fk = d.Dimkey;
delete from DimensionkeysToDelete
from DimensionkeysToDelete d
inner join fact3 on f.fk = d.Dimkey;
После завершения работы со всеми таблицами фактов в DimensionkeysToDelete остаются только неиспользуемые ключи. Чтобы ответить на ваш вопрос, просто выполните select в этой таблице, чтобы получить все неиспользуемые ключи для этого конкретного измерения, или соедините его с измерением, чтобы получить данные.
Но, насколько я понимаю, ваши потребности в очистке вашего хранилища используйте эту таблицу для удаления из исходной таблицы измерений. На этом шаге вы также можете предпринять некоторые действия в целях аудита (например: вставить в таблицу аудита ‘Key ‘ key ‘ deleted on преобразовать (datetime, getdate(),121) ‘ по сценарию X’ …. )
Я думаю, что это можно оптимизировать, взгляните на план выполнения, но мой клиент был доволен этим, поэтому нам не пришлось прилагать к этому особых усилий.
Комментарии:
1. Рискуя заявить очевидное — я предполагаю, что ни одно из измерений, из которых вы хотите удалить записи, не является SCD2 (или аналогичным)? Если они есть, у вас, очевидно, есть более сложная проблема, поскольку вам нужно будет исправить даты начала / окончания оставшихся записей, чтобы устранить любые пробелы
Ответ №2:
Возможно, вы захотите разделить это на разные запросы. Проверьте неиспользуемые строки в fact1, затем в fact2 и т.д. по отдельности. Затем пересеките все эти результаты, чтобы получить неиспользуемые строки во всех таблицах фактов.
Я бы также предложил использовать левое внешнее соединение вместо вложенных запросов, подсчитывая строки в таблице фактов для каждого pk и отфильтровывая из результирующего набора те, которые имеют ненулевое количество.
Ваш запрос будет испытывать трудности, поскольку он будет сканировать каждую таблицу фактов одновременно.