Оптимизация запроса.

[EnMac]

Здравствуйте.

Прошу помощи в оптимизации 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 может и не быть.
Для выборки использую такой запрос:

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 - наименование товара
Индексы Sklad (KodTov, Data, KodSklad), SprTovar (Kod, Name)

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

Спасибо за потраченное время.

[Аватар]

1. субд - ?
2. какие индексы в обеих таблицах - ?
3. напрашивается (для MS SQL)

select a.*, 
    (SELECT TOP 1 S.Koli
       FROM Sklad S
       WHERE S.KodSklad=0 AND S.KodTov=a.KOD AND S.DATA<='14.04.2015'
       ORDER BY S.DATA DESC)  as Ostatok 
  FROM SprTovar a
  order by lower(a.Name)

4. Ни когда бы так остатки не держал

PS забыл о дате, подправил

[EnMac]

Сообщение от Аватар

1. субд - ?
2. какие индексы в обеих таблицах - ?
3. напрашивается (для MS SQL)

select a.*, 
    (SELECT TOP 1 S.Koli
       FROM Sklad S
       WHERE S.KodSklad=0 AND S.KodTov=a.KOD AND S.DATA<='14.04.2015'
       ORDER BY S.DATA DESC)  as Ostatok 
  FROM SprTovar a
  order by lower(a.Name)

4. Ни когда бы так остатки не держал

PS забыл о дате, подправил

1. Субд - ADS и FireBird
2. Индексы Sklad (KodTov, Data, KodSklad), SprTovar (Kod, Name)
3. - отлично! попробую
4. Как бы организовали остатки?

[Аватар]

Ну там First вместо TOP. order by в подзапросе не уверен, нужно пробовать.

Остатки считал бы на весу - самые начальные + приход - расход на дату и все движение вместе с начальными остатками в одной таблице. Начальные как таковые и не нужны, это может быть просто приход на дату начала эксплуатации

попробовать с составным индексом из даты, кода продукта и номера склада

[Stanislav]

Вообще свои 5 копеек положу могу ошибаться но это мое мнение.

Весь склад по сути это GR, GI храним все в одной таблице, приход +, расход минус. это главная таблица.

Индексы да нужны безусловно по полям выборки желательно. А вообще мне что то очень не нравится этот запрос с точки зрения правильности. Ну даже если так можно было сделать вьюху.

[EnMac]

Сообщение от Аватар

Остатки считал бы на весу - самые начальные + приход - расход на дату и все движение вместе с начальными остатками в одной таблице.

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

Составной индекс попробую. спасибо.

p.s. Аватар,прокомментируйте пожалуйста.

[EnMac]

Сообщение от Stanislav

Весь склад по сути это GR, GI храним все в одной таблице, приход +, расход минус. это главная таблица.

А поясните немножко поподробней пожалуйста.

[Stanislav]

В первом приближении:

20.03.2015 22.00 яблоки GI 10
20.03.2015 22.10 яблоки GR 5
21.03.2015 22.00 яблоки GI 100

смысл все должно быть просто дата. продукт. приход. расход кол-во. считайте потом сколько вам душе угодно остатки, расходы, приходы. если есть промежуточные движения ну например может буфер какой, можно типы движений писать.

из логики думаю только не возможность списать больше чем есть остатков

на всякий:

GI - Goods Issue
GR - Goods receipt

еще если интересно почитайте про WMS, начать можно отсюда я думаю это лишнее конечно но раз у вас стоят такие задачи я считаю вам необходимо понимать все эти вещи так или иначе

[EnMac]

Сообщение от Stanislav

В первом приближении:

20.03.2015 22.00 яблоки GI 10
20.03.2015 22.10 яблоки GR 5
21.03.2015 22.00 яблоки GI 100

смысл все должно быть просто дата. продукт. приход. расход кол-во. считайте потом сколько вам душе угодно остатки, расходы, приходы. если есть промежуточные движения ну например может буфер какой, можно типы движений писать.

из логики думаю только не возможность списать больше чем есть остатков

на всякий:

GI - Goods Issue
GR - Goods receipt

еще если интересно почитайте про WMS, начать можно отсюда я думаю это лишнее конечно но раз у вас стоят такие задачи я считаю вам необходимо понимать все эти вещи так или иначе

Станислав, спасибо за помощь. Уже изучаю (всегда исхожу из того что, повторение - Мать учения) Напишу про результат.

Сообщение от Аватар

напрашивается (для MS SQL)

select a.*, 
    (SELECT TOP 1 S.Koli
       FROM Sklad S
       WHERE S.KodSklad=0 AND S.KodTov=a.KOD AND S.DATA<='14.04.2015'
       ORDER BY S.DATA DESC)  as Ostatok 
  FROM SprTovar a
  order by lower(a.Name)

Аватар, большое Вам спасибо!
Ваш запрос отлично работает, и очень быстро.
Я немножко дополнил его, и вся информация которая мне нужна была у меня есть!

select a.*, 
    (SELECT top 1 S.Kol
       FROM Sklad S
       WHERE S.KodSklad=0 AND S.KodTov=a.KOD AND S.DATA<='14.04.2015'
       ORDER BY S.DATA DESC)  as Ostatok,
    (SELECT top 1 S.DATA
       FROM Sklad S
       WHERE S.KodSklad=0 AND S.KodTov=a.KOD AND S.DATA<='14.04.2015'
       ORDER BY S.DATA DESC)  as DATA	    
  FROM SprTovar a
  order by lower(a.Name)

Остатки сверил, все сходится.
Предварительно (в sql-утилите):
предыдущий мой запрос = 5,36 сек. Ваш 0,52 сек

Чуть позже напишу точные замеры по времени при использовании в приложении.

p.s. Я в лоб написал второй подзапрос, правильно сделал или не совсем корректно?

В приложении оказалось не так все радужно.
В sql-утилите записи не все подгружались. Поэтому было быстро.
А в приложении у меня предполагается загрузка всех записей. (почему? потому что клиент так хочет).
По замерам времени по sql-запросу "Аватара" время выборки составляет от 4,12 до 4,66 сек
По моему запросу время от 2,63 до 2,67.
Для ускорения работы убрал кое-какие настройки в компонентах доступа к таблицам.

Получается в sql-запросе оптимизировать нечего?

[Аватар]

Как вариант еще можно

SELECT a.*,c.DATA,c.Ostatok
  FROM SprTovar a
    LEFT JOIN (
      SELECT S1.KodTov,S1.DATA,S1.Kol AS Ostatok
        FROM Sklad S1
        WHERE S1.KodSklad=0 and S1.DATA<='14.04.2015' AND
              NOT EXISTS(SELECT 0
                           FROM Sklad S2
                           WHERE S2.KodSklad=0 and S2.KodTov=S1.KodTov AND
                                 S2.DATA>S1.DATA AND S2.DATA<='14.04.2015')) c ON c.KodTov=a.KOD
  order by lower(a.Name)

и внимательно на индексы Sklad посмотреть