Есть сайт на хостинге.
Есть "взрослая" БД с ограниченным местом, которое уже подходит к концу.
В этой БД есть одна таблица:
{
id [int]
text [nvarchar(400)]
}
Записей в этой таблице 500.000 шт.
На сайте нужно отображает все 500к сущностей постранично. Количество страниц не ограничено никаким значение — пролистать можно все 500к.
Работает это сильно медленнее, чем хотелось бы, поскольку при выборе страницы приходится скан всей таблицы 500к.
(относительно нетехнических решений этой проблемы смотри "pps" в конце поста)
Есть место на диске, которое можно использовать (достаточное количество).
Я хотел бы использовать это место для кэширования этой большой коллекции, чтобы пейджирование работало быстрее.
Для этого я решил выгружать всю таблицу из БД и сохранять ее на диск разбивая на небольшие файлы, а при запросе N-ой страницы читать тот файл, в котором хранится N-ая страница.
Вроде вполне нормальное решение, но встала проблема транзакционной работы с файлами — создавать lock-файлы не вариант, делать примитивы синхронизации в памяти — то же (поток может умереть в процессе выполнения или сервер может быть перезапущен при превышении оперативного кэша и повиснут файлы в невалидном состоянии).
Вопрос: можно ли использовать для этой задачи SQL Server Compact и как? Если я просто запихну всю коллекцию 500к в таблицу sql compact'а, то возникнет та же проблема производительности — полный скан таблицы. Создавать несколько таблиц и разбивать всю коллекцию 500к по таблицам, в каждой из которых по 10-50к записей как-то криво, хотя в итоге это даст увеличение скорости работы.
И поскольку с Sql compact никогда не работал — не знаю как там с многопоточным доступом и транзакциями.
ps Я уже задавал похожий вопрос на этом форуме и найденным тогда решением было кэшировать всю таблицу в памяти, но теперь экспериментальным путем я выявил, что при превышении 100 МБ оперативной памяти хостер перезапускает все приложение стирая кэш, поэтому кэш живет ровно один запрос.
pps Проблема может показаться надуманной, типа чего там купил памяти, построил индекс и все, но:
1. проект пока не запущен и убыточен и еще больше денег, кроме хостинга не охота на него тратить.
2. задача интересна сама по себе, т.к. логично кэшировать редко меняющиеся данные на винте, а не запрашивать все время из БД.
Здравствуйте, Ocenochka, Вы писали:
O>pps Проблема может показаться надуманной, типа чего там купил памяти, построил индекс и все, но: O>1. проект пока не запущен и убыточен и еще больше денег, кроме хостинга не охота на него тратить.
А тратить время на изобретение мопеда охото?
Сколько денег будет стоить место под индекс БД и сколько под файловый кэш данных?
O>2. задача интересна сама по себе, т.к. логично кэшировать редко меняющиеся данные на винте, а не запрашивать все время из БД.
А БД хранит данные на перфокартах что ли?
Если винт загружен, то он сосать будет что под БД, что под набором файлов, только под набором файлов наверняка сильнее.
Здравствуйте, samius, Вы писали:
O>>pps Проблема может показаться надуманной, типа чего там купил памяти, построил индекс и все, но: O>>1. проект пока не запущен и убыточен и еще больше денег, кроме хостинга не охота на него тратить. S>А тратить время на изобретение мопеда охото? S>Сколько денег будет стоить место под индекс БД и сколько под файловый кэш данных?
Дело не только в деньгах. Место на диске более дешевый ресурс уметь его использовать стратегически важно.
O>>2. задача интересна сама по себе, т.к. логично кэшировать редко меняющиеся данные на винте, а не запрашивать все время из БД. S>А БД хранит данные на перфокартах что ли? S>Если винт загружен, то он сосать будет что под БД, что под набором файлов, только под набором файлов наверняка сильнее.
В основной БД хотя бы таблица лочиться не будет, (в нее еще регулярно идет запись).
Загруженность БД != загруженность винта.
Люблю ставить оценки.
Re[2]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, adontz, Вы писали:
O>> Есть сайт на хостинге. O>> Есть "взрослая" БД с ограниченным местом, которое уже подходит к концу.
A>Взрослая это какая?
Полноценная SQL Server 2008
A>Почему для постраничного вывода взрослой БД нужно сканировать всю таблицу?
Ну чтобы выбрать даже первую страницу нужно быть увереным, что последняя запись таблицы по условию не попадет на первую страницу вот и приходится проходить всю таблицу.
Я это так понимаю.
A>Кластерный индекс, FK+JOIN пробовали?
Нет, можно ссылку или словосочетание как гуглить?
Только есть один момент, который сразу уточню — страницы выбираются из большой таблицы не тупо как есть, а по не сложному условию и еще сортируются (в начальном посте я опустил "лишние" столбцы).
Люблю ставить оценки.
Re[3]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, Ocenochka, Вы писали:
O>Здравствуйте, samius, Вы писали:
S>>Сколько денег будет стоить место под индекс БД и сколько под файловый кэш данных? O> Дело не только в деньгах. Место на диске более дешевый ресурс уметь его использовать стратегически важно.
Стратегически важно уметь использовать то, что дорого. Например, свое время.
Кэшировать данные на диске можно, но будучи уверенным, что ты это делаешь эффективнее чем БД. Как это делает БД, ты узнать не стремишься. Результат будет печален.
O>>>2. задача интересна сама по себе, т.к. логично кэшировать редко меняющиеся данные на винте, а не запрашивать все время из БД. S>>А БД хранит данные на перфокартах что ли? S>>Если винт загружен, то он сосать будет что под БД, что под набором файлов, только под набором файлов наверняка сильнее. O> В основной БД хотя бы таблица лочиться не будет, (в нее еще регулярно идет запись).
Значит встает вопрос синхронизации кэша с таблицей.
O> Загруженность БД != загруженность винта.
Я не утверждал что они равны. Сделай доброе дело, создай индекс и разгрузи винт.
Re[3]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, samius, Вы писали:
O>>Здравствуйте, samius, Вы писали:
S>>>Сколько денег будет стоить место под индекс БД и сколько под файловый кэш данных? O>> Дело не только в деньгах. Место на диске более дешевый ресурс уметь его использовать стратегически важно. S>Стратегически важно уметь использовать то, что дорого. Например, свое время.
Я свое время выделяю на обучение новым интересующим меня вещам.
Кэширование на жестком диске и работа с inproc-БД как раз такие вещи.
S>Кэшировать данные на диске можно, но будучи уверенным, что ты это делаешь эффективнее чем БД. Как это делает БД, ты узнать не стремишься. Результат будет печален.
Я думаю, что чтение маленького файла быстрее чем запрос в другой процесс, в котором нагромождено куча всего + все равно идет работа с файлом.
Я пробовал (без транзакционной работы) вводить файлы и скорость работы с ними была в несколько раз выше (всегда — мгновенная) даже по сравнению с простыми запросами к БД.
O>>>>2. задача интересна сама по себе, т.к. логично кэшировать редко меняющиеся данные на винте, а не запрашивать все время из БД. S>>>А БД хранит данные на перфокартах что ли? S>>>Если винт загружен, то он сосать будет что под БД, что под набором файлов, только под набором файлов наверняка сильнее. O>> В основной БД хотя бы таблица лочиться не будет, (в нее еще регулярно идет запись). S>Значит встает вопрос синхронизации кэша с таблицей.
Конечно встает, но в нем я сложностей не вижу — раз в сутки перестраивать в моем случае допустимо. Главное — транзакционно.
O>> Загруженность БД != загруженность винта. S>Я не утверждал что они равны. Сделай доброе дело, создай индекс и разгрузи винт.
В общем-то Вы меня почти убедили. Только я не знаю как построить индексы, чтобы они дали сравнимую с файлами скорость. Опишу более подробно:
есть таблица: (750к записей (на самом деле))
id [int] pk
text [nvarchar(400)]
erased [bit]
added [datetime]
в конечном итоге, мне нужно выполнить такой запрос:
select * from table
where erased == 0
order by added
Даже такой запрос
select * from table
where erased == 0
выполняется на моей (более мощной машине, чем на хостинге) 6 секунд.
По сравнению с файлами, которые читаются мгновенно — БД, как Вы выражаетесь, сосет.
Подскажите как настроить БД чтобы это происходило меньше чем за секунду?
Люблю ставить оценки.
Re[5]: [SQL Server Compact] Можно ли использовать?
Инициализация БД. Имеем миллион уникальных записей.
--------------------------------------------------------------------------------
DROP TABLE [Test];
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
CREATE TABLE [Test]
(
[ID] INT NOT NULL IDENTITY,
[Text] NVARCHAR(400) NOT NULL,
[Erased] BIT NOT NULL,
[Added] DATETIME NOT NULL
);
--------------------------------------------------------------------------------
GO
--------------------------------------------------------------------------------
ALTER TABLE [Test] ADD CONSTRAINT [Test/PK/ID] PRIMARY KEY NONCLUSTERED ([ID] ASC);
CREATE CLUSTERED INDEX [Test/IX/ErasedAdded] ON [Test]([Erased] ASC, [Added] ASC);
--------------------------------------------------------------------------------
DECLARE @Index INT
DECLARE @String NVARCHAR(400);
SET @Index = 0;
WHILE (@Index < 1000000)
BEGIN
SET @String =
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100)) +
CAST(NEWID() AS NVARCHAR(100));
INSERT INTO
[Test]([Text], [Erased], [Added])
VALUES (@String, CASE WHEN RAND() > 0.1 THEN 0 ELSE 1 END, GETDATE())
SET @Index = @Index + 1;
END
У меня получилось 900077 неудалённых записей и 99923 удалённых.
Запрос (обычный винчестер, никакого рейда, сервер ограничен 2Гб памяти)
16 секунд (100%) - SELECT * FROM [Test]
12 секунд ( 75%) - SELECT * FROM [Test] WHERE [Erased] = 0 ORDER BY [Added]
Запрос (обычный винчестер, никакого рейда, сервер ограничен 256Мб памяти)
38 секунд (100%) - SELECT * FROM [Test]
20 секунд ( 53%) - SELECT * FROM [Test] WHERE [Erased] = 0 ORDER BY [Added]
То есть задача эффективной фильтрации успешно решена.
Вот если у вашего провайдера диски веб-сервера быстрее дисков SQL сервера, тогда я не знаю что делать кроме как сменить провайдера.
Здравствуйте, Ocenochka, Вы писали:
O>Здравствуйте, samius, Вы писали:
S>>Стратегически важно уметь использовать то, что дорого. Например, свое время.
O> Я свое время выделяю на обучение новым интересующим меня вещам. O> Кэширование на жестком диске и работа с inproc-БД как раз такие вещи.
Тогда начать нужно с изучения механизмов работы БД.
O> Я думаю, что чтение маленького файла быстрее чем запрос в другой процесс, в котором нагромождено куча всего + все равно идет работа с файлом.
Это ошибочное мнение в общем случае. O> Я пробовал (без транзакционной работы) вводить файлы и скорость работы с ними была в несколько раз выше (всегда — мгновенная) даже по сравнению с простыми запросами к БД.
А как насчет непростых запросов к файлам?
S>>Значит встает вопрос синхронизации кэша с таблицей.
O> Конечно встает, но в нем я сложностей не вижу — раз в сутки перестраивать в моем случае допустимо. Главное — транзакционно.
Транзакционно и раз в сутки?
O> В общем-то Вы меня почти убедили. Только я не знаю как построить индексы, чтобы они дали сравнимую с файлами скорость. Опишу более подробно: O> есть таблица: (750к записей (на самом деле)) O> в конечном итоге, мне нужно выполнить такой запрос: O>
O> select * from table
O> where erased == 0
O> order by added
O>
O>Даже такой запрос O>
O> select * from table
O> where erased == 0
O>
O>выполняется на моей (более мощной машине, чем на хостинге) 6 секунд. O>По сравнению с файлами, которые читаются мгновенно — БД, как Вы выражаетесь, сосет.
O>Подскажите как настроить БД чтобы это происходило меньше чем за секунду?
Думаю что этого должно быть достаточно.
+ paging значительно упростит время выполнения.
Re[5]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, adontz, Вы писали:
A>Инициализация БД. Имеем миллион уникальных записей.
[SKIP] A>У меня получилось 900077 неудалённых записей и 99923 удалённых.
A>Запрос (обычный винчестер, никакого рейда, сервер ограничен 2Гб памяти) A>
A>16 секунд (100%) - SELECT * FROM [Test]
A>12 секунд ( 75%) - SELECT * FROM [Test] WHERE [Erased] = 0 ORDER BY [Added]
A>
A>Запрос (обычный винчестер, никакого рейда, сервер ограничен 256Мб памяти) A>
A>38 секунд (100%) - SELECT * FROM [Test]
A>20 секунд ( 53%) - SELECT * FROM [Test] WHERE [Erased] = 0 ORDER BY [Added]
A>
A>То есть задача эффективной фильтрации успешно решена.
Задача фильтрации может и решена, а вот задача быстрого рендеринга html-страницы, выводящей часть данных такого запроса — не решена.
А если все эти данные выбирать только один раз в сутки и разбивать по файлам, то страницы будут рендериться мгновенно.
A>Вот если у вашего провайдера диски веб-сервера быстрее дисков SQL сервера, тогда я не знаю что делать кроме как сменить провайдера.
У хостера диски веб-сервера скорее всего хуже, чем диски для SQL сервера, только это дела не меняет — 12 (на моей машине ~ 6) секунд для пользователей сайта будет слишком долго.
Люблю ставить оценки.
Re[6]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, samius, Вы писали:
S>>>Стратегически важно уметь использовать то, что дорого. Например, свое время. O>> Я свое время выделяю на обучение новым интересующим меня вещам. O>> Кэширование на жестком диске и работа с inproc-БД как раз такие вещи. S>Тогда начать нужно с изучения механизмов работы БД.
Спасибо за рекомендацию, приму к сведению.
O>> Я думаю, что чтение маленького файла быстрее чем запрос в другой процесс, в котором нагромождено куча всего + все равно идет работа с файлом. S>Это ошибочное мнение в общем случае.
Не плохо было бы аргументировать.
O>> Я пробовал (без транзакционной работы) вводить файлы и скорость работы с ними была в несколько раз выше (всегда — мгновенная) даже по сравнению с простыми запросами к БД. S>А как насчет непростых запросов к файлам?
Непростые запросы к файлам мне не нужны — на то они и файлы.
S>>>Значит встает вопрос синхронизации кэша с таблицей. O>> Конечно встает, но в нем я сложностей не вижу — раз в сутки перестраивать в моем случае допустимо. Главное — транзакционно. S>Транзакционно и раз в сутки?
А что не так? Ну может раз в час... я пока не знаю сколько ресурсов на это будет уходить.
O>> В общем-то Вы меня почти убедили. Только я не знаю как построить индексы, чтобы они дали сравнимую с файлами скорость. Опишу более подробно: O>> есть таблица: (750к записей (на самом деле)) O>> в конечном итоге, мне нужно выполнить такой запрос: O>>
O>> select * from table
O>> where erased == 0
O>> order by added
O>>
O>>Даже такой запрос O>>
O>> select * from table
O>> where erased == 0
O>>
O>>выполняется на моей (более мощной машине, чем на хостинге) 6 секунд. O>>По сравнению с файлами, которые читаются мгновенно — БД, как Вы выражаетесь, сосет.
O>>Подскажите как настроить БД чтобы это происходило меньше чем за секунду? S>Думаю что этого должно быть достаточно. S>+ paging значительно упростит время выполнения.
Гуглил я такое уже ) И как создавать индексы я знаю.
Вы мне лучше скажите что нужно сделать, чтобы пейджировать 500к записей в одной таблице быстрее чем за секунду на среднем железе.
Люблю ставить оценки.
Re[6]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, Аноним, Вы писали:
O>>Подскажите как настроить БД чтобы это происходило меньше чем за секунду? А>Berkeley DB
Спасибо за наводку, но меня интересуетвопрос, ответ на который я не смог нагуглить сходу:
Допустим у меня список ключ/поле в такой БД. Записи делаются в архаичном порядке.
Нужно получить десятую страницу по двадцать записей в каждой, отсортированных по полю.
Придётся ли получать все записи, сортировать, а потом отсчитывать с 200-ой по 220-ую?
Люблю ставить оценки.
Re[7]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, Ocenochka, Вы писали:
A>>То есть задача эффективной фильтрации успешно решена. O> Задача фильтрации может и решена, а вот задача быстрого рендеринга html-страницы, выводящей часть данных такого запроса — не решена.
WITH
[OrderedTest] AS
(
SELECT
[ID],
[Text],
[Added],
ROW_NUMBER() OVER (ORDER BY [Added] ASC) AS [Index]
FROM
[Test]
WHERE
([Erased] = 0)
)
SELECT
[ID],
[Text],
[Added]
FROM
[OrderedTest]
WHERE
([Index] BETWEEN 10000 AND 11000)
ORDER BY
[Added]
Здравствуйте, Ocenochka, Вы писали:
O> Допустим у меня список ключ/поле в такой БД. Записи делаются в архаичном порядке.
Хронологический, а не архиачный.
O> Нужно получить десятую страницу по двадцать записей в каждой, отсортированных по полю. O> Придётся ли получать все записи, сортировать, а потом отсчитывать с 200-ой по 220-ую?
Здравствуйте, adontz, Вы писали:
A>Здравствуйте, Ocenochka, Вы писали:
A>Хочу скрипт запроса без "редактирования для форума" и скрипт создания всех упоминаемых в запросе таблиц.
Люблю ставить оценки.
Re[4]: [SQL Server Compact] Можно ли использовать?
Здравствуйте, adontz, Вы писали:
A>Здравствуйте, Ocenochka, Вы писали: A>Хочу скрипт запроса без "редактирования для форума" и скрипт создания всех упоминаемых в запросе таблиц.
Прошу прощения, случайно отправил пустое сообщение.
Скрипт приложу после того, как попробую Ваше решение из соседней ветки этой темы.
А то может и скрипт не нужно будет прикладывать, если запрос начнет быстро работать...
Люблю ставить оценки.
Re[8]: [SQL Server Compact] Можно ли использовать?
A>>>То есть задача эффективной фильтрации успешно решена. O>> Задача фильтрации может и решена, а вот задача быстрого рендеринга html-страницы, выводящей часть данных такого запроса — не решена.
[SKIP] A>Меньше секунды.
Большое спасибо!
Открыли глаза — я был уверен, что это невозможно.
Подскажите — чтобы понять как это работает нужно про CTE читать?
Т.е. почему так быстро получается, ведь обходить всю таблицу и сортировать ее все равно нужно, а это не может быть быстрее 'тупой' выборки данных?
