Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Эффективная выборка записей по списку ID  [new]
NickDee
Member

Откуда:
Сообщений: 1395
Хочется по списку ID-ов (~10 тыс. штук) получить записи из таблицы (на ~10 миллионов записей).
Какая субд с этим справится эффективно?
А какая справится с этой задачей эффективно одним SQL-запросом?
20 окт 08, 15:14    [6330075]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
miksoft
Member

Откуда:
Сообщений: 38921
А каков размер блока и средняя длина записей?

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

Намного больше влияют запрошенные мной величины и настройки конкретного инстанса.
20 окт 08, 16:25    [6330660]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
NickDee
Member

Откуда:
Сообщений: 1395
miksoft
А каков размер блока и средняя длина записей?

не думаю что это важно, ведь перебор 10 миллионов записей делать никто не будет.

табличка SomeTable произвольной структуры, Primary Key по полю Id (типа "Целое").

так будет выглядеть запрос?
select * from SomeTable where Id in (тут 10 тыщ значений через запятую)
А такой запрос случайно не вылезет за какие-нибудь ограничения сервера (например на кол-во элементов в IN, или на длину sql-запроса)?
Вот в Firebird вылазеет ограничение на IN, хотя для него есть другой, более эффективный выход.
Как с такими ограничениями в Orace и MSSQL?
20 окт 08, 16:41    [6330815]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
так будет выглядеть запрос?
select * from SomeTable where Id in (тут 10 тыщ значений через запятую)


Он может выглядеть и не так. Важно знать откуда буреться эти 10 000 IDшников.
20 окт 08, 16:49    [6330879]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
Как с такими ограничениями в ... MSSQL?


Да никаких, собственно:


SELECT
  SUM(rows) AS NumRows
FROM
  sys.partitions
WHERE
  object_id = OBJECT_ID('dbo.Object') AND
  index_id IN (1, 2)

125 547 415


DECLARE
  @sql nvarchar(max)

SET @sql = 'SELECT StateID FROM dbo.Object WHERE ID IN('

SELECT TOP 10000
  @sql = @sql + CAST(ID AS varchar) + ',' FROM dbo.Object

SET @sql = STUFF(@sql, LEN(@sql), 1, ')')  

exec sp_executesql @sql


(10000 row(s) affected)

Таблица "Object". Число просмотров 0, логических чтений 40634, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время выполнения SQL Server:
Время ЦП = 31 мс, истекшее время = 39 мс.

Время синтаксического анализа и компиляции SQL Server:
время ЦП = 0 мс, истекшее время = 1 мс.
20 окт 08, 17:08    [6331021]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
NickDee
Member

Откуда:
Сообщений: 1395
pkarklin
автор
так будет выглядеть запрос?
select * from SomeTable where Id in (тут 10 тыщ значений через запятую)


Он может выглядеть и не так. Важно знать откуда буреться эти 10 000 IDшников.

10000 Id-шников есть в памяти приложения, нужно по ним выбрать данные из таблицы.
Я помню долго парился с этим вопросом в Firebird, и вот захотел узнать - как обстоят с этим дела в других движках БД.
20 окт 08, 17:26    [6331152]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
pkarklin
index_id IN (1, 2)


читать как:

index_id IN (0, 1)

ЗЫ. Результат запроса верный.
20 окт 08, 17:32    [6331211]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709

NickDee wrote:
> Хочется по списку ID-ов (~10 тыс. штук) получить записи из таблицы (на
> ~10 миллионов записей).
> Какая субд с этим справится эффективно?

Любая, поддерживающая индексирование.

Но 10 тыщ штук записей - довольно много.
В таких случаях лучше обрабатывать данные
в самой БД с помощью SQL.

> А какая справится с этой задачей эффективно одним SQL-запросом?

Тоже любая. ЕСЛИ ID-ы идут подряд, то вам лучше использовать
кластерный индекс, если я конечно всё правильно понял, а вы ничего
не утаили. СУБД, поддерживающих кластерные индексы, тоже много.

Posted via ActualForum NNTP Server 1.4

20 окт 08, 20:09    [6331890]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709

NickDee wrote:

> select * from SomeTable where Id in (тут 10 тыщ значений через запятую)

В такой постановке вопроса лучше 10 тыщ запросов вида

select * from SomeTable where Id = @id1

select * from SomeTable where Id = @id2


или один типа

select * from SomeTable where Id = @id1
union all
select * from SomeTable where Id = @id2
....

ЕСЛИ id-ы идут не подряд. Тогда лучше просто диапазон задать.

> А такой запрос случайно не вылезет за какие-нибудь ограничения сервера
> (например на кол-во элементов в IN, или на длину sql-запроса)?

Может запросто. Но такие идиоцкие запросы пишите не вы один, так
что многие современные СУБД к этому уже готовы.
А так - конечно надо проверять, и , понятно, если завтра их окажется не 10 тыщ,
а 20, то ...

> Как с такими ограничениями в Orace и MSSQL?

Смотрите в документации. Это всё время меняется, надо смотреть конкр. версию.

Posted via ActualForum NNTP Server 1.4

20 окт 08, 20:15    [6331902]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Yo.!
Guest
в оракле можно еще передать в сторед процедуру массив из 10К элементами и потом с помощью TABLE и CAST юзать как обычную таблицу.
20 окт 08, 20:21    [6331910]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Yo.!
в оракле можно еще передать...


MS SQL 2008 -> table-valued parameters. ;)
21 окт 08, 08:36    [6332651]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Yo.!
в оракле можно еще передать в сторед процедуру массив из 10К элементами и потом с помощью TABLE и CAST юзать как обычную таблицу.

извиняюсь за офтопик: и можно эту TABLE джоинить с обычными таблицами? если можно - можно пример синтаксиса? и можно ли это делать в 9-й версии?
21 окт 08, 09:14    [6332753]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Ggg_old
Member

Откуда: Одесса
Сообщений: 1342
закидывате все айдишники на сервер в таблицу(временную например) и потом используете ее в join.
закидывать можно раными способами, конкретный механизм зависит от сервера и ваших требований.

Why CORBA is DEAD?
21 окт 08, 11:01    [6333477]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Yo.!
Guest
SergSuper

извиняюсь за офтопик: и можно эту TABLE джоинить с обычными таблицами? если можно - можно пример синтаксиса? и можно ли это делать в 9-й версии?


да, вроде с 8i можно: http://www.citforum.ru/database/oracle/cast/
передать масив из пхп вот так: http://php.net/manual/pt_BR/function.oci-bind-array-by-name.php

а в оракле примерно так:
SQL> create or replace type a_t is table of varchar2(32);                                                                                      
                                                                                                                                      
Type created.

declare
 a a_t := a_t('DFCFIDGJDC','J@ELBOBLEI');
begin
 for cv in (select deptno from emp where ename in (select * from table(a)))
 loop
  dbms_output.put_line(cv.deptno);
 end loop;
end;
/                                                                                                                                              

1
1

PL/SQL procedure successfully completed.
у меня где-то на сайтике работает.
21 окт 08, 11:38    [6333785]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
не получается
...
type RLIM is record (D	varchar2(128)),
type ALIM is table of RLIM index by binary_integer;
mLim	ALIM;
mWhat	number;
begin
...
  select count(*) into mWhat from table(mLim);
end;

PL/SQL: ORA-22905: невозможно получить к строкам элементов не вложенных таблиц
или тип надо обязательно создавать?
21 окт 08, 12:22    [6334124]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Yo.!
Guest
SergSuper

или тип надо обязательно создавать?

у тебя ALIM это набор из RLIM, вложеный. там как раз с этим CAST нужно колдовать.
21 окт 08, 12:55    [6334364]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
тыц
Member

Откуда: центральная нигерия
Сообщений: 4214
SergSuper
не получается
PL/SQL: ORA-22905: невозможно получить к строкам элементов не вложенных таблиц[/src]или тип надо обязательно создавать?
ахтунг! в sql можно работать только с sql типами, те ALIM вам надо определить на уровне базы
21 окт 08, 14:09    [6335019]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Gluk (Kazan)
Member

Откуда:
Сообщений: 9365
SergSuper
не получается
...
type RLIM is record (D	varchar2(128)),
type ALIM is table of RLIM index by binary_integer;
mLim	ALIM;
mWhat	number;
begin
...
  select count(*) into mWhat from table(mLim);
end;

PL/SQL: ORA-22905: невозможно получить к строкам элементов не вложенных таблиц
или тип надо обязательно создавать?


Т.е. про то что с index by не получится работать из SQL никто не скажет ?
Это ассоциативный массив (PL/SQL), а не коллекция (SQL).
Сказанное не отменяет предыдущих ораторов по поводу объявления типа на уровне SQL и необходимости приведения типов
21 окт 08, 14:22    [6335103]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
miksoft
Member

Откуда:
Сообщений: 38921
NickDee
miksoft
А каков размер блока и средняя длина записей?
не думаю что это важно, ведь перебор 10 миллионов записей делать никто не будет.
Это достаточно важно, т.к. на основе этой (не только этой, но в т.ч. и этой) информации оптимизатор будет принимать решение о методе доступа к таблице.
Например, если размер блока 16 Кб, а размер записи 8 байт, то в каждый блок попадает примерно 2000 записей. А выбрать нам нужно примерно каждую тысячную запись. Если принять, что искомые записи распределены в таблице примерно равномерно, то получается, что нам придется прочитать все или почти все блоки таблице. В таком случае использовать индекс не эффективно, полное сканирование таблицы будет эффективнее.
21 окт 08, 18:59    [6337099]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
NickDee
Хочется по списку ID-ов (~10 тыс. штук) получить записи из таблицы (на ~10 миллионов записей).
Какая субд с этим справится эффективно?
А какая справится с этой задачей эффективно одним SQL-запросом?
Автор, а что мешает вам загрузить эти 10000 IDшников в базу, а потом JOIN с большой таблицей? Что за блажь такая, обязательно "пропихнуть" их через WHERE?
21 окт 08, 20:59    [6337380]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Микросекунда
Member

Откуда: Из предыдущего мгновения
Сообщений: 1905
А какие у уважаемых СУБД ограничения на размер текста запроса ? А то если запихнуть 10к идэшников в текст, то даже если по 6 цифр на один идэ + запятые, это будет больше 70к.

Posted via ActualForum NNTP Server 1.4

22 окт 08, 07:01    [6338263]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Микросекунда
А какие у уважаемых СУБД ограничения на размер текста запроса ? А то если запихнуть 10к идэшников в текст, то даже если по 6 цифр на один идэ + запятые, это будет больше 70к.


MS SQL ~256 Mb
22 окт 08, 08:16    [6338326]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
stopor
Guest
PostgreSQL - не нашел явного ограничения, похоже зависит только от доступной памяти

50-мегабайтный запрос SELECT 1 FROM tab WHERE id in (...) с миллионом значений отработал за 3 минуты, выжрав 1.7ГБ памяти сервера.
Для 100МБ запроса уже не хватило :)
22 окт 08, 10:39    [6339024]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
miksoft
Member

Откуда:
Сообщений: 38921
Oracle - не более 1000 значений.

MySQL - размер SQL запроса может быть не больше значения переменной max_allowed_packet (значение по умолчанию - 1 Мб).
22 окт 08, 10:49    [6339094]     Ответить | Цитировать Сообщить модератору
 Re: Эффективная выборка записей по списку ID  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Ребята, занакуя?! Занакуя мерять ограничения СУБД в таком вопросе? Чтобы померяться звонкостью звука в штанах? Есть же решение
Ggg_old
закидывате все айдишники на сервер в таблицу(временную например) и потом используете ее в join.
закидывать можно раными способами, конкретный механизм зависит от сервера и ваших требований.

А если делаем так
select * from SomeTable where Id in (тут 10 тыщ значений через запятую)
то логика такая получается. Делаем криво запрос, а потом гоним на СУБД: "Фууу... какая вона слабая ... Вот у Оракула ....эт да!"
22 окт 08, 13:35    [6340446]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Сравнение СУБД Ответить