вопрос к тем, кто показывает много данных в таблицах
есть у меня запрос
SELECT Id, ....
FROM ... WHERE .... ORDER BY ...
задача — узнать каким номером будет идти строка с Id=N
по специфике приложения, должна использоваться встроенная БД, я смотрел SQLite, FireBird, MS SQL CE — нигде этого сделать нельзя кроме как полным "выкачиванием" результатов и ручным поиском.
в полноценной версии MS SQL SERVER 2005 вроде есть ROW_NUMBER(), при помощи которой это делается.
а как быть с более простыми базами, есть идеи?
зачем все это надо: чтобы в гриде с сортировкой и фильтрацией восстановить выделенную строчку после смены сортировки. так как записей может быть много, то чтение идет кусками и хотелось бы сразу прочитать нужный кусок.
Здравствуйте, ov, Вы писали:
ov>а как быть с более простыми базами, есть идеи?
Есть идея, как можно в firebird попытаться проэмулировать rownum. Можно попытаться использовать для этого последовательности, приблизительно так
create sequence temp_seq;
select gen_id(temp_seq,1) rownum from ....
Тут правда возникают другие проблемы, которые нужно ещё подумать как решить
1. недопущение использования одной последовательности в паралельных запросах
2. превышение максимального значения в последовательности (я не уверен, как оно устроено в firebird. вероятно, начнётся опять с нуля(?))
Здравствуйте, KRA, Вы писали:
ov>>а как быть с более простыми базами, есть идеи?
KRA>Есть идея, как можно в firebird попытаться проэмулировать rownum. Можно попытаться использовать для этого последовательности, приблизительно так
Поправки для FB.
KRA>create sequence temp_seq;
CREATE GENERATOR TEMP_GEN;
SET GENERATOR TEMP_GEN TO 0;
COMMIT;
KRA>select gen_id(temp_seq,1) rownum from ....
Угу.
KRA>Тут правда возникают другие проблемы, которые нужно ещё подумать как решить KRA>1. недопущение использования одной последовательности в паралельных запросах
Генераторы в FB работают вне контекста транзакций, поэтому при параллельной работе придется делать по генератору на каждый запрос. Потом — удалять. Т.к. пользователь будет владельцем обьекта — все должно пройти нормально (не будет накладок с правами).
KRA>2. превышение максимального значения в последовательности (я не уверен, как оно устроено в firebird. вероятно, начнётся опять с нуля(?))
В FB 2.x генераторы — INT64. Должно хватить по уши. Насчет установки начального значения — смотри выше.
P.S. Более универсальное решение для FB — ххранимая процедура:
I=0;
for SELECT ... FROM ... WHERE ...
INTO ....
do BEGIN
I=I+1;
SUSPEND;
END
Здравствуйте, DarkMaster, Вы писали:
DM>P.S. Более универсальное решение для FB — ххранимая процедура:
Забыл еще про EXECUTE BLOCK;
execute block
returns (AROWNUM INT64, AVALUE varchar(10))
as
begin
AROWNUM=0;
for select VALUE
from MYTABLE
where VALUE LIKE 'A%'
order by VALUEDATE
into :AVALUE
do begin
AROWNUM=AROWNUM+1;
suspend;
end
end
> зачем все это надо: чтобы в гриде с сортировкой и фильтрацией > восстановить выделенную строчку после смены сортировки. так как записей > может быть много, то чтение идет кусками и хотелось бы сразу прочитать > нужный кусок.
Какая разница, какой по порядку идёт нужная вам строка ?
У каждой строки есть первичный ключ (должен быть, иначе
нужно делать редизайн БД), вы до смены сортировки запоминаете значение
ключа, делаете сортировку, и находите нужную запись по запомненному
значению ключа.
>вы до смены сортировки запоминаете значение ключа, делаете сортировку, и находите нужную запись по запомненному >значению ключа.
все бы хорошо, да надо читать блоками. для чтения целиком проблем нет — все так и делается и прекрасно работает.
>> может быть много, то чтение идет кусками и хотелось бы сразу прочитать нужный кусок. MZ>А это сделать не получится.
прочитать кусок не проблема. SQLite и FireBird позволяют в SELECT'е указать "окно". MS SQL CE, правда, не позволяет, но его старший брат может.
по моим тестам чтение 20К записей (на SQLite) не тормозит и можно читать целиком, но если записей будет 100К?
я отдаю себе отчет, что негоже юзеру показывать 100К записей, но в этом суть программы: длинный список с сортировкой и фильтрацией.
ov пишет:
> прочитать кусок не проблема. SQLite и FireBird позволяют в SELECT'е > указать "окно". MS SQL CE, правда, не позволяет, но его старший брат может. > по моим тестам чтение 20К записей (на SQLite) не тормозит и можно читать > целиком, но если записей будет 100К?
Какими такими блоками ?
Если речь о всяких там LIMIT-ах или TOP-ах,
то их не имеет смысла применять, если вам нужны не
первые N записей.
MZ>Если речь о всяких там LIMIT-ах или TOP-ах,
да, о них. MZ>то их не имеет смысла применять, если вам нужны не первые N записей.
почему? в SQLite есть конструкция LIMIT 100 OFFSET 1000, которая вернет 100 строк с 1000-й позиции. в FireBird есть FIRST/SKIP, кажется.
и как тогда "правильно" считать результат запроса в 100К строк? за раз чтоли??
ov пишет:
> MZ>Если речь о всяких там LIMIT-ах или TOP-ах, > да, о них. > MZ>то их не имеет смысла применять, если вам нужны не первые N записей. > почему? в SQLite есть конструкция LIMIT 100 OFFSET 1000, которая вернет > 100 строк с 1000-й позиции. в FireBird есть FIRST/SKIP, кажется.
Потому что LIMIT-ы или TOP-ы в общем случая обрабатывают
сначала ВЕСЬ набор данных, а потом возвращают только его часть.
Экономия -- только на операции FETCH на клиента.
При этом экономии можно достичь только :
0) выбирая только N записей от начала набора данных,
при этом оптимизатор может понять, что от запроса нужно только
первые N строк, и построит план так, чтобы только они обрабатывались,
и то при этом нужно, чтобы операция ORDER BY оптимизировалась за счёт индекса,
а не производилась реальная сортировка, иначе опять-таки будет обрабатываться
весь потенциальный набор данных
1) выбирая N записей начиная с M-ой СУБД придётся обработать подобным же
образом M+N записей, на M записей получится экономия только на FETCH-е на
клиента, и на ещё (размер таблицы — (M+N)) -- может быть экономия, как
в случае (0)
> и как тогда "правильно" считать результат запроса в 100К строк? за раз > чтоли??
Нужно не питать иллюзий, что есть какой-то магический способ выборки части
набора данных из всего набора. Набор нужно обрабатывать целиком.
Единственный реальный способ оптимизации выборки -- это отбросить
невыбранную часть данных, он работает (почти) всегда и везде.
Но даже в этом случае СУБД может обрабатывать весь большой невыбираемый
набор данных.
Да, вы тут говорили о SQL Lite, возможно, есть какие-то специфичные
для него аспекты этой проблемы, я его к сож. не знаю.
MZ>Нужно не питать иллюзий, что есть какой-то магический способ выборки части MZ>набора данных из всего набора. Набор нужно обрабатывать целиком.
да не в иллюзиях дело. просто хочется решить вопрос на уровне БД, оптимальным для нее способом. чтобы я мог ей сказать "хочу такие-то записи, с такой-то позиции", а она уже сама думала как их мне наиболее оптимально выдать.
иначе, если так рассуждать, можно дойти и до того, что сортировать результаты работы надо самому и не питать иллюзий, что БД с этим оптимально справится.
MZ>Да, вы тут говорили о SQL Lite, возможно, есть какие-то специфичные MZ>для него аспекты этой проблемы, я его к сож. не знаю.
да нет, там своих тараканов хватает. он окно делает как раз пропуская лишние строчки, в результате окно, близкое к началу выборки, летает, а если начинать с полумиллионной строчки — тормозит. MS SQL Server с хитрой конструкцией из ROW_NUMBER'ов летает и на таких количествах. файрберд тупил и на гораздо меньших цифрах, но там, возможно, я с индеками перемудрил.
MS SQL Server с > хитрой конструкцией из ROW_NUMBER'ов летает и на таких количествах. > файрберд тупил и на гораздо меньших цифрах, но там, возможно, я с > индеками перемудрил.
Это что за конструкция такая ?
Вообще, как вы узнаете ROW_NUMBER() строки априори, не выполняя запроса ?
MZ>Это что за конструкция такая ?
что-то типа этого. сервера под рукой нет проверить точно:
SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ...) AS ROW FROM ...)
WHERE ROW>=... AND ROW<...
MZ>Вообще, как вы узнаете ROW_NUMBER() строки априори, не выполняя запроса ?
запрос, конечно, выполняется. но внутри БД и я не задумываюсь как его наиболее оптимально выполнить с ее точки зрения.
Здравствуйте, ov, Вы писали:
ov>что-то типа этого. сервера под рукой нет проверить точно: ov>
ov>SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY ...) AS ROW FROM ...)
ov>WHERE ROW>=... AND ROW<...
ov>
это аналитика
если был insert/update/delete между двумя выполнениями этого запроса, то номер строки — это средняя температура по больнице
Вам ясно сказали: по ID
про генераторы/сиквенсы для нумерации результатов упоминать не будем, т.к. за такое в любой софтостроительной конторе рвут руки, и это справедливо
N>Вам ясно сказали: по ID
про ID речь шла в контексте поиска выделенной строки после применения новой сортировки.
а пример запроса с ROW_NUMBER этого всего лишь эмуляция окна в MS SQL сервере. возможно кривая, я не вникал подробно, т.к. Compact Edition ее не поддерживает.
поиск выделенной строки по ID после сортировки у меня прекрасно работает пока я считываю результат запроса полностью.
а я хочу читать блоками, чтобы не фетчить все 100К/500К записей разом.
а после смены сортировки неплохо бы еще и блок правильный найти с первого раза.
и тут, похоже, все очень запущено...
N>про генераторы/сиквенсы для нумерации результатов упоминать не будем, т.к. за такое в любой софтостроительной конторе рвут руки, и это справедливо
согласен.
ov пишет:
> поиск выделенной строки по ID после сортировки у меня прекрасно работает > пока я считываю результат запроса полностью. > а я хочу читать блоками, чтобы не фетчить все 100К/500К записей разом. > а после смены сортировки неплохо бы еще и блок правильный найти с > первого раза. > и тут, похоже, все очень запущено...
Ещё раз -- вы хотите невозможного. Почти невозможного.
Фетчить наборы данных можно только целиком. Вообще, если вы выбрали
набор, потом тот же запросы выполнили, и выбрали набор ещё раз --
это уже другой набор, к первому не имеющий никакого отношения.
Фетчить большие наборы тоже плохо -- они никому не нужны, даже отсортированные.
Вам следует не заниматься поисками эфимерности, а улучшить
критерии выборки, чтобы сократить кол-во выбераемых с сервера
данных.
Здравствуйте, MasterZiv, Вы писали:
MZ>Фетчить наборы данных можно только целиком. Вообще, если вы выбрали MZ>набор, потом тот же запросы выполнили, и выбрали набор ещё раз -- MZ>это уже другой набор, к первому не имеющий никакого отношения.
Может я чего не понимаю, но чем плох вариант: сначала определяем на какую страницу попадает выбраная пользователем строка (по сути то с чего началась тема — нужно определить порядковый номер записи с даным идентификатором). Запрос получается вида (пишу с ораклиным синтаксисом)
select rn from (
select e.*, rownum rn
from table e
order by
e.field1, e.field2
)
where id = :id
при наличии индексов по полям сортировки даже при 100к может работать с приемлимой скоростью на соответствующем железе (по крайней мере оракл с rownum-ами неплохо работает).
После этого, зная номер записи (и расчитав на какой странице она находится), мы можем подчитать только нужную нам страницу запросом вида
select e.* from (
select e.*, rownum rn
from table e
order by
e.field1, e.field2
)
where rn between :start_index and :end_index
Опять же в оракле такой запрос не приводит к тому, что читаются все данные целиком. Оракл понимает, что такое rownum и как тут можно оптимизировать.
Естественно оба запроса в одной транзакции.
MZ>Фетчить большие наборы тоже плохо -- они никому не нужны, даже отсортированные. MZ>Вам следует не заниматься поисками эфимерности, а улучшить MZ>критерии выборки, чтобы сократить кол-во выбераемых с сервера MZ>данных.
В теории согласен. Но вот на практике как это реализовать непонятно. Делать предварительную выборку количества без учёта сортировок и если в результате получается много — говорить пользователю, чтоб задал более жёсткие ограничения? Обычно пользователи хотят задавать любые критерии и иметь пейджинг.
KRA>Может я чего не понимаю, но чем плох вариант:
вариант хорош. плохо то, что нет "настольной" БД, способной это сделать не подохнув...
KRA>Обычно пользователи хотят задавать любые критерии и иметь пейджинг.
угу. об этом и речь.
KRA пишет:
> Может я чего не понимаю, но чем плох вариант: сначала определяем на > какую страницу попадает выбраная пользователем строка (по сути то с чего > началась тема — нужно определить порядковый номер записи с даным > идентификатором). Запрос получается вида (пишу с ораклиным синтаксисом) > > > select rn from ( > select e.*, rownum rn > from table e > order by > e.field1, e.field2 > ) > where id = :id > > > > при наличии индексов по полям сортировки даже при 100к может работать с > приемлимой скоростью на соответствующем железе (по крайней мере оракл с > rownum-ами неплохо работает).
Вот этот запрос:
select e.*, rownum rn > from table e > order by > e.field1, e.field2 > )
который, между прочим, у вас всю таблицу обрабатывает,
должен сначала весь выбраться, отсортироваться,
и потом выбраться из него уже с id = :id.
Что тут может быть быстрого ?
Где тут поводы для оптимизации ? (кроме оптимизации ORDER BY
по индексу)
Кстати, по идее, order by в подзапросе СУБД может запросто
выкинуть, оно ни на что не влияет. Псевдоколонка rownum
же имеет смысл только в строке _возвращаемого_ набора данных,
она по идее там и должна генерироваться. Я не говорю, что
оракл так будет делать, просто логически тут всё очешь шатко.
> После этого, зная номер записи (и расчитав на какой странице она > находится),
Как ?
мы можем подчитать только нужную нам страницу запросом вида > > select e.* from ( > select e.*, rownum rn > from table e > order by > e.field1, e.field2 > ) > where rn between :start_index and :end_index > > > > Опять же в оракле такой запрос не приводит к тому, что читаются все > данные целиком.
Ээээ.. доказательства будут ?
Оракл понимает, что такое rownum и как тут можно > оптимизировать.
Как можно что-то оптимизировать по rownum, если он генерируется
в момент создания набора данных ?
Объясните, я не очень понимаю.
> > Естественно оба запроса в одной транзакции.
> В теории согласен. Но вот на практике как это реализовать непонятно. > Делать предварительную выборку количества без учёта сортировок и если в > результате получается много — говорить пользователю, чтоб задал более > жёсткие ограничения?
Да, например. Можно по любому запросу выводить, скажем, только первые 100
записей, и если есть ещё, писать, что часть данных были отброшены.
Больше 100-1000 записей никто читать не будет. Если это не выгрузка
данных куда-то для экспорта, или не отчёт какой-то для складирования, бесполезно
выводить более 100-1000 записей. Конкретные 100-1000 конечно варьируются в
зависимости от специфики приложения, но в общем идея такая.
Обычно пользователи хотят задавать любые критерии и > иметь пейджинг.
Нахрена пейджинг нужен, если ты получаешь 10-100 нужных тебе записей ?
KRA>select rn from (
KRA>select e.*, rownum rn
KRA>from table e
KRA>order by
KRA> e.field1, e.field2
KRA>)
KRA>where id = :id
KRA>
если так Вы хотите получить порядковый номер в сортировке e.field1, e.field2, то ошибаетесь
KRA>Опять же в оракле такой запрос не приводит к тому, что читаются все данные целиком. Оракл понимает, что такое rownum и как тут можно оптимизировать.
никакой магии
данные читаются целиком
