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

Откуда:
Сообщений: 17
Доброго времени суток!

Помогите оптимизировать запрос. Он нереально долго выполняется при небольшом объеме данных (около 40 минут для 250 тыс строк).
При этом без условия на время ([DEBITOR].D_UBJH + [DEBITOR].D_UBJJ + [DEBITOR].D_UBMM + [DEBITOR].D_UBTT >= '20140101' справляется за 20 секунд.

SELECT [DEBITOR].D_INK_NR, [DEBITOR].D_SNR, [TIT].T_NR,  
   IIF( ([TIT].T1_KZ <> 16 AND ([TIT].T1_ZV = 'j' OR [TIT].T1_ZV = 'J')  AND ([TIT].T1_GS = 'j' OR [TIT].T1_GS = 'J')) 
      OR ([TIT].T2_KZ <> 16 AND ([TIT].T2_ZV = 'j' OR [TIT].T2_ZV = 'J')  AND ([TIT].T2_GS = 'j' OR [TIT].T2_GS = 'J')) 
      OR ([TIT].T3_KZ <> 16 AND ([TIT].T3_ZV = 'j' OR [TIT].T3_ZV = 'J')  AND ([TIT].T3_GS = 'j' OR [TIT].T3_GS = 'J')) , 'YES', 'NO' ) AS ISTIT  
FROM DEBITOR, TIT 
WHERE [TIT].T_INKNR=[DEBITOR].D_INK_NR AND [TIT].T_SNR=[DEBITOR].S_SNR 
AND [DEBITOR].D_SPERR = '0' AND [DEBITOR].D_ERLDAT ='00000000'  AND [DEBITOR].D_UBJH + [DEBITOR].D_UBJJ + [DEBITOR].D_UBMM + [DEBITOR].D_UBTT >= '20140101'


Очень надеюсь на вашу помощь!
11 сен 19, 15:38    [21968623]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Извините, не написал необходимые уточнения:
В таблице TIT первичный ключ состоит из 3 полей: T_INKNR + T_SNR + T_NR
В таблице DEBITOR ПК состоит из 2 полей: S_INK_NR + S_SNR
Все поля - STRING, за исключением T1_KZ, который INT.

В одной таблице хранятся задолженности (DEBITOR), в другой - акты(TIT). Нужно проверить имеет ли задолженность соответствующий ей акт. Одна задолженность может быть нескольких видов и ей могут соответствовать несколько актов.
11 сен 19, 16:44    [21968675]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 316
автор
[DEBITOR].D_UBJH + [DEBITOR].D_UBJJ + [DEBITOR].D_UBMM + [DEBITOR].D_UBTT >= '20140101'
Поясните что хранится в каждом из 4-х полей и какой их тип. Они индексированные?
Значения месяца и дня могут отличаться от 01?
автор
[TIT].T1_ZV = 'j' OR [TIT].T1_ZV = 'J'
Если у вас обычная БД Акса, то это лишнее - JET не учитывает регистр.
11 сен 19, 16:48    [21968681]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Кривцов Анатолий
автор
[DEBITOR].D_UBJH + [DEBITOR].D_UBJJ + [DEBITOR].D_UBMM + [DEBITOR].D_UBTT >= '20140101'
Поясните что хранится в каждом из 4-х полей и какой их тип. Они индексированные?
Значения месяца и дня могут отличаться от 01?

В них хранится значение даты задолжности D_UBJH [столетие - 19 или 20], D_UBJJ[Год - от 1 до 99], D_UBMM[Месяц - от 1 до 12], D_UBTT [День - от 1 до 31]

Кривцов Анатолий
автор
[TIT].T1_ZV = 'j' OR [TIT].T1_ZV = 'J'
Если у вас обычная БД Акса, то это лишнее - JET не учитывает регистр.

спасибо! попробую
11 сен 19, 17:27    [21968716]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stanislav P
Member

Откуда: Сочи
Сообщений: 82
argnis
В них хранится значение даты задолжности D_UBJH [столетие - 19 или 20], D_UBJJ[Год - от 1 до 99], D_UBMM[Месяц - от 1 до 12], D_UBTT [День - от 1 до 31]
спасибо! попробую


Разбей запрос на SELECT .... D_UBJH="20" AND D_UBJJ = "14" и так далее, и посмотри на скорость выполнения.
11 сен 19, 17:48    [21968754]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Stanislav P
argnis
В них хранится значение даты задолжности D_UBJH [столетие - 19 или 20], D_UBJJ[Год - от 1 до 99], D_UBMM[Месяц - от 1 до 12], D_UBTT [День - от 1 до 31]
спасибо! попробую


Разбей запрос на SELECT .... D_UBJH="20" AND D_UBJJ = "14" и так далее, и посмотри на скорость выполнения.


Тут дело в том, что дата может быть любая. Ее задает ползователь.
11 сен 19, 17:53    [21968765]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stanislav P
Member

Откуда: Сочи
Сообщений: 82
argnis, не имеет значение, разбей дату введенную юзером так, как я ранее показал и проверь скорость работы.
11 сен 19, 17:58    [21968780]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Stanislav P
argnis, не имеет значение, разбей дату введенную юзером так, как я ранее показал и проверь скорость работы.


Заработало действительно быстрее. Правда не пойму почему, но очень большое Вам спасибо!
11 сен 19, 18:07    [21968796]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
MrShin
Member

Откуда:
Сообщений: 1276
FROM DEBITOR, TIT

Не делайте так, если вы действительно не хотите получить декартово произведение строк. Перепишите через нормальный JOIN и проверьте наличие индексов по всем полям, входящим в джоин и WHERE
12 сен 19, 06:26    [21969062]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stanislav P
Member

Откуда: Сочи
Сообщений: 82
argnis
Заработало действительно быстрее. Правда не пойму почему, но очень большое Вам спасибо!

У тебя в запросе идёт конкатенация строк: [DEBITOR].D_UBJH + [DEBITOR].D_UBJJ, а это медленная операция, тем более конкатенация сразу нескольких значений. Поэтому лучше делать через AND: [DEBITOR].D_UBJH ="20" AND [DEBITOR].D_UBJJ="14"
12 сен 19, 09:37    [21969129]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 316
argnis
В них хранится значение даты задолжности D_UBJH [столетие - 19 или 20], D_UBJJ[Год - от 1 до 99], D_UBMM[Месяц - от 1 до 12], D_UBTT [День - от 1 до 31]
Интересно, это вы придумали такой изощренный способ хранения даты? Да еще и в текстовых полях. Хранение даты в одном индексированном поле типа "Дата/Время" удобнее во всех отношениях и без проблем можно получить любой компонент даты.
12 сен 19, 09:45    [21969135]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Кривцов Анатолий
argnis
В них хранится значение даты задолжности D_UBJH [столетие - 19 или 20], D_UBJJ[Год - от 1 до 99], D_UBMM[Месяц - от 1 до 12], D_UBTT [День - от 1 до 31]
Интересно, это вы придумали такой изощренный способ хранения даты? Да еще и в текстовых полях. Хранение даты в одном индексированном поле типа "Дата/Время" удобнее во всех отношениях и без проблем можно получить любой компонент даты.

База создавалась очень давно и что-то менять в ней я не имею права
12 сен 19, 10:08    [21969149]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
MrShin
FROM DEBITOR, TIT

Не делайте так, если вы действительно не хотите получить декартово произведение строк. Перепишите через нормальный JOIN и проверьте наличие индексов по всем полям, входящим в джоин и WHERE


Я проверял, JOIN работает нереально медленно. И индексов почти нет, я не могу менять базу.
12 сен 19, 10:13    [21969155]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stanislav P
Member

Откуда: Сочи
Сообщений: 82
Кривцов Анатолий
Интересно, это вы придумали такой изощренный способ хранения даты? Да еще и в текстовых полях. Хранение даты в одном индексированном поле типа "Дата/Время" удобнее во всех отношениях и без проблем можно получить любой компонент даты.

Я бы использовал DOUBLE поле, с ним меньше проблем при индексировании, при создании запросов, даже не нужно думать о формате даты.
12 сен 19, 10:35    [21969195]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6176
Кривцов Анатолий
Интересно, это вы придумали такой изощренный способ хранения даты? Да еще и в текстовых полях. Хранение даты в одном индексированном поле типа "Дата/Время" удобнее во всех отношениях и без проблем можно получить любой компонент даты.
argnis
....База создавалась очень давно и что-то менять в ней я не имею права
argnis
Помогите оптимизировать запрос....
На не правильно созданной таблице.
Без её изменения,как не изощряйся,ничего путного не выйдет
12 сен 19, 12:46    [21969346]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
sdku
Кривцов Анатолий
Интересно, это вы придумали такой изощренный способ хранения даты? Да еще и в текстовых полях. Хранение даты в одном индексированном поле типа "Дата/Время" удобнее во всех отношениях и без проблем можно получить любой компонент даты.
argnis
....База создавалась очень давно и что-то менять в ней я не имею права
argnis
Помогите оптимизировать запрос....
На не правильно созданной таблице.
Без её изменения,как не изощряйся,ничего путного не выйдет



Stanislav P очень хорошую идею подкинул, за что ему спасибо
12 сен 19, 15:16    [21969569]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Кривцов Анатолий
Member

Откуда:
Сообщений: 316
Stanislav P
Я бы использовал DOUBLE поле, с ним меньше проблем при индексировании, при создании запросов, даже не нужно думать о формате даты.

argnis
Stanislav P очень хорошую идею подкинул, за что ему спасибо

И чем хороша идея? В Аксе поле "Дата/Время" это то же DOUBLE, с таким же индексированием, но значения отображаются как дата. Все что изменится при использовании типа DOUBLE, это, например, число 43720 вместо 12.09.19 или (еще круче) 43720.6625810185 вместо 12.09.19 15:54:07.
Это сильно удобно? А если надо отобрать записи за последний месяц, надо знать числовые эквиваленты начальной и конечной даты

Кстати, конструкция WHERE [ПолеТипаДата] = 43720 вполне работоспособна и вернет всё с датой 12.09.19.

На всякий случай:
?Cdbl(Date())
 43720 
12 сен 19, 15:55    [21969601]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Кривцов Анатолий,

Идея хорошая для моего задания, так как я работю с тем, что есть. Правки в базу вносить не могу. Формат даты определен так, как он есть.
12 сен 19, 16:36    [21969650]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Подкиньте пожалуйста идею, как реализовать запрос. Таблицы нарисованы к EXCEL.
12 сен 19, 16:46    [21969663]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
Забыл файл

К сообщению приложен файл (sql.xlsx - 6Kb) cкачать
12 сен 19, 16:46    [21969664]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
Stanislav P
Member

Откуда: Сочи
Сообщений: 82
Кривцов Анатолий,

Double всегда double, а дата/время в Access вариативно и зависит от фаз луны, региональных настроек и настроения очередного индуса в команде разработки MS Access.
Я как-то натолкнулся на проблему с типом поля дата/время. Потом кто-то из гуру по Акссесу то ли в статье, то ли в книге об этом писал.
По той же самой причине я не использую boolean поле, а вместо него integer с проверкой ввода на 0 и -1.
12 сен 19, 17:33    [21969691]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6176
sdku
argnis
Помогите оптимизировать запрос....
На не правильно созданной таблице.Без её изменения,как не изощряйся,ничего путного не выйдет

argnis
Подкиньте пожалуйста идею, как реализовать запрос....
Все это сильно напоминает циклическую ссылку
12 сен 19, 17:37    [21969693]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
argnis
Member

Откуда:
Сообщений: 17
sdku,

Просто прошлый запрос я не правильно составил, он не учитывал то, что вo второй таблице не все ключи из первой. К сожалению, LEFT JOIN работает очень медленно. Думаю, как обойтись без него.
13 сен 19, 10:00    [21970112]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос  [new]
sdku
Member

Откуда: Краснодар
Сообщений: 6176
argnis,
в данном случае словосочетание "циклическая ссылка" применена не к коду а к Вашему вопросу:
-помогите оптимизировать запрос
-не меняя таблицу ничего не выйдет
-ну подкиньте идею,только ничего не меняйте
замкнутый круг,однако
(разве что,пользуясь DateSerial, создать в запросе поле с датой и производить отбор по нему-но вряд ли это сильно что-то изменит)
13 сен 19, 12:01    [21970218]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить