Лучший способ выполнить алгоритм сжатия таблиц на SQL Server 2012 Express

Позвольте мне начать с того, что у меня ограниченный опыт работы с SQL, но я искал несколько дней, чтобы найти хорошее решение для своей проблемы, и пока не нашел его. Если существует очевидный ответ, я не использовал правильные ключевые слова, чтобы найти его.

У меня есть аппаратная система сбора данных, которая вставляет строку состояния системы в таблицу каждые 10 минут в течение длительных периодов времени. Данные используются в качестве диагностической информации в случае сбоя оборудования. Таблица содержит записи для нескольких устройств, которые идентифицируются с помощью уникального столбца DeviceID. Я планирую хранить не более 100 тыс. записей в этой таблице для каждого устройства. Планирую изредка уплотнять таблицу по следующему алгоритму:

Когда количество строк для определенного DeviceID превышает 100 КБ, выберите самые старые 50% строк для определенного DeviceID и удалите каждую вторую из этих строк, начиная со второй строки в выборе.

Этот алгоритм имеет желаемую функцию, заключающуюся в том, что самые последние данные сохраняются с 10-минутными интервалами, а более старые данные постепенно расширяются во времени. Также первая (самая старая) запись никогда не удаляется.

Я могу легко реализовать это на C# с несколькими удалениями одной строки после запроса на выбор самых старых 50 000 строк. Я надеюсь, что кто-то знает гораздо лучший способ сделать это.

Если это поможет, я могу реорганизовать данные, чтобы у каждого устройства была отдельная таблица, но это усложняет представление данных для просмотра в одном виртуализирующем представлении сетки на C#.

Спасибо за вашу помощь.


sql delete-row sql-server-2012-express
person Jesse Ursery    schedule 03.04.2013    source источник
comment
То, к чему вы клоните, имеет смысл. Тем не менее, вы должны включить некоторые вещи, которые вы пробовали, чтобы помочь другим определить наилучший курс действий, чтобы помочь вам.   -  person Brad Fox    schedule 04.04.2013
comment
Я мало пробовал, потому что не знаю, с чего начать. Я не могу использовать свой табличный индекс, потому что он становится все более разреженным после каждой итерации сжатия. Я рассмотрел использование ROW_NUMBER(), но, похоже, это относится только к таблицам, а не к наборам результатов. Я начал читать о курсорах и подозреваю, что лучшим решением будет использование курсора для пропуска каждой второй строки при удалении строк, но это может иметь неприятные последствия, поскольку результирующий набор уменьшается каждый раз, когда я удаляю строку.   -  person Jesse Ursery    schedule 04.04.2013
comment
P.S. Спасибо за быстрый ответ. Я использовал StackOverflow много раз, чтобы найти отличные решения для всех моих вопросов по программированию, но я никогда не задавал вопросов и понятия не имел, как быстро я могу получить ответ.   -  person Jesse Ursery    schedule 04.04.2013

Ответы (1)


arrow_upward
0
arrow_downward

Я думаю, что нашел решение своей проблемы здесь (Удаление каждой n-й строки SQL), используя обычное табличное выражение. В следующем коде HealthID — это индекс в моей таблице DeviceHealth. Предложение DeviceID = 1 предназначено только для тестирования. В реальном коде вместо 1 будет использоваться параметр.

WITH cte AS (
    SELECT TOP 50 PERCENT t.*, ROW_NUMBER() OVER (ORDER BY t.HealthID)
        AS rank FROM [DeviceHealth] t WHERE DeviceID = 1)
DELETE cte WHERE rank%2 != 0

Кажется, это работает. Есть ли лучший способ сделать это или нашли хорошее решение?

person Jesse Ursery    schedule 04.04.2013