Запрос против 250k строк, занимающих 53 секунды

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

во-вторых, существует ли включенный триггер? Что-то, что обновило бы поле LastActivityAt?

Таким образом, если я считал первую часть правильно, она отвечает за 17 секунд индексом. Который должен все еще некоторое время выпить залпом 10 записей. Я думаю, что время находится в порядке пунктом. Я хотел бы индекс на LastActivityAt, UserID, CategoryID. Только для забавы, удалите порядок и посмотрите, возвращает ли это 10 записей быстро. Если это делает, то Вы знаете, что это не находится в соединениях к другим таблицам. Также было бы полезно заменить * столбцами, необходимыми, как все 3 столбца таблицы находятся в tempdb, поскольку Вы сортируете - как Neil упомянул.

При рассмотрении планов выполнения, Вы заметите дополнительный вид - я полагаю, что это - порядок, согласно которому собирается занять время. Я предполагаю, что у Вас был индекс с 3, и это были 17 секунд..., таким образом, можно хотеть один индекс для критериев соединения (идентификатор пользователя, categoryID), и другой для lastactivityat - видит, работает ли это лучше. Также было бы хорошо выполнить запрос через индексный настраивающий мастер.

Вы истратили Диски в своей настройке оборудования.

Учитывая Ваши комментарии о Вашем размещении Файла Data/Log/tempDB, я думаю, что любой объем настройки будет лейкопластырем.

250 000 строк являются небольшими. Вообразите, как плохо Ваши проблемы будут с 10 миллионами строк.

Я предлагаю, чтобы Вы перешли tempDB на его собственный физический диск (предпочтительный RAID 0).

Хорошо, таким образом, моя тестовая машина не быстра. На самом деле это действительно медленно. Это 1,6 ГГц, n 1 ГБ поршня, Никаких нескольких дисков, просто сингл (медленное чтение) диск для SQL-сервера, OS и отдельно оплачиваемых предметов.

Я составил Ваши таблицы с первичными и определенными внешними ключами. Вставленный 2 категории, 500 случайных пользователей и 250 000 случайных историй.

Выполнение первого запроса выше взятий 16 секунд (никакой кэш плана любой). Если я индексирую столбец LastActivityAt, я вкладываю результаты менее чем секунда (никакой кэш плана здесь любой).

Вот сценарий, я раньше делал все это.

    --Categories table --
Create table Categories (
[ID] [int] IDENTITY(1,1) primary key NOT NULL,
[ShortName] [nvarchar](8) NOT NULL,
[Name] [nvarchar](64) NOT NULL)

--Users table --
Create table Users(
[ID] [int] IDENTITY(1,1) primary key NOT NULL,
[Username] [nvarchar](32) NOT NULL,
[Password] [nvarchar](64) NOT NULL,
[Email] [nvarchar](320) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[LastActivityAt] [datetime] NOT NULL
)
go

-- Stories table --
Create table Stories(
[ID] [int] IDENTITY(1,1) primary key NOT NULL,
[UserID] [int] NOT NULL references Users ,
[CategoryID] [int] NOT NULL references Categories,
[VoteCount] [int] NOT NULL,
[CommentCount] [int] NOT NULL,
[Title] [nvarchar](96) NOT NULL,
[Description] [nvarchar](1024) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[UniqueName] [nvarchar](96) NOT NULL,
[Url] [nvarchar](512) NOT NULL,
[LastActivityAt] [datetime] NOT NULL)

Insert into Categories (ShortName, Name) 
Values ('cat1', 'Test Category One')

Insert into Categories (ShortName, Name) 
Values ('cat2', 'Test Category Two')

--Dummy Users
Insert into Users
Select top 500
UserName=left(SO.name+SC.name, 32)
, Password=left(reverse(SC.name+SO.name), 64)
, Email=Left(SO.name, 128)+'@'+left(SC.name, 123)+'.com'
, CreatedAt='1899-12-31'
, LastActivityAt=GETDATE()
from sysobjects SO 
Inner Join syscolumns SC on SO.id=SC.id
go

--dummy stories!
-- A Count is given every 10000 record inserts (could be faster)
-- RBAR method!
set nocount on
Declare @count as bigint
Set @count = 0
begin transaction
while @count<=250000
begin
Insert into Stories
Select
  USERID=floor(((500 + 1) - 1) * RAND() + 1)
, CategoryID=floor(((2 + 1) - 1) * RAND() + 1)
, votecount=floor(((10 + 1) - 1) * RAND() + 1)
, commentcount=floor(((8 + 1) - 1) * RAND() + 1)
, Title=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))
, Description=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))
, CreatedAt='1899-12-31'
, UniqueName=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36)) 
, Url=Cast(NEWID() as VARCHAR(36))+Cast(NEWID() as VARCHAR(36))
, LastActivityAt=Dateadd(day, -floor(((600 + 1) - 1) * RAND() + 1), GETDATE())
If @count % 10000=0
Begin
Print @count
Commit
begin transaction
End
Set @count=@count+1
end 
set nocount off
go

--returns in 16 seconds
DBCC DROPCLEANBUFFERS
SELECT TOP(10) *
FROM Stories
INNER JOIN Categories ON Categories.ID = Stories.CategoryID
INNER JOIN Users ON Users.ID = Stories.UserID
ORDER BY Stories.LastActivityAt
go

--Now create an index
Create index IX_LastADate on Stories (LastActivityAt asc)
go
--With an index returns in less than a second
DBCC DROPCLEANBUFFERS
SELECT TOP(10) *
FROM Stories
INNER JOIN Categories ON Categories.ID = Stories.CategoryID
INNER JOIN Users ON Users.ID = Stories.UserID
ORDER BY Stories.LastActivityAt
go

Вид определенно, где Ваш замедляться происходит. Сортировка главным образом сделана в tempdb, и большая таблица заставит ПАРТИИ быть добавленными. Наличие индекса на этом столбце определенно улучшит производительность на порядке.

Кроме того, определение Ваших Первичных и Внешних ключей помогает SQL Server immensly

Ваш метод, который перечислен в Вашем коде, изящен, и в основном тот же ответ, который cdonner записал кроме c# и не sql. Настройка дб, вероятно, даст еще лучшие результаты!

- Крис

На основе Вашего проблемного запроса попытка добавляет индекс комбинации на таблице Stories (CategoryID, UserID, LastActivityAt)

Вы очистили кэш SQL Server прежде, чем выполнить каждый запрос?

В SQL 2000 это - что-то как DBCC DROPCLEANBUFFERS. Google команда для большего количества информации.

Смотря на запрос, у меня был бы индекс для

Категории. Идентификационные истории. Пользователи CategoryID. Идентификационные истории. UserID

и возможно Истории. LastActivityAt

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

Когда Вы работали с SQL Server в течение некоторого времени, Вы обнаружите, что даже самые маленькие изменения в запросе могут вызвать дико различное время отклика. Из того, что я читал в начальном вопросе и рассмотрении плана запросов, я подозреваю, что оптимизатор решил, что лучший подход должен сформировать частичный результат и затем вид что как отдельный шаг. Частичным результатом является составной объект таблиц Users и Stories. Это формируется в tempdb. Таким образом, чрезмерный доступ к диску происходит из-за формирования и затем сортировки этой временной таблицы.

Я соглашаюсь, что решение должно состоять в том, чтобы создать составной индекс на Историях. LastActivityAt, Истории. UserId, Истории. CategoryId. Порядок ОЧЕНЬ важен, поле LastActivityAt должно быть первым.