Система голосования. Должен ли я использовать SQL Trigger или больше кода?
Я создаю систему голосования, в которой каждый голос фиксируется в таблице голосов с UserID и DateTime голосования вместе с int из 1 или -1.
Я также держу промежуточный итог TotalVotes в таблице, содержащей элемент, за который пользователь фактически проголосовал. Таким образом, я не постоянно запускаю запрос SUM таблица голосования.
Мой вопрос является своего рода вопросом за / против, когда дело доходит до обновления поля TotalVotes. Что касается управляемости кода, добавление дополнительного метода обновления в приложение позволяет легко устранять неполадки и обнаруживать любые потенциальные проблемы. Но если это приложение значительно расширится в своей пользовательской базе, это может вызвать много дополнительных вызовов SQL из приложения в БД. Использование триггера сохраняет его "все в семействе sql", так сказать, и должно добавить небольшое повышение производительности, а также не допустить обыденной активности за пределы базы кода.
Я понимаю, что в этом конкретном вопросе можно было бы назвать преждевременную оптимизацию, но, поскольку я еще не построил ее, я мог бы также попытаться найти лучший подход прямо из ворот.
Лично я склоняюсь к курку. Пожалуйста, дайте мне ваши мысли / рассуждения.
5 ответов
Я использовал триггерный метод годами и всегда был счастливее. Так что, как говорится, "давай, вода в порядке". Однако обычно я делаю это, когда задействовано много таблиц, а не одна.
Плюсы / минусы хорошо известны. Материализация значения - это решение "заплати мне сейчас", вы платите немного больше за вкладыш, чтобы быстрее читать. Это путь, если и только если вы хотите чтение в 5 миллисекунд вместо 500 миллисекунд.
PRO: TotalVotes всегда будет доступна сразу после одного чтения.
PRO: Вам не нужно беспокоиться о пути к коду, код, который делает вставку, намного проще. Умножение на множество таблиц в больших приложениях - это большое преимущество для удобства обслуживания.
CON: за каждую вставку вы также платите с дополнительным обновлением. Требуется намного больше вставок в секунду, чем думает большинство людей, прежде чем вы заметите это.
CON: для многих таблиц ручное кодирование триггеров может быть сложным. Я рекомендую генератор кода, но, поскольку я написал единственный, о котором я знаю, это привело бы меня на территорию саморекламы. Если у вас есть только одна таблица, просто закодируйте ее вручную.
CON: чтобы обеспечить полную корректность, не должно быть возможности выдавать UPDATE из консоли или кода для изменения TotalVotes. Это значит, что это сложнее. Триггер должен работать как специальный суперпользователь, который обычно не используется. Второй триггер в родительской таблице срабатывает при UPDATE и предотвращает изменения в TotalVotes, если только пользователь, производящий обновление, не является этим специальным суперпользователем.
Надеюсь, это даст вам достаточно, чтобы решить.
Другой вариант - создать представление таблицы голосов, объединяющее голоса в виде TotalVotes. Затем индексируйте представление.
Волшебство оптимизатора SQL Server (я думаю, только для корпоративного выпуска) заключается в том, что когда он видит запросы суммы (voiceColumn), он выбирает это значение из индекса для представления тех же данных, что удивительно, если учесть, что вы не ссылаясь на представление непосредственно в вашем запросе!
Если у вас нет корпоративной версии, вы можете запросить общее количество голосов в представлении, а не в таблице, а затем воспользоваться индексом.
Индексы - это, по сути, денормализация ваших данных, о которых знает оптимизатор. Вы создаете или отбрасываете их по мере необходимости и позволяете оптимизатору выяснить это (никаких изменений в коде не требуется). Как только вы начнете идти по пути собственной денормализации, созданной вами, вы будете встраиваться в ваш код на долгие годы.
Проверьте Улучшение производительности с индексированными представлениями
Есть несколько конкретных критериев, которые должны быть соблюдены для работы индексированных представлений. Вот пример, основанный на предположении вашей модели данных:
create database indexdemo
go
create table votes(id int identity primary key, ItemToVoteOn int, vote int not null)
go
CREATE VIEW dbo.VoteCount WITH SCHEMABINDING AS
select ItemToVoteOn, SUM(vote) as TotalVotes, COUNT_BIG(*) as CountOfVotes from dbo.votes group by ItemToVoteOn
go
CREATE UNIQUE CLUSTERED INDEX VoteCount_IndexedView ON dbo.VoteCount(itemtovoteon)
go
insert into votes values(1,1)
insert into votes values(1,1)
insert into votes values(2,1)
insert into votes values(2,1)
insert into votes values(2,1)
go
select ItemToVoteOn, SUM(vote) as TotalVotes from dbo.votes group by ItemToVoteOn
И этот запрос (который не ссылается на представление или, по расширению, его индекс) приводит к этому плану выполнения. Обратите внимание, индекс используется. Конечно, падение индекса (и повышение производительности вставки)
И последнее слово. До тех пор, пока вы не начнете работать, есть действительно известный способ узнать, поможет ли какая-либо денормализация на самом деле общей пропускной способности. С помощью индексов вы можете создавать их, измерять, помогает ли это или причиняет боль, а затем сохранять или отбрасывать их по мере необходимости. Это единственный вид денормализации для производительности, который можно сделать безопасно.
Преждевременная преждевременная оптимизация сохраняет итоги в таблице, а не просто суммирует данные по мере необходимости. Вам действительно нужно денормализовать данные для повышения производительности?
Если вам не нужно денормализовать данные, вам не нужно писать триггер.
Я бы посоветовал вам создать хранимую процедуру, в которую будет вставляться как голосование, так и обновление общего количества голосов. Тогда ваше приложение должно знать только, как записать голосование, но логика того, что происходит, когда вы звоните, все еще содержится в одном месте (хранимая процедура, а не специальный запрос на обновление и отдельный триггер).,
Это также означает, что позже, если вы хотите удалить обновление для общего количества голосов, все, что вам нужно изменить, - это процедура, закомментировав часть обновления.
Моим первым инстинктом было бы написать UDF для выполнения операции SUM и сделать TotalVotes вычисляемый столбец на основе этого UDF.