Здравствуйте, Ocenochka, Вы писали:

O> Вы мне лучше скажите что нужно сделать, чтобы пейджировать 500к записей в одной таблице быстрее чем за секунду на среднем железе.

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

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

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

Но даже если не хочется использовать тотальное кеширование, то на этот случай клиенты шлют специальный хидер (IF-MODIFIED-SINCE), которым дают понять серверной стороне, что есть некие данные от такого-то числа. Данные с тех пор обновились или нет? Тогда остается только проверить изменения и отправить клиенту либо обновленную страницу либо выставить статус 304.

Здравствуйте, Ocenochka, Вы писали:

O>Здравствуйте, samius, Вы писали:

O>>> Я думаю, что чтение маленького файла быстрее чем запрос в другой процесс, в котором нагромождено куча всего + все равно идет работа с файлом.
S>>Это ошибочное мнение в общем случае.

O> Не плохо было бы аргументировать.
Выборка по индексу может работать быстрее чем чтение всего файла в общем случае. Специально не делаю сильных утвреждений.
полагаю, adontz уже убедил в этом.

O>>> Я пробовал (без транзакционной работы) вводить файлы и скорость работы с ними была в несколько раз выше (всегда — мгновенная) даже по сравнению с простыми запросами к БД.
S>>А как насчет непростых запросов к файлам?

O> Непростые запросы к файлам мне не нужны — на то они и файлы.
а как тогда по поводу простых запросов к индексированной таблице?

S>>>>Значит встает вопрос синхронизации кэша с таблицей.
O>>> Конечно встает, но в нем я сложностей не вижу — раз в сутки перестраивать в моем случае допустимо. Главное — транзакционно.
S>>Транзакционно и раз в сутки?

O> А что не так? Ну может раз в час... я пока не знаю сколько ресурсов на это будет уходить.
Транзакции нужны для того что бы данные в БД были согласованные. Если синхронизировать кэш раз в час, то можно считать данные кэша будут согласованы мгновение в час.
Или речь о том что бы запретить модификацию данных на время перестроения кэша?

O> Гуглил я такое уже ) И как создавать индексы я знаю.
O> Вы мне лучше скажите что нужно сделать, чтобы пейджировать 500к записей в одной таблице быстрее чем за секунду на среднем железе.

Накидать adontz-у спасибов раза 4 как минимум.
Во всяком случае, полагаю что нужда в велосипедостроении отпала.

Здравствуйте, Ocenochka, Вы писали:

O> Открыли глаза — я был уверен, что это невозможно.
O> Подскажите — чтобы понять как это работает нужно про CTE читать?
O> Т.е. почему так быстро получается, ведь обходить всю таблицу и сортировать ее все равно нужно, а это не может быть быстрее 'тупой' выборки данных?

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

Здравствуйте, rameel, Вы писали:

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

R>При должном подходе можно добиться кеширования на всем пути прохождения страницы до клиента от прокси и кеша IIS до браузера на заданный тобою в заголовках период.

IIS замечательно умеет кешировать готовые страницы.
Т.е., рекомендую дополнительно ко всем предложенным решениям по оптимизации выборки из БД использовать кеширование на стороне IIS.
Если настроить кеширование для страницы целиком не представляется возможным (например, на странице отображается информация о текущем пользователе), то можно вынести таблицу, отображающую одну страницу данных в отдельный ascx-контрол. Для этого контрола настроить кеширование на нужное время и с вариацией по параметрам. Подробнее см. Caching Portions of an ASP.NET Page.

Здравствуйте, Ocenochka, Вы писали:

O> Подскажите — чтобы понять как это работает нужно про CTE читать?

В запросе есть CTE, но это не нлавный момент.

O> Т.е. почему так быстро получается, ведь обходить всю таблицу и сортировать ее все равно нужно

Нет не нужно. Читайте про кластерные индексы.

Здравствуйте, adontz, Вы писали:

O>> Подскажите — чтобы понять как это работает нужно про CTE читать?
A>В запросе есть CTE, но это не нлавный момент.

O>> Т.е. почему так быстро получается, ведь обходить всю таблицу и сортировать ее все равно нужно
A>Нет не нужно. Читайте про кластерные индексы.

У меня быстро работает без кластерных индексов.
Я ничего не менял, просто запустил запрос на своей таблице, а на таблице только кластерный индекс по id (pk) и не кластерный по added.
Причем быстро работает (1 сек) при сортировке не только по added, а по любым имеющимся столбцам на которых вообще никаких индексов нет.
Прямо магия.

Здравствуйте, rameel, Вы писали:

O>> Вы мне лучше скажите что нужно сделать, чтобы пейджировать 500к записей в одной таблице быстрее чем за секунду на среднем железе.
R>Как написать запрос так, чтобы отбирались только нужные в данный момент данные, а не все подряд, уже подсказали.
R>Если хочется еще более лучшей отзывчивости сайта, то надо научиться пользоваться управлять клиентским кешированием, выставлять странице правильные заголовки.
R>При должном подходе можно добиться кеширования на всем пути прохождения страницы до клиента от прокси и кеша IIS до браузера на заданный тобою в заголовках период.
R>Но даже если не хочется использовать тотальное кеширование, то на этот случай клиенты шлют специальный хидер (IF-MODIFIED-SINCE), которым дают понять серверной стороне, что есть некие данные от такого-то числа. Данные с тех пор обновились или нет? Тогда остается только проверить изменения и отправить клиенту либо обновленную страницу либо выставить статус 304.

Это я уже делаю — и кэширование на клиенте, сервере и промежуточным серверах и expired правильный выставляю и javascript с css минимизирую и в один файл их сшиваю...
Еще etag можно будет прикрутить, и в mvc 3 можно контролы еще кэшировать...
Это для меня не так сложно, как понять правильную работу с БД.

Здравствуйте, samius, Вы писали:

O>>>> Я думаю, что чтение маленького файла быстрее чем запрос в другой процесс, в котором нагромождено куча всего + все равно идет работа с файлом.
S>>>Это ошибочное мнение в общем случае.
O>> Не плохо было бы аргументировать.
S>Выборка по индексу может работать быстрее чем чтение всего файла в общем случае. Специально не делаю сильных утвреждений.
S>полагаю, adontz уже убедил в этом.

Убедил ) Но я ведь то же собирался все не в один файл складывать, а по частям разбивать.
Сейчас нужный мне запрос отрабатывает секунду, в то время как расчитанный, отсортированный и сохраненный по файлам он бы _мгновенно_ работал,
но тут уже слишком много проблем реализации, поэтому теперь с БД действительно проще.

O>>>> Я пробовал (без транзакционной работы) вводить файлы и скорость работы с ними была в несколько раз выше (всегда — мгновенная) даже по сравнению с простыми запросами к БД.
S>>>А как насчет непростых запросов к файлам?
O>> Непростые запросы к файлам мне не нужны — на то они и файлы.
S>а как тогда по поводу простых запросов к индексированной таблице?

На индексы место в БД нужно, а его нет. Хотя с запросами adontz'а я готов и место докупить )
Я думал придется часть полей всех записей дублировать (в кластерном индексе) для быстрого запроса, но как видно это не нужно.

S>>>>>Значит встает вопрос синхронизации кэша с таблицей.
O>>>> Конечно встает, но в нем я сложностей не вижу — раз в сутки перестраивать в моем случае допустимо. Главное — транзакционно.
S>>>Транзакционно и раз в сутки?
O>> А что не так? Ну может раз в час... я пока не знаю сколько ресурсов на это будет уходить.
S>Транзакции нужны для того что бы данные в БД были согласованные.

Или в файлах в том случае, что я хотел.

S>Если синхронизировать кэш раз в час, то можно считать данные кэша будут согласованы мгновение в час.
S>Или речь о том что бы запретить модификацию данных на время перестроения кэша?

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

O>> Гуглил я такое уже ) И как создавать индексы я знаю.
O>> Вы мне лучше скажите что нужно сделать, чтобы пейджировать 500к записей в одной таблице быстрее чем за секунду на среднем железе.
S>Накидать adontz-у спасибов раза 4 как минимум.
S>Во всяком случае, полагаю что нужда в велосипедостроении отпала.

Уже накидал )
И да, действительно похоже отпала, хотя я еще детально не тестировал результаты запроса, но похоже, что все честно выбирается и сортируется как нужно.

Здравствуйте, Ocenochka, Вы писали:

O> У меня быстро работает без кластерных индексов.

С кластерным заработает ещё быстрее.

O> Я ничего не менял, просто запустил запрос на своей таблице, а на таблице только кластерный индекс по id (pk) и не кластерный по added.

Это значит что база сперва ищет в индексе что читать, а потом читает. Создав кластерный индекс вы увеличите производительность и сэкономите место.

O> Причем быстро работает (1 сек) при сортировке не только по added, а по любым имеющимся столбцам на которых вообще никаких индексов нет.
O> Прямо магия.

Там два ORDER BY. Второй сортирует окончательную выборку, а она небольшая.

Здравствуйте, adontz, Вы писали:

O>> Причем быстро работает (1 сек) при сортировке не только по added, а по любым имеющимся столбцам на которых вообще никаких индексов нет.
O>> Прямо магия.
A>Там два ORDER BY. Второй сортирует окончательную выборку, а она небольшая.

Я в обоих местах меняю сортировку по столбцу без индекса — все равно отрабатывает за 1 секунду.
Ладно. Большое спасибо за помощь. Теперь буду разбираться и если вопросы возникнут, то буду создавать другую тему с более адекватным названием и содержимым.

Здравствуйте, adontz, Вы писали:

A>Меньше секунды.

За сколько выполнится этот же запрос, если объём доступной серверу памяти ограничить 256 Мб, а диапазон запрашиваемых записей сместить в самый конец, например, 899000-900000?

Здравствуйте, Sergei MO, Вы писали:

SM>За сколько выполнится этот же запрос, если объём доступной серверу памяти ограничить 256 Мб, а диапазон запрашиваемых записей сместить в самый конец, например, 899000-900000?

Примерно за столько же.

Здравствуйте, adontz, Вы писали:

SM>>За сколько выполнится этот же запрос, если объём доступной серверу памяти ограничить 256 Мб, а диапазон запрашиваемых записей сместить в самый конец, например, 899000-900000?

A>Примерно за столько же.

Странно, у меня он за столько же выполнятся не хочет.
[SQL Express 2008 R2]

Здравствуйте, Sergei MO, Вы писали:

A>>Примерно за столько же.
SM>Странно, у меня он за столько же выполнятся не хочет.
SM>[SQL Express 2008 R2]

Да, наврал. Сказал не подумав. Действительно больше.

Я сделал индекс наоборот

CREATE CLUSTERED INDEX [Test/IX/AddedErased] ON [Test]([Added] ASC, [Erased] ASC);

вместо

CREATE CLUSTERED INDEX [Test/IX/ErasedAdded] ON [Test]([Erased] ASC, [Added] ASC);

и это заметно помогло: 12 секунд вместо 19.

И тут ничего не поделаешь, наверное, потому что в плане запроса 99% clustered index seek. Честно говоря, я очень удивлён, что SQL Server читает все строки до начала диапазона.

Здравствуйте, adontz, Вы писали:

A>Я сделал индекс наоборот
A>

CREATE CLUSTERED INDEX [Test/IX/AddedErased] ON [Test]([Added] ASC, [Erased] ASC);

A>вместо
A>

CREATE CLUSTERED INDEX [Test/IX/ErasedAdded] ON [Test]([Erased] ASC, [Added] ASC);

A>и это заметно помогло: 12 секунд вместо 19.
Есть мнение, что это произошло из-за изменения плана запроса и каких-то особенностей внутренней работы сервера. Я не вижу в новом индексе никаких преимуществ над старым.

A>Честно говоря, я очень удивлён, что SQL Server читает все строки до начала диапазона.
Он всегда так делал — в индексах нет такой информации, которая позволила бы обойтись без чтения всех предыдущих строк.

A>И тут ничего не поделаешь, наверное, потому что в плане запроса 99% clustered index seek.
Не, можно кое-что придумать.

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

Чтобы ускорить работу нужно уменьшить объём данных, которые должны быть прочитаны с диска. Индексы можно задать так:

ALTER TABLE [Test] ADD CONSTRAINT [Test/PK/ID] PRIMARY KEY CLUSTERED ([ID] ASC);
CREATE INDEX [Test/IX/ErasedAdded] ON [Test] ([Erased] ASC, [Added] ASC);

А запрос немного переписать:

SELECT [Test].[ID], [Test].[Text], [Test].[Added]
FROM
    (SELECT [ID], [Added], ROW_NUMBER() OVER (ORDER BY [Added] ASC) AS [Index]
        FROM [Test] WHERE [Erased] = 0) [OrderedTest]
LEFT JOIN
    [Test] ON [OrderedTest].[ID] = [Test].[ID]
WHERE
    [Index] BETWEEN 899000 AND 900000
ORDER BY
    [Index]

В этом случае размер индекса [Test/IX/ErasedAdded] будет всего около 16 Мб. А новый запрос позволяет серверу выбрать ID нужных записей, используя только этот индекс (со старым запросом оптимизатор Express'a не справился). Когда ID записей найдены, в основную таблицу делается очень небольшое количество обращений — столько, сколько возвращается записей. В худшем случае это по одной странице размером 8К для каждой из тысячи записей. Т.е. для выполнения всего запроса потребуется прочитать с диска не более 24 Мб.

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

Здравствуйте, Sergei MO, Вы писали: