Что лучше делать в хранимой процедуре или программе SQL?
Я стажер в компании, и мне и моим коллегам-стажерам поручено создать панель отчетности. Мы используем MVC, API и SQL Server.
В настоящее время мы получаем данные для панели управления с помощью 9 различных хранимых процедур. Эти хранимые процедуры выполняют некоторые вычисления с использованием агрегатных функций (например, сколько продаж кто-то совершил, среди прочего) и фильтруются в соответствии с диапазоном дат (а также некоторыми другими фильтрами, такими как продукты). Один сохраненный набор результатов процедуры, как правило, возвращает от 100 до 200 строк, рассчитанных из примерно 70 000 строк необработанных данных в базе данных - если фильтр диапазона дат установлен на один месяц. Итак, мы получаем около 9 таблиц по 100-200 строк в каждой, чтобы заполнить нашу панель инструментов.
Наша проблема заключается в том, что пользователь может изменять различные фильтры, что в настоящее время требует от нас снова получить все девять наборов результатов из базы данных, и в настоящее время это немного медленно - для обновления требуется около 15 секунд или больше.
Один из стажеров в моей команде хочет изменить код, чтобы все данные вызывались из базы данных в необработанном формате (то есть 70000 строк в базе данных для каждой сохраненной процедуры) без фильтрации, а затем применить пользовательские фильтры и расчеты на C#. Конечно, это убирает фильтры диапазона дат, что означает, что будут возвращены все данные в базе данных, поэтому вместо 70 000 строк каждая хранимая процедура будет возвращать около 2 520 000 строк, так как базе данных около 3 лет ... и из конечно, общее количество строк будет увеличиваться каждый месяц.
Понятия не имею, хорошая ли это идея ... Не знаю, повысит ли это производительность или нет. Итак, у меня в основном два основных вопроса:
- Сможет ли C# выполнять эти вычисления (сколько продаж совершил человек) быстрее и эффективнее, чем SQL?
- Будет ли наличие 9 таблиц с примерно 2,5 миллионами строк данных (и подсчетом), каждая из которых вызывается в интерфейсную часть, в любом случае не приведет к замедлению всего, что делает любые преимущества недействительными.
Другое предложение заключалось в том, чтобы потратить время на улучшение самих хранимых процедур и изменить интерфейс так, чтобы эти хранимые процессы вызывались параллельно, а не последовательно, что мы и делаем сейчас.
ОТВЕЧАТЬ: Хорошо, поэтому после прочтения всех ответов, казалось бы, можно будет исправить эти сохраненные процессы и улучшить их производительность. Хотя C#, возможно, может выполнять вычисления быстрее, он никоим образом не компенсирует ресурсы, необходимые для перемещения данных и их хранения, и эти хранимые процессы не должны быть такими медленными, как в любом случае.
Мы потенциально могли бы сделать это с помощью C# с включенными фильтрами, но делать это на SQL кажется лучше.
есть ли способ выбрать любой ответ в качестве ответа?
Не рекомендуется удалять фильтры
В первую очередь вам следует задать вопрос: почему запросы выполняются медленно? Агрегирование 70K строк не должно занимать 15 секунд, равно как и выбор 70K строк из 2,5M. Какое из них является узким местом? Во-первых, определите, является ли выбор 70K из 2,5M вашим узким местом или агрегированием. Если выбор выполняется медленно, убедитесь, что вы правильно используете доступные индексы; в противном случае убедитесь, что требуемые индексы доступны в таблице (ах). Если медленным является только агрегирование, возможно, что-то не так в том, как это изложено. В качестве альтернативы возьмите 70 КБ строк и выполните агрегирование на C#; но фильтр в БД.
Можно ли протестировать получение всех данных обратно - это может добавить совершенно недопустимую задержку при запуске приложения. Кроме того, вам когда-либо понадобится текущая информация - и в этом случае вам потребуется либо больше хранимых процедур для получения последних данных, либо снова получить все 20 с лишним миллионов записей.
Однозначного ответа на этот вопрос нет, поскольку есть аргументы в пользу обоих. Мне действительно сложно провести надлежащее модульное тестирование, когда многие функции реализованы только как SQL и функции / процедуры SQL, но если вы можете управлять этой частью, я определенно рассмотрю это. Однако вы также можете более эффективно выполнять вычисления на C#, поскольку SQL по своей природе привередлив в отношении сложных структур данных и императивной логики, в нашем продукте у нас есть вычисления, которые мы не можем получить эффективную реализацию на SQL, не изобретая новый язык программирования для Это. Итак +/-
Ответы 1
Рекомендуется проводить расчеты с помощью хранимых процедур. Это экономит время. Убедитесь, что все вычисления не выполняются в одной хранимой процедуре. Вы можете создавать разные процедуры и иметь одну последнюю процедуру для объединения всех хранимых процедур. Более того, вы также можете использовать функции. Используйте свойства sql server в своих интересах. Хранимые процедуры будут быстрее, чем вычисления в C#. Убедитесь, что вы установили время ожидания при вызове хранимой процедуры в C#. Это очень полезно, иначе вы не получите ожидаемых результатов.
Хорошо настроенная программа C# может выполнять вычисления быстрее, чем механизм SQL, особенно если вы распределяете работу по потокам, но тогда проблема заключается в том, что ресурсы, потраченные на перемещение необходимых C# данных из базы данных в память (и сохранение результата на обратном пути) , если нужно). Механизм SQL также использует свою магию sql (индексы и статистика) для оптимизации этапа выбора строк, что может не быть лучше в вашем приложении C# (если вы хотите фильтровать, а не просто выполнять вычисления).