ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Другие СУБД [игнор отключен] [закрыт для гостей] / Оптимизация запроса
25 сообщений из 44, страница 1 из 2
  1  все
Оптимизация запроса
    #38936414
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Здравствуйте.
Прошу помощи в оптимизации sql-запроса.
Описание:
Есть 2 таблицы SprTovar и Sklad
Необходимо сформировать выборку содержащую информацию о всех товарах и остатков по ним.
SprTovar - справочная таблица по всем товарам, в которой есть поля типа Kod, Name и прочее.
Sklad - таблица остатков. В эту таблицу заносится информация по кол-ву товара на определенную дату при условии что на эту дату было движение.
Т.е. например Яблоко.
01.01.2015 остаток = 100 шт
10.01.2015 был приход в кол-ве 200 шт.
15.01.2015 было реализовано 50 шт

В таблице Sklad касаемо это товара будут следующие записи
01.01.2015 100 (т.е. изначальный остаток)
10.01.2015 300 (включая приход +200)
15.01.2015 250 (минус реализация -50)

При добавлении товара в какой либо документ, необходимо отображать весь список товаров с указанием остатков по каждой записи. Необходимо учитывать что записи об остатках в таблице Sklad может и не быть.
Для выборки использую такой запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select a.*, c.Kol as Ostatok from SprTovar a
   left outer join (select b.DATA, b.KodTov, Sklad.Kol
                           from (select MAX(DATA) as DATA, KodTov
                                        from Sklad
                                        where KodSklad=0
                                          and DATA<='14.04.2015'
                                        group by KodTov
                                ) b,
                                Sklad
                           where b.KodTov=Sklad.KodTov
                             and b.DATA=Sklad.DATA
                             and Sklad.KodSklad=0
                    ) c ON a.KOD=c.KodTov
order by lower(a.Name)


Описание ключевых полей
Sklad.DATA - дата, на которое сформирован остаток
Sklad.KodTov - уникальный код товара
Sklad.Kol - количество на дату
Sklad.KodSklad - код склада на котором хранится товар.
SprTovar.Kod - код товара
SprTovar.Name - наименование товара

Выборка выполняется верно. Но!
Не устраивает скорость выборки. При 5000 записей в таблице SprTovar и 300000 записей в таблице Sklad (информация за 3 года) запрос выполняется около 10 секунд. Хотелось бы ускорить процесс выборки.
Прошу помощи в оптимизации sql-запроса...
Также рад выслушать замечания об эффективном хранении остатков товаров.

Спасибо за потраченное время.
...
Рейтинг: 0 / 0
14.04.2015, 19:25
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936430
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Андрей Рябенко,

1) Не перемешивать в пределах одного запроса ANSI-JOIN-ы и старообрядные череззапятушечные (на производительность не влияет, влияет на читабельность кода)
2) При поиске остатков товара по складу попробовать использовать не агрегацию, а селф-джойн
3) построить необходимые индексы, если их ещё нет.
...
Рейтинг: 0 / 0
14.04.2015, 19:38
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936444
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей РябенкоВ эту таблицу заносится информация по кол-ву товара на определенную
дату при условии что на эту дату было движение.
Убери это условие и будет тебе счастье. Запрос схлопнется до тривиального.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
14.04.2015, 19:53
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936452
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Индексы необходимые есть.
Dimitry SibiryakovАндрей РябенкоВ эту таблицу заносится информация по кол-ву товара на определенную
дату при условии что на эту дату было движение.
Убери это условие и будет тебе счастье. Запрос схлопнется до тривиального.

Я отказался от этого, ну в целях уменьшения объема базы. Хотя это будет быстрее.... выбирать не максимальную дату, а конкретную...
А по поводу оптимизации запроса по текущей ситуации подскажите что нибудь.
...
Рейтинг: 0 / 0
14.04.2015, 19:59
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936459
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей РябенкоЯ отказался от этого, ну в целях уменьшения объема базы.
И сколько мегабайт ты на этом сэкономил?
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
14.04.2015, 20:03
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936469
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovАндрей РябенкоЯ отказался от этого, ну в целях уменьшения объема базы.
И сколько мегабайт ты на этом сэкономил?

Думаю немного. Посчитаю напишу. Как раз из-за экономии выбрал этот вариант. Показался мне наиболее универсальным.

Но Ваша мысль хорошая. Если не найдется варианта выборки побыстрее, Ваш вариант будет оптимальным.
...
Рейтинг: 0 / 0
14.04.2015, 20:13
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936492
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovАндрей РябенкоВ эту таблицу заносится информация по кол-ву товара на определенную
дату при условии что на эту дату было движение.
Убери это условие и будет тебе счастье. Запрос схлопнется до тривиального.

Дмитрий, а как быть с ситуацией если например предприятие не работает несколько дней. Откуда возьмутся остатки на каждую дату?
...
Рейтинг: 0 / 0
14.04.2015, 20:44
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936496
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей РябенкоДмитрий, а как быть с ситуацией если например предприятие не работает
несколько дней. Откуда возьмутся остатки на каждую дату?
А тебе нужно получать остатки на дату, когда никто не работал?.. Ну, в любом случае они
возьмутся оттуда же, откуда и в любой рабочий день. Не хочешь же ты сказать, что процедуру
закрытия оборотов за день ты запускаешь вручную?..
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
14.04.2015, 20:58
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936500
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovАндрей РябенкоДмитрий, а как быть с ситуацией если например предприятие не работает
несколько дней. Откуда возьмутся остатки на каждую дату?
А тебе нужно получать остатки на дату, когда никто не работал?.. Ну, в любом случае они
возьмутся оттуда же, откуда и в любой рабочий день. Не хочешь же ты сказать, что процедуру
закрытия оборотов за день ты запускаешь вручную?..

Ну вот в моей ситуации:
В таблице Sklad касаемо товара будут следующие записи
01.01.2015 100
10.01.2015 300
15.01.2015 250
16.01.2015 - не рабочий день.
И вот наступило 17 число, мой запрос выборку выполнит (выберет макс дату, т.е. 15.01.2015 и оттуда возьмет количество).
А какой Вы предлагаете вариант запроса? (как я понял вы предлагаете хранить остатки на каждый день?).
Поясните пожалуйста.
...
Рейтинг: 0 / 0
14.04.2015, 21:03
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936516
Молочный Александр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Молочный Александр
Гость
Андрей Рябенко, так попробуй
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select a.*, c.Kol as Ostatok from SprTovar a
   left outer join (select b.KodTov, Sum(Kol) Kol
                    from Sklad b
                    where
                      KodSklad=0  and DATA <= '14.04.2015'
                    group by KodTov
                    having b.DATA = MAX(b.DATA)
                    ) c ON a.KOD=c.KodTov
order by lower(a.Name)
...
Рейтинг: 0 / 0
14.04.2015, 21:40
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936526
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Молочный АлександрАндрей Рябенко, так попробуй
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select a.*, c.Kol as Ostatok from SprTovar a
   left outer join (select b.KodTov, Sum(Kol) Kol
                    from Sklad b
                    where
                      KodSklad=0  and DATA <= '14.04.2015'
                    group by KodTov
                    having b.DATA = MAX(b.DATA)
                    ) c ON a.KOD=c.KodTov
order by lower(a.Name)


Ругается на отсутствие поля в group by
Column not found in GROUP BY clause: DATA in SELECT or HAVING clause.
...
Рейтинг: 0 / 0
14.04.2015, 21:59
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936528
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей РябенкоА какой Вы предлагаете вариант запроса? (как я понял вы предлагаете
хранить остатки на каждый день?).
Да. Предлагаю на каждый день. Раз уж ты так упёрто их вообще решил хранить.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
14.04.2015, 22:00
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936532
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovАндрей РябенкоА какой Вы предлагаете вариант запроса? (как я понял вы предлагаете
хранить остатки на каждый день?).
Да. Предлагаю на каждый день. Раз уж ты так упёрто их вообще решил хранить.

Дмитрий, я не уперто решил их хранить. Но как быть с вышеописанной ситуацией? Когда остатков нет, откуда их взять? Что, по выполнению определенного события? Какого тогда? Поясните пожалуйста.
...
Рейтинг: 0 / 0
14.04.2015, 22:03
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936542
Молочный Александр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Молочный Александр
Гость
Андрей Рябенко,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select a.*, c.Kol as Ostatok from SprTovar a
   left outer join (select b.DATA, b.KodTov, Sum(Kol) Kol
                    from Sklad b
                    where
                      b.KodSklad=0  and b.DATA <= '14.04.2015'
                    group by b.DATA, b.KodTov
                    having b.DATA = MAX(b.DATA)
                    ) c ON a.KOD=c.KodTov
order by lower(a.Name)
...
Рейтинг: 0 / 0
14.04.2015, 22:24
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936545
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей РябенкоКогда остатков нет, откуда их взять?
Посчитать как сумму оборотов.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
14.04.2015, 22:27
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936554
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovАндрей РябенкоКогда остатков нет, откуда их взять?
Посчитать как сумму оборотов.

Ну обычный select выполняется долго, а представьте выбирать сумму оборотов со всеми условиями по всем позициям. Ведь дольше будет.
...
Рейтинг: 0 / 0
14.04.2015, 22:41
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936556
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей Рябенкообычный select выполняется долго, а представьте выбирать сумму
оборотов со всеми условиями по всем позициям. Ведь дольше будет.
Может будет, может нет. Всё зависит от того как эти самые обороты хранить и на какой
именно момент времени требуется считать остатки.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
14.04.2015, 22:43
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936558
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Молочный АлександрАндрей Рябенко,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select a.*, c.Kol as Ostatok from SprTovar a
   left outer join (select b.DATA, b.KodTov, Sum(Kol) Kol
                    from Sklad b
                    where
                      b.KodSklad=0  and b.DATA <= '14.04.2015'
                    group by b.DATA, b.KodTov
                    having b.DATA = MAX(b.DATA)
                    ) c ON a.KOD=c.KodTov
order by lower(a.Name)


Результат - выбираются все товары с остатками по всем датам, т.е. примерно так:
Код товара\ Кол-во \Дата
1 100 01.01.2015
1 150 05.01.2015
1 120 06.01.2015
1 130 07.01.2015

Нет группировки по коду товара получается..., точнее есть группировка по коду+дате
...
Рейтинг: 0 / 0
14.04.2015, 22:45
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936565
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovАндрей Рябенкообычный select выполняется долго, а представьте выбирать сумму
оборотов со всеми условиями по всем позициям. Ведь дольше будет.
Может будет, может нет. Всё зависит от того как эти самые обороты хранить и на какой
именно момент времени требуется считать остатки.

Ну как вариант: попробую сделать таблицу оборотов, перепровести все документы с автозаполнением оборотов. И напишу про результат, быстрее будет или нет.
Все равно пока что другого рабочего варианта нет.
...
Рейтинг: 0 / 0
14.04.2015, 22:51
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936725
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
Андрей РябенкоОткуда возьмутся остатки на каждую дату?храни опорные остатки, например, на первое число месяца. Остальные вычисляй от ближайшего опорного +/- обороты.

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

Как вариант для текущего остатка держать его как хранимый агрегат на триггерах на таблице(ах) приходов-расходов. Но тут надо вдумчиво подходить к вопросу, шоб не сдохнуть на блокировках. Имеет смысл только если остаток нужен ОЧЕНЬ быстро и не так много конкурирующих продаж (например при очень широком ассортименте, когда каждый товар продает не более пары-тройки манагеров)
...
Рейтинг: 0 / 0
15.04.2015, 09:00
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936896
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Ivan_PisarevskyИмеет смысл только если текущий остаток нужен ОЧЕНЬ быстро
А если у него преобладают запросы остатков на вчера/позавчера/месяц назад/17 августа 1991
года, то будет нерентабельно. Но сама идея - держать один базовый остаток где-нибудь в
частотном максимуме обращений, а остальные от него высчитывать - правильная. Запрос в этом
случае тоже схлопывается до простого JOIN+GROUP BY.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
15.04.2015, 11:29
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936904
Андрей Рябенко
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Рябенко
Гость
Dimitry SibiryakovIvan_PisarevskyИмеет смысл только если текущий остаток нужен ОЧЕНЬ быстро
А если у него преобладают запросы остатков на вчера/позавчера/месяц назад/17 августа 1991
года, то будет нерентабельно. Но сама идея - держать один базовый остаток где-нибудь в
частотном максимуме обращений, а остальные от него высчитывать - правильная. Запрос в этом
случае тоже схлопывается до простого JOIN+GROUP BY.


Предположим следующую ситуацию.
Сегодня 15.04.2015.
Допустим остатки хранятся на конец месяца, соответственно последние остатки есть на 31.03.2015.
Также есть таблица оборотов (приход/расход).

Пользователю нужно сделать изменения в документе за 20.03.2015.
Он их сделал - провел документ.
В итоге надо пересчитать остатки на конец марта, т.е. на 31.03.2015. Так?
А если ему взбрело в голову изменить документ за месяцев 5 назад, то придется пересчитывать остатки на каждый конец месяца (т.е. на 5 дат)
Я понимаю, что нехорошо пользователю играться с задними числами. Но они пересчитывают.

В моем случае, остатки хранятся уже посчитанные (тем более на дату когда произошло движение, а не на каждый день).
Т.е. их не надо высчитывать, просто нужно взять.
Вот я и думаю что проблему можно решить, изменив sql-запрос.
...
Рейтинг: 0 / 0
15.04.2015, 11:42
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936929
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
Андрей РябенкоВ итоге надо пересчитать остатки на конец марта, т.е. на 31.03.2015. Так?Логичней сделать все опорные остатки по данному товару свежее этой даты невалидными, что приведет к тому, что хранимка отдающая остатки переключится автоматически на более ранние и отдаст таки правильные остатки, да, возможно на это уйдет чуть больше времени. ночером робот подтянет, все что протухло.

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

Андрей РябенкоВ моем случае, остатки хранятся уже посчитанные (тем более на дату когда произошло движение, а не на каждый день).
Т.е. их не надо высчитывать, просто нужно взять.Это тебе кажется, помедитируй еще над этим.
...
Рейтинг: 0 / 0
15.04.2015, 12:07
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936934
Ivan_Pisarevsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan_Pisarevsky
Участник
Андрей РябенкоЯ понимаю, что нехорошо пользователю играться с задними числами. Но они пересчитывают.Есть такое бухгалтерские понятие "закрытый период". Используй его в своих интересах. Если опорный остаток совпадает с границей закрытого периода, то никаких пересчетов остатков посередь рабочего не возникнет. Запросы на изменение задним числом надо одобрить ответственному за данную операцию бухгалтером, а не рядовым манагером по велению его левой пятки.
...
Рейтинг: 0 / 0
15.04.2015, 12:12
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #38936952
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Андрей РябенкоПользователю нужно сделать изменения в документе за 20.03.2015.
Он их сделал - провел документ.
В итоге надо пересчитать остатки на конец марта, т.е. на 31.03.2015. Так?
А если ему взбрело в голову изменить документ за месяцев 5 назад, то придется
пересчитывать остатки на каждый конец месяца (т.е. на 5 дат)
Я понимаю, что нехорошо пользователю играться с задними числами. Но они пересчитывают.
Во-первых, повторяю медленно: имеет смысл хранить только один базовый остаток. Если
изменяется документ раньше него, то триггер автоматически этот остаток скорректирует на
изменившуюся сумму. Поскольку задним числом документы проводятся редко, конкуренции и
связанных с ней проблем не будет.

Но даже если ты настаиваешь на хранении толпы остатков, то изменение пяти остатков это тот
же один запрос "update sklad set amount+:change where amount_date>:change_date". И
опять-таки операция редкая и риска нарваться на update conflict - нет.

Но повторю ещё раз: при хранении одного базового остатка, запрос получения остатка на
любую другую дату выливается в тривиальный и быстрый JOIN + GROUP BY.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
15.04.2015, 12:25
| Ответить | Цитировать | Написать  
25 сообщений из 44, страница 1 из 2
  1  все
Форумы / Другие СУБД [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=56&msg=38936556&tid=2015148]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 181ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 1ms
others: 234ms
total:  517ms
созд. / загр.: 517ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]