iBase.ru Forum

Народ, данный запрос был написан "ногами". Работает он крайне медленно, так как таблица base в 1000 раз больше таблицы goods. Помогите его оптимизировать. Плз.

Код: Выделить всё

SELECT (SELECT count(*)
              FROM base b
              LEFT JOIN base b1 ON b1.war_base_id=b.base_id
              WHERE g.goods_id=b.goods_id
                AND b1.war_base_id IS NULL
                AND b.sell_date IS NULL
                AND b.beznal=0),
             (SELECT count(*) FROM base b
              LEFT JOIN base b1 ON b1.war_base_id=b.base_id
              WHERE g.goods_id=b.goods_id
                AND b1.war_base_id IS NULL
                AND b.sell_date IS NULL
                AND b.beznal=1),
             c.category, g. name
FROM goods g, category c
WHERE g.category_id=c.category_id
AND g.inprice=1
ORDER BY c.category, g.name

[Модератор: Тема перемещена из "Общие проблемы"]

Доброго времени суток!

Я бы, наверное, написал нечто вроде следующего (FB 1.5)

Код: Выделить всё

SELECT c.category,g.name,
       SUM(CASE b.beznal WHEN 0 THEN 1 ELSE 0),
       SUM(CASE b.beznal WHEN 1 THEN 1 ELSE 0)
FROM goods g inner join category c
          on g.category_id=c.category_id
       left join base b
          on g.goods_id=b.goods_id
       left join base b1 
          ON b1.war_base_id=b.base_id
WHERE g.inprice=1
and b.sell_date IS NULL   
and b1.war_base_id IS NULL     
group by c.category, g.name
order by 1,2

хотя вряд ли запрос будет летать.

Если не трудно укажи полностью задачу: схема БД,хотя бы название таблиц и стобцов, и то чего надо получить

вообще запрос похож на поиск несоответствий между
war_base_id и base_id, или поиск "дырявых" незаполненных записей.

Переваривал запрос Кузнецова Евгения. НЕТ. Это не совсем то. Мой запрос возвращает 170 записей, Ваш - 131. Вот такой же, только упращенный вариант моего запроса.

Код: Выделить всё

SELECT g.name,(SELECT count(*)
              FROM base b 
              WHERE b.goods_id=g.goods_id
                AND b.sell_date IS NULL
                AND b.beznal=0), 
             (SELECT count(*)
              FROM base b
              WHERE b.goods_id=g.goods_id
                AND b.sell_date IS NULL
                AND b.beznal=1)
FROM goods g
WHERE g.inprice=1

Как объеденить таблицы так, чтовы кол-во записей результата соответствовало кол-ву записей таблицы goods (с условием g.inprice=1), а именно - 170.

Левым соединением.

Если Вас не затруднит код написать. У меня почему то теряются записи.

S.H.S писал(а):Переваривал запрос Кузнецова Евгения. НЕТ. Это не совсем то.

Извините, ошибся. В общем случае без использования
SP/Derived Tables/Execute Block, наверное, красиво, не получится

S.H.S писал(а):Если Вас не затруднит код написать. У меня почему то теряются записи.

Наверное, как и я, Вы условия в WHERE помещаете, а надо
в условие связки ON. Ну вот так устроит?

Код: Выделить всё

SELECT g.name,SUM(CASE b.beznal WHEN 0 THEN 1 ELSE 0), 
       SUM(CASE b.beznal WHEN 1 THEN 1 ELSE 0)
FROM goods g left join base b 
      on b.goods_id=g.goods_id and b.sell_date IS NULL
WHERE g.inprice=1
group by g.name

Спасибо все супер.
Но у меня возникла другая проблема, как к этому запросу прикрутить

Код: Выделить всё

LEFT JOIN base b1 ON b1.war_base_id=b.base_id 
                 AND b1.war_base_id IS NULL

Если b1.war_base_id IS NULL ставить в ON то SUM считается не верно, если после общего WHERE - теряются записи.

S.H.S писал(а):Спасибо все супер.
Но у меня возникла другая проблема, как к этому запросу прикрутить
Код: Выделить всё
LEFT JOIN base b1 ON b1.war_base_id=b.base_id 
                 AND b1.war_base_id IS NULL
Если b1.war_base_id IS NULL ставить в ON то SUM считается не верно, если после общего WHERE - теряются записи.

Вообще - то в такой конструкции запросы записи теряться не должны. Проверь.

S.H.S писал(а):Но у меня возникла другая проблема, как к этому запросу прикрутить

Заноси его в CASE чтобы считалось правильно.

Все работает. ВСЕМ СПАСИБО.

В итоге с такого:

Код: Выделить всё

SELECT goods_id, c.category_id, c.category, name, price, warranty, 
      (SELECT count(*) FROM base b
                       LEFT JOIN base b1 ON b1.war_base_id=b.base_id
                       WHERE g.goods_id=b.goods_id
                         AND b.sell_date IS NULL
                         AND b.warranty_date IS NULL
                         AND b.del_date IS NULL
                         AND b1.war_base_id IS NULL
                         AND b.reserved_id IS NULL
                         AND b.beznal='0'
                          ),
      (SELECT count(*) FROM base b
                       LEFT JOIN base b1 ON b1.war_base_id=b.base_id
                       WHERE g.goods_id=b.goods_id
                         AND b.sell_date IS NULL
                         AND b.warranty_date IS NULL
                         AND b.del_date IS NULL
                         AND b1.war_base_id IS NULL
                         AND b.reserved_id IS NULL 
                         AND b.beznal='1'
                          ) as beznal,
   category ||' '|| name as fullname, (ROUND((price-price/100*discount_pr)/100))*100,
   discount_pr, g.color_sell, '' as c
FROM goods g, category c
WHERE g.category_id=c.category_id AND
      g.inprice=1
ORDER BY c.category, name

стал такой:

Код: Выделить всё

SELECT g.goods_id, c.category_id, c.category, g.name, g.price, g.warranty,
       SUM(iif((b.beznal=0 AND b1.war_base_id IS NULL), 1, 0)),
       SUM(iif((b.beznal=1 AND b1.war_base_id IS NULL), 1, 0)),
       c.category ||' '|| g.name as fullname,
       (ROUND((g.price-g.price/100*g.discount_pr)/100))*100,
      g.discount_pr, g.color_sell, '' as c
FROM goods g
JOIN category c ON c.category_id=g.category_id
LEFT JOIN base b ON b.goods_id=g.goods_id
                AND b.sell_date IS NULL
                AND b.warranty_date IS NULL
                AND b.del_date IS NULL
                AND b.reserved_id IS NULL
LEFT JOIN base b1 ON b1.war_base_id=b.base_id
WHERE g.inprice=1
GROUP BY 1,2,3,4,5,6,9,10,11,12,13
ORDER BY c.category, g.name

Мне он на много больше нравится

. Толоко работает на 30ms медленнее.

Я проверил, все из-за LEFT JOIN base b1 ON b1.war_base_id=b.base_id. Но без него нельзя.

Доброго времени суток!

TO "S.H.S"

Ну зачем Вам группировать по 1,2,3,4,5,6,9,10,11,12,13? -
естественно, будет тормозить.
В предположении, что g.goods_id - первичный ключ, а каждый товар
принадлежит не более одной категории, я бы написал следующее

Код: Выделить всё

select b.goods_id,min(g.name),
                      min(c.category),
                      SUM(iif((b.beznal=0 AND b1.war_base_id IS NULL), 1, 0)),
                      SUM(iif((b.beznal=1 AND b1.war_base_id IS NULL), 1, 0)),
                      min((ROUND((g.price-g.price/100*g.discount_pr)/100))*100),
                      min(g.discount_pr),
                      min(g.color_sell),                      
                      min(g.price),
                      min(g.warranty),
                      min(c.category ||' '|| g.name) as fullname,
                      min(c.category_id)
    from base b left join base b1
             ON b1.war_base_id=b.base_id 
         inner join goods g
             ON b.goods_id=g.goods_id
         inner join  category c
             ON c.category_id=g.category_id
    where b.sell_date IS NULL
      AND b.warranty_date IS NULL
      AND b.del_date IS NULL
      AND b.reserved_id IS NULL
      AND g.inprice=1
    group by b.goods_id

    union all

    select g.goods_id,min(g.name),
                      min(c.category),
                     CAST(0 AS INTEGER),
                     CAST(0 AS INTEGER),
                     min((ROUND((g.price-g.price/100*g.discount_pr)/100))*100),
                     min(g.discount_pr),
                     min(g.color_sell),
                     min(g.price),
                     min(g.warranty),
                     min(c.category ||' '|| g.name) as fullname,
                     min(c.category_id)
    from goods g inner join  category c
             ON c.category_id=g.category_id
      LEFT JOIN base b
              ON b.goods_id=g.goods_id
                AND b.sell_date IS NULL 
                AND b.warranty_date IS NULL 
                AND b.del_date IS NULL 
                AND b.reserved_id IS NULL 
    where g.inprice=1 and b.goods_id IS NULL
    group by g.goods_id
    order by 2,3

Запрос может быть ошибочным, но основная идея
ясна - вынести связку base left join base в начало плана.

Кстати IIF - Yaffil или FB2?

А ведь ошибся - во-первых, сортировка должна идти по 3,2,
во-вторых, если g.goods_id - первичный ключ, то группировка
во втором запросе не нужна.

Кстати, правильно ли я понимаю, что Вы храните цену товара вместе
с его наименованием в таблице goods? Ведь это противоречит второй нормальной форме!

Ведь это противоречит второй нормальной форме!

это почему же противоречит?

kdv писал(а):это почему же противоречит?

Цена товара вроде бы зависит также от времени, не только от идентификатора товара.
Можно, конечно, хранить только действующую цену - но какая же это БД,
если в ней нельзя посмотреть ситуацию на "вчера"?

Ваш запрос на FB2 - отрабатывает на 210 ms дольше последнего. Но все равно благодарю.
На счет 2НФ:
category - это категория товара.
goods - это виды товаров (price - цена продажи по видам)
base - это собственно "база" товаров (здесь цена закупки)
b1.base - это таже "база" (товар, выданный клиенту на замену)

Код: Выделить всё

category<-goods<-base<-|
                  |    |
                  |----|

а где здесь противоречие?
Это запрос формирования прайса, с разделением количества товара по безналу и за нал.

Кузнецов Евгений писал(а):Цена товара вроде бы зависит также от времени, не только от идентификатора товара.

А валюта? А склад? А ....

в goods - price-цена продажи на сегодня (меня не интересует вчерашняя цена)
в base - buy_price, sell_price - цена покупки и цена продажи.

iBase.ru Forum

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

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

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