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

Ответ 1

Попробуйте добавить индекс в Stories.LastActivityAt. Я думаю, что кластеризованное сканирование индекса в плане выполнения может быть связано с сортировкой.

Изменить: Поскольку мой запрос возвращался в одно мгновение с строками длиной всего несколько байтов, но уже работает уже 5 минут и все еще продолжается после того, как я добавил 2K varchar, я думаю, что у Митча есть точка. Это объем данных, которые перемещаются вокруг ничего, но это может быть исправлено в запросе.

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

Вот так:

select * from 
(
    SELECT TOP(10) id, categoryID, userID
    FROM Stories
    ORDER BY Stories.LastActivityAt
) s
INNER JOIN Stories ON Stories.ID = s.id
INNER JOIN Categories ON Categories.ID = s.CategoryID
INNER JOIN Users ON Users.ID = s.UserID

Если у вас есть указатель на LastActivityAt, это должно выполняться очень быстро.

Ответ 2

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

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

Ответ 3

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

второй, есть ли триггер? Что-то, что обновит поле LastActivityAt?

Ответ 4

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

Ответ 5

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

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

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

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

Ответ 6

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

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

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

Здесь script я использовал все это.

    --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 @[email protected]+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, а большая таблица вызывает добавление LOTS. Наличие индекса в этом столбце, безусловно, улучшит производительность по порядку.

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

Ваш метод, указанный в вашем коде, является элегантным, и в основном тот же ответ, что и cdonner, за исключением С#, а не sql. Настройка db, вероятно, даст еще лучшие результаты!

- Kris

Ответ 7

Удалили ли вы кеш SQL Server перед запуском каждого запроса?

В SQL 2000 это похоже на DBCC DROPCLEANBUFFERS. Google для получения дополнительной информации.

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

Categories.ID Stories.CategoryID Users.ID Stories.UserID

и, возможно, Stories.LastActivityAt

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

Ответ 8

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

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