Подтвердить что ты не робот

SQL Group по годам, месяцам, неделям, дням, часам SQL и процедурной производительности

Мне нужно написать запрос, который будет группировать большое количество записей по периодам времени от года к часу.

Мой первоначальный подход состоял в том, чтобы решить периоды процедурно на С#, перебирать каждый из них и запускать SQL для получения данных за этот период, создавая набор данных по мере того, как я иду.

SELECT Sum(someValues)
FROM table1
WHERE deliveryDate BETWEEN @fromDate AND @ toDate

Впоследствии я обнаружил, что могу группировать записи с помощью Year(), Month() Day() и datepart (неделя, дата) и datepart (hh, date).

SELECT Sum(someValues)
FROM table1
GROUP BY Year(deliveryDate), Month(deliveryDate), Day(deliveryDate)

Я обеспокоен тем, что использование datepart в группе приведет к худшей производительности, чем выполнение запроса несколько раз за определенный период времени из-за невозможности использовать индекс в поле datetime так же эффективно; любые мысли относительно того, правда ли это?

Спасибо.

4b9b3361

ОТВЕТЫ

Ответ 1

Как и для любой производительности, связанной с Мерой

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

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

В частности

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

Ответ 2

Если вы поместите формулу в полевую часть сравнения, , вы получите сканирование таблицы.

Индекс находится в поле, а не в datepart (поле), , поэтому все поля должны быть рассчитаны - поэтому я думаю, что ваша догадка правильная.

Ответ 3

вы можете сделать что-то похожее на это:

SELECT Sum(someValues)
FROM 
(
    SELECT *, Year(deliveryDate) as Y, Month(deliveryDate) as M, Day(deliveryDate) as D
    FROM table1
    WHERE deliveryDate BETWEEN @fromDate AND @ toDate
) t
GROUP BY Y, M, D

Ответ 4

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

Создайте таблицу, которую я назову ALMANAC, с такими столбцами, как день недели, месяц, год. Вы даже можете добавить столбцы для специфических для компании характеристик даты, например, является ли дата праздником компании или нет. Возможно, вы захотите добавить начальную и конечную временную метку, как показано ниже.

Хотя вы можете обойтись с одной строкой в ​​день, когда я это сделал, мне было удобно идти с одной строкой за смену, где в течение трех смен в день. Даже при таком раскладе период в десять лет составлял немногим более 10 000 строк.

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

Ответ 5

Я искал аналогичное решение для целей отчетности и наткнулся на эту статью под названием Group by Month (и другие периоды времени). Он показывает различные способы, хорошие и плохие, группировать поле datetime. Определенно стоит посмотреть.

Ответ 6

Я думаю, что вы должны сравнить его, чтобы получить надежные результаты, но IMHO и моя первая мысль заключалась в том, что позволить DB позаботиться об этом (ваш второй подход) будет намного быстрее, чем когда вы это сделаете в своем клиентском коде, С вашего первого подхода у вас есть несколько обращений к БД, что, я думаю, будет намного дороже.:)

Ответ 7

Возможно, вам захочется взглянуть на мерный подход (это похоже на то, что предложил Уолтер Митти), где каждая строка имеет внешний ключ для измерения даты и/или времени. Это позволяет очень гибко суммировать через соединение к этой таблице, где эти части предварительно рассчитаны. В этих случаях ключ обычно представляет собой натуральный целочисленный ключ формы YYYYMMDD и HHMMSS, который является относительно совершенным и также читаемым человеком.

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

Или вычисленные столбцы.

Но производительность должна быть проверена и рассмотрены планы выполнения...