Рекомендуемые индексы для запроса в большой таблице, включающей "диапазон дат" и "идентификатор заказа"

Сколько строк вы ожидаете иметь между типичным диапазоном дат? Вы обычно смотрите на месяц за раз?

Я бы начал с индекса по столбцу [Date]. Если для типичного запроса полученное количество строк невелико, вам не нужно добавлять столбец [OrderId] в свой индекс.

С другой стороны, если у вас есть большое количество строк в типичном месяце, вы можете добавить столбец [OrderId] в индекс, хотя, поскольку он рассматривается как логическое значение, он может не купить вам много. Это зависит от того, сколько строк у NULL против NOT NULL. Если у вас много строк в течение данного месяца, но только некоторые из них имеют действительный [OrderId], то индекс, вероятно, повысит производительность.

Прочтите принятый ответ в этом связанном вопросе и определите, стоит ли индексировать дополнительный столбец:

Следует ли индексировать битовое поле в SQL Server?

И, конечно же, протестируйте индексы и планы, созданные с помощью индекса и без.

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

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

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

Также стоит подумать, нужно ли вам хранить в SiteVisit строки, у которых нет KnownRefererId в вашей таблице KnownReferer, и у которых есть Null OrderId. Если они вам не нужны, измените их удаление из таблицы и измените кластерный индекс так, чтобы он находился как на SiteVisitId, так и на Date, и запрос должен быть довольно быстрым.

Но я уверен, что вы не зря храните эти лишние строки.

Если вы действительно хотите оптимизировать bejesus из этого запроса и можете принять немного более медленную вставку в таблицу, вы должны создать индекс на: -

(Date, OrderId, SiteVisitId, Domain, Referer, CampaignId)

Это позволит базе данных полностью возвращать ответ из индекса без какой-либо сортировки или доступа к отдельной таблице.

Я бы создал индекс для столбцов Date и OrderId и INCLUDE SiteVisitId, Referer, CampaignId (при условии, что вы используете SQL Server 2005 и далее). Также создайте индекс в столбце внешнего ключа KnownRefererId.

Учитывая, что это отчетный запрос и может выдержать нечетную незавершенную строку, я бы предложил использовать NOLOCK (или подсказку READ UNCOMMITED):

using (var trans = new TransactionScope(TransactionScopeOption.Required,
                      new TransactionOptions
                      {
                          IsolationLevel = IsolationLevel.ReadUncommitted
                      }))
{
    // Put your linq to sql query here
}

Ссылка.

Предостережение: используйте подсказки NOLOCK только там, где у вас есть очень веская причина. В прошлом я видел, как разработчики терпели беду из-за использования одеял!

SELECT TOP 1000
  t0.SiteVisitId, t0.OrderId, t0.Date, 
  t1.Domain, t0.Referer, t0.CampaignId
FROM 
  SiteVisit AS t0
LEFT OUTER JOIN KnownReferer AS t1 ON t1.KnownRefererId = t0.KnownRefererId
WHERE
  t0.Date <= @p0 
  AND t0.Date >= @p1
  AND t0.OrderId IS NOT NULL
ORDER BY 
  t0.Date DESC

@p0='2008-11-1 23:59:59:000', @p1='2008-10-1 00:00:00:000'

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

Если предположить, что в диапазоне дат в 1 месяц много строк и относительно мало из них имеют OrderId IS NULL, лучше всего будет использовать кластеризованный индекс по дате. Это должно дать вам сканирование кластерного индекса с результатами, упорядоченными для вашей TOP 1000.

Вы также можете захотеть, чтобы KnownReferer.KnownRefererId был либо кластеризованным индексом, либо комбинированным индексом с knownRefererId + Domain, чтобы избежать поиска в этой таблице. Я предполагаю, что количество KnownReferers невелико, поэтому я не ожидал бы большой выгоды от этого.