Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
Здравствуйте, уважаемые форумчане.
Помогите решить проблему с запросом: есть таблица с номером телефона (без начального +7) и есть таблица, в которой содержатся регион и мобильный оператор, к которому относится телефоны, но проблема в том, что речь в ней идет про диапазон телефонов.
Например, в одной таблице есть телефон 9379334565, а в другой таблице номера телефонов начинающиеся с 9379330000 и до 9379340000 принадлежат условно Мегафону в Республике Татарстан.

Сама проблема: При количестве телефонов в таблице около 9 тысяч им проставляется в соответствие Регион и Оператор из другой таблицы за не приемлимое время, более минуты. Я пытался номер телефона представлять как текст, создавать по нему индекс в одной таблице и то же самое проделывать с другой - на моем компе это полторы минуты. Пытался номер телефона представлять как число и по нему создавать индекс - время никак не уменьшается. Хуже всего то, что телефонов может быть около полумиллиона и данный запрос выполняется несколько часов.

Прошу Вашей помощи в оптимизации запроса, быть может как-то можно его упростить чтоб он выполнялся хотя бы несколько секунд. В прицепе база на аксессе с номерами телефонов и операторов, а также мои варианты запросов с индексами по строке и по числу. Буду признателен за любую помощь.
Для того чтоб загрузить файл на форум пришлось немного подрезать таблицы, сейчас таблица с телефонами содержит 2300 строк (было 9000), таблица операторов 2000 строк (было 6500), но тормознутось запроса можно заметить и на таких маленьких данных

К сообщению приложен файл (телефоны.rar - 131Kb) cкачать
30 мар 21, 20:40    [22302222]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
mds_world
Member

Откуда: Ташкент
Сообщений: 27562
papant1, попробуйте джойнами связать таблицы вместо Where
SELECT t1.телефон_число, t2.регион, t2.оператор
FROM телефоны AS t1 inner join РегионыОператоры AS t2 on (t1.телефон_число between t2.ОТ_число and t2.ДО_число)
30 мар 21, 21:02    [22302231]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
mds_world,
такое чувство, что еще медленнее стало (((
30 мар 21, 21:13    [22302238]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
MX-9
Member

Откуда: LIBAVA
Сообщений: 521
papant1,

перекинул Ваши файлы в наш ексцел (слегка навороченый)

Ваш пример считает примерно за секунду

Думаю что миллион телефонов раскидает за 10 секунд

Если интересует - пришлем

Приложен результат ( справа - Ваши данные для сравнения)

К сообщению приложен файл (telefoni.XLSX - 127Kb) cкачать
31 мар 21, 00:59    [22302289]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
vmag
Member

Откуда: MP
Сообщений: 3970
papant1,

оба запроса в вашем примере ровно 1 секунда...
Что-то не так в вашем королевстве...
Я так думаю вы всё это по сетке фигачите, тогда так и должно быть...
Если в акцессе только пример, а в реале есть сервак, то делайте это на серваке...
Ну или комп поменяйте, ssd вместо hdd, памяти добавьте...
Ну реально - на компе телек смотрю, камтазия видео рендерит, оба запроса секунда...
31 мар 21, 02:04    [22302296]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
MX-9
papant1,

перекинул Ваши файлы в наш ексцел (слегка навороченый)
Ваш пример считает примерно за секунду

Давайте попробуем, в личку киньте ссыль пожалуйста
31 мар 21, 06:07    [22302301]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
vmag
papant1,
оба запроса в вашем примере ровно 1 секунда...
Что-то не так в вашем королевстве...

Если просто запускать запросы, то да, вроде оно быстро выполняется.... Но, если вы попробуете перейти к последней записи, то увидите тормоза. И, как я сказал, обе таблицы очень порезаны, поэтому если в таблице телефонов будет полмиллиона записей, а в таблице с регионами в районе 6.5 тыс, то несколько часов ожидания обеспечены
31 мар 21, 06:09    [22302303]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
MX-9
Member

Откуда: LIBAVA
Сообщений: 521
papant1,

Ваш адрес скрыт
Пришлите миллионые таблицы - проверю у себя на скорость
sia.enters@inbox.lv

--------
31 мар 21, 09:27    [22302345]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
ROI
Member

Откуда: г. Тюмень
Сообщений: 2186
papant1,

RecordSet пробывали?
31 мар 21, 10:56    [22302379]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
vmag
Member

Откуда: MP
Сообщений: 3970
papant1
Если просто запускать запросы, то да, вроде оно быстро выполняется.... Но, если вы попробуете перейти к последней записи, то увидите тормоза.


ну а кто же в таких запросах ходит по записям? да и зачем?
я вас уверяю, что если любой из этих запросов переделать в запрос на добавление в промежуточную таблицу,
то после добавления вы по промежуточной таблице будете ходить туда-сюда как у себя дома...
Ну единственное сам запрос на добавление будет выполняться не секунду, а 4-5 ибо добавляются операции записи...
31 мар 21, 11:27    [22302397]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
ROI
Member

Откуда: г. Тюмень
Сообщений: 2186
vmag
ну а кто же в таких запросах ходит по записям? да и зачем?

До меня тоже дошло, а зачем по этим тысячам записей ходить
(глазами чёли фильтровать)
31 мар 21, 15:19    [22302548]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
MX-9
papant1,
Пришлите миллионые таблицы - проверю у себя на скорость
sia.enters@inbox.lv
--------

Ушло, проверьте почту
1 апр 21, 05:26    [22302792]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
vmag
papant1
Если просто запускать запросы, то да, вроде оно быстро выполняется.... Но, если вы попробуете перейти к последней записи, то увидите тормоза.


ну а кто же в таких запросах ходит по записям? да и зачем?
я вас уверяю, что если любой из этих запросов переделать в запрос на добавление в промежуточную таблицу,
то после добавления вы по промежуточной таблице будете ходить туда-сюда как у себя дома...
Ну единственное сам запрос на добавление будет выполняться не секунду, а 4-5 ибо добавляются операции записи...

Не очень понял про промежуточную таблицу, что в нее положить? А еще лучше с примером insert-a
1 апр 21, 05:27    [22302793]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
vmag
Member

Откуда: MP
Сообщений: 3970
papant1
Не очень понял про промежуточную таблицу, что в нее положить?


в нее положить результат селекции и потом работать с этим результатом, а не работать с процессом самой селекции на ходу...
- в примере пустая таблица Результат
- выполните там же запрос на добавление в таблицу результат...
- откройте секунд через 5-10 (не знаю сколько будет на вашем компе) таблицу результат и попробуйте походить по ней в конец и начало....

К сообщению приложен файл (телефоны.rar - 131Kb) cкачать
1 апр 21, 13:50    [22302960]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
MX-9
Member

Откуда: LIBAVA
Сообщений: 521
papant1
MX-9
papant1,
Пришлите миллионые таблицы - проверю у себя на скорость
sia.enters@inbox.lv
--------

Ушло, проверьте почту


проверил
отправил результат на Вашу почту
в пределах 20 сек формирует и выводит на экран таблицу
в ней : ввод - корректировка - в начале или в конце - мгновенно
1 апр 21, 19:54    [22303096]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
vmag
papant1
Не очень понял про промежуточную таблицу, что в нее положить?


в нее положить результат селекции и потом работать с этим результатом, а не работать с процессом самой селекции на ходу...
- в примере пустая таблица Результат
- выполните там же запрос на добавление в таблицу результат...
- откройте секунд через 5-10 (не знаю сколько будет на вашем компе) таблицу результат и попробуйте походить по ней в конец и начало....

Идея Ваша понятна, на самом деле я делал подобное - в таблицу с телефонами добавлял 2 столбца Оператор и Регион и апдейтил ее, у вас решение - наполнить промежуточную таблицу, что тоже вариант. Просто дело в том, что выполнение запроса МЕДЛЕННОЕ. Например, по вашему образцу с числом записей в таблице телефонов 620 тысяч запрос на заполнение таблицы РЕЗУЛЬТАТ на моем компе (i7, ssd, 16Гб ОЗУ) выполняется более 20 минут. А все потому, что не используются индексы при написании запроса
t1.телефон_строка>=t2.ОТ_строка And t1.телефон_строка<=t2.До_строка;
Это можно увидеть, если данные поместить в MySQL и выполнить запрос с EXPLAINом, который говорит, что использует индекс по таблице Телефоны и не использует по РегионыОператоры:
ID, SELECT_TYPE, Table, Partition, Type, PossibleKeys, Key, Key_Len, ref, rows, filtered, extra
'1', 'SIMPLE', 'тел', NULL, 'index', 'column2_UNIQUE', 'column3_UNIQUE', '8', NULL, '2356', '100.00', 'Using index'
'1', 'SIMPLE', 'РегОпер', NULL, 'ALL', 'idx_ro__to_str,idx_ro__from_str', NULL, NULL, NULL, '6380', '11.11', 'Range checked for each record (index map: 0x3)'
Таким образом из столбца Extra можем увидеть, что для таблицы PH (Телефоны) используется индек, а для RO (РегионыОператоры) нет, это же видно из столбцов PossibleKeys (column2_UNIQUE, 'idx_ro__to_str,idx_ro__from_str') и Key (column3_UNIQUE, NULL) в скобочках указаны значения для (Телефоны, РегионыОператоры) соответственно
Т.е. для использования индекса во второй таблице не подходит условие это условие
t1.телефон_строка>=t2.ОТ_строка And t1.телефон_строка<=t2.До_строка
его надо переписать как-то. в интернете пишут, что обычно помогает JOIN, но у меня ума не хватает ). К сожалению
2 апр 21, 07:28    [22303214]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4739
papant1,

может добавить отсечку по первым 3 символам номера
SELECT t1.телефон_строка, t2.регион, t2.оператор
FROM телефоны AS t1, РегионыОператоры AS t2
WHERE mid(t1.телефон_строка,1,3)= mid(t2.ОТ_строка,1,3) and 
 (((t1.телефон_строка)>=[t2].[ОТ_строка] And (t1.телефон_строка)<=[t2].[До_строка]));

можно эти 3 символа вывести в отдельное поле и проиндексировать его

Сообщение было отредактировано: 2 апр 21, 08:24
2 апр 21, 08:31    [22303219]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 5182
papant1
его надо переписать как-то. в интернете пишут, что обычно помогает JOIN, но у меня ума не хватает ).

mds_world
papant1, попробуйте джойнами связать таблицы вместо Where
SELECT t1.телефон_число, t2.регион, t2.оператор
FROM телефоны AS t1 inner join РегионыОператоры AS t2 on (t1.телефон_число between t2.ОТ_число and t2.ДО_число)
2 апр 21, 08:45    [22303221]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 7269
Вопрос:
-ЗАЧЕМ в таблицах хранить ИЗЛИШЕСТВУЮЩУЮ информацию ОТ/ДО строка/текст,а если в "числах" возможны лидирующие нули..ответ однозначен. Да и сомневаюсь что без связей между таблицами с наполнением свыше~50000 записей возможно создать быстродействующий запрос
А о связи Вам говорили давным-давно 2230223,но намного лучше "наморщить лоб" и придумать другую
2 апр 21, 15:19    [22303419]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
sdku
Вопрос:
-ЗАЧЕМ в таблицах хранить ИЗЛИШЕСТВУЮЩУЮ информацию ОТ/ДО строка/текст,а если в "числах" возможны лидирующие нули..ответ однозначен. Да и сомневаюсь что без связей между таблицами с наполнением свыше~50000 записей возможно создать быстродействующий запрос
А о связи Вам говорили давным-давно 2230223,но намного лучше "наморщить лоб" и придумать другую

Не оч понял про ИЗЛИШЕСТВУЮЩУЮ информацию и про "числа", но хочу заметить, что в таблице РегионыОператоры есть поля ОТ_строка, ДО_строка, а также для измерения быстродействия добавлены ОТ_число и ДО_число - думалось, что по числу индекс будет работать или быстрее выполняться запрос - но это не влияет.
По поводу сообщения 2230223 вообще ничего не понял, там какой-то другой язык программирования и разговор о высоких материях.... Про промежуточную таблицу я усвоил, так и делаю (почти, апдейчу таблицу с Телефонами с добавлением информации из таблицы РегионыОператоры), но этот запрос выполняется также несколько часов при числе записей в несколько миллионов.
Видимо запрос не получится оптимизировать, буду жить так
Всем большое спасибо за участие
3 апр 21, 05:34    [22303616]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
papant1
Member

Откуда:
Сообщений: 14
ПЕНСИОНЕРКА,
Идея отличная, уже даже придумал где ее можно применить (не в этом проекте), но сюда решение не годится - ведь для создания поля с помещением туда какой-то подстроки (длину которой можно подобрать опытным путем) придется все равно один раз перебрать все записи из таблицы, а это уже делается при создании промежуточной таблицы, в которую помещается для каждого телефона соответствующий ему Регион и Оператор
3 апр 21, 05:38    [22303617]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4739
papant1
придется все равно один раз перебрать все записи

ввести дополнительное текстовое поле в основную таблицу и через update заполнить, если оно пустое - почти разовая процедура
3 апр 21, 09:27    [22303633]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
ПЕНСИОНЕРКА
Member

Откуда: Владимирская обл
Сообщений: 4739
papant1
(длину которой можно подобрать опытным путем

подбор не требуется - точно 3 знака, хотя совпадающих в тел_до и тел_после от 3 до 7 символов
3 апр 21, 09:30    [22303635]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 5182
ПЕНСИОНЕРКА
papant1
придется все равно один раз перебрать все записи

ввести дополнительное текстовое поле в основную таблицу и через update заполнить, если оно пустое - почти разовая процедура
не проще сразу айди опсоса ввести?
3 апр 21, 15:24    [22303790]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
Панург
Member

Откуда: настоящему индейцу завсегда везде ништяк
Сообщений: 5182
papant1, да и всё это с определением опсоса по номеру ещё та идея... Сейчас со старым номером можно переходить к другому опсосу и хотя таких случаев немного, но они компрометируют всю эту затею.

Сообщение было отредактировано: 3 апр 21, 15:21
3 апр 21, 15:28    [22303796]     Ответить | Цитировать Сообщить модератору
 Re: Очень долгое выполнение запроса, более минуты  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 7269
При копировании ссылки "потерял" цифру-должно быть 22302231
Если правильно Вас понял-можно так(форма операторРегион):

К сообщению приложен файл (телефоны.rar - 24Kb) cкачать

Сообщение было отредактировано: 3 апр 21, 16:14
3 апр 21, 16:18    [22303814]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft Access Ответить